Read non-externally described file in vb.net 2010
Last Post 11 Dec 2012 02:20 PM by mrichard. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
mrichard
New Member
New Member
Posts:4

--
11 Dec 2012 02:20 PM

I am trying to read non-externally described file in CCSID 65535 from a vb.net 2010 application. I know how to use the CAST function in sql to handle the conversion from CCSID 65535 to 37. However the record format of the file is giving me problems in how i select an individual set of records for a given account number in file. I have attached the record format. The first nine record formats only appear one time at beginning of file. Then record formats 9 thru 98 can appear many times after that depending on number of unique account numbers there are. My project is to pass in a account number from another routine, capture the first nine record formats and then capture record formats 9 thru 98 for that specific account number. This may be a sql question but i am unsure.

Following is the test code so far:

Imports IBM.Data.DB2.iSeries
Public Class Form1

 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 Dim thisConnection As New iDB2Connection("Datasource=sub; Naming=System; LibraryList=QS36F")
 ' select ppa,pga,delq date, cut off date arrears, cut off date current,
 Dim thisCommand As New iDB2Command("SELECT CASE WHEN CAST(SUBSTR(F00001,35,1) AS CHAR(1) CCSID 37) = '}' THEN " &
 "DECIMAL(DECIMAL((CAST(SUBSTR(F00001,29,6) AS CHAR(6) CCSID 37) || '0'),7,0)/10000000,7,7)*-1 " &
 "WHEN CAST(SUBSTR(F00001,35,1) AS CHAR(1) CCSID 37) = 'J' THEN " &
 "DECIMAL(DECIMAL((CAST(SUBSTR(F00001,29,6) AS CHAR(6) CCSID 37) || '1'),7,0)/10000000,7,7)*-1 " &
 "WHEN CAST(SUBSTR(F00001,35,1) AS CHAR(1) CCSID 37) = 'K' THEN " &
 "DECIMAL(DECIMAL((CAST(SUBSTR(F00001,29,6) AS CHAR(6) CCSID 37) || '2'),7,0)/10000000,7,7)*-1 " &
 "WHEN CAST(SUBSTR(F00001,35,1) AS CHAR(1) CCSID 37) = 'L' THEN " &
 "DECIMAL(DECIMAL((CAST(SUBSTR(F00001,29,6) AS CHAR(6) CCSID 37) || '3'),7,0)/10000000,7,7)*-1 " &
 "WHEN CAST(SUBSTR(F00001,35,1) AS CHAR(1) CCSID 37) = 'M' THEN " &
 "DECIMAL(DECIMAL((CAST(SUBSTR(F00001,29,6) AS CHAR(6) CCSID 37) || '4'),7,0)/10000000,7,7)*-1 " &
 "WHEN CAST(SUBSTR(F00001,35,1) AS CHAR(1) CCSID 37) = 'N' THEN " &
 "DECIMAL(DECIMAL((CAST(SUBSTR(F00001,29,6) AS CHAR(6) CCSID 37) || '5'),7,0)/10000000,7,7)*-1 " &
 "WHEN CAST(SUBSTR(F00001,35,1) AS CHAR(1) CCSID 37) = 'O' THEN " &
 "DECIMAL(DECIMAL((CAST(SUBSTR(F00001,29,6) AS CHAR(6) CCSID 37) || '6'),7,0)/10000000,7,7)*-1 " &
 "WHEN CAST(SUBSTR(F00001,35,1) AS CHAR(1) CCSID 37) = 'P' THEN " &
 "DECIMAL(DECIMAL((CAST(SUBSTR(F00001,29,6) AS CHAR(6) CCSID 37) || '7'),7,0)/10000000,7,7)*-1 " &
 "WHEN CAST(SUBSTR(F00001,35,1) AS CHAR(1) CCSID 37) = 'Q' THEN " &
 "DECIMAL(DECIMAL((CAST(SUBSTR(F00001,29,6) AS CHAR(6) CCSID 37) || '8'),7,0)/10000000,7,7)*-1 " &
 "WHEN CAST(SUBSTR(F00001,35,1) AS CHAR(1) CCSID 37) = 'R' THEN " &
 "DECIMAL(DECIMAL((CAST(SUBSTR(F00001,29,6) AS CHAR(6) CCSID 37) || '9'),7,0)/10000000,7,7)*-1 " &
 "ELSE DECIMAL(DECIMAL(CAST(SUBSTR(F00001,29,7) AS CHAR(7) CCSID 37),7,0)/10000000,7,7) END AS PPA, " &
 "CASE WHEN CAST(SUBSTR(F00001,45,1) AS CHAR(1) CCSID 37) = '}' THEN " &
 "DECIMAL(DECIMAL((CAST(SUBSTR(F00001,39,6) AS CHAR(6) CCSID 37) || '0'),7,0)/10000000,7,7)*-1 " &
 "WHEN CAST(SUBSTR(F00001,45,1) AS CHAR(1) CCSID 37) = 'J' THEN " &
 "DECIMAL(DECIMAL((CAST(SUBSTR(F00001,39,6) AS CHAR(6) CCSID 37) || '1'),7,0)/10000000,7,7)*-1 " &
 "WHEN CAST(SUBSTR(F00001,45,1) AS CHAR(1) CCSID 37) = 'K' THEN " &
 "DECIMAL(DECIMAL((CAST(SUBSTR(F00001,39,6) AS CHAR(6) CCSID 37) || '2'),7,0)/10000000,7,7)*-1 " &
 "WHEN CAST(SUBSTR(F00001,45,1) AS CHAR(1) CCSID 37) = 'L' THEN " &
 "DECIMAL(DECIMAL((CAST(SUBSTR(F00001,39,6) AS CHAR(6) CCSID 37) || '3'),7,0)/10000000,7,7)*-1 " &
 "WHEN CAST(SUBSTR(F00001,45,1) AS CHAR(1) CCSID 37) = 'M' THEN " &
 "DECIMAL(DECIMAL((CAST(SUBSTR(F00001,39,6) AS CHAR(6) CCSID 37) || '4'),7,0)/10000000,7,7)*-1 " &
 "WHEN CAST(SUBSTR(F00001,45,1) AS CHAR(1) CCSID 37) = 'N' THEN " &
 "DECIMAL(DECIMAL((CAST(SUBSTR(F00001,39,6) AS CHAR(6) CCSID 37) || '5'),7,0)/10000000,7,7)*-1 " &
 "WHEN CAST(SUBSTR(F00001,45,1) AS CHAR(1) CCSID 37) = 'O' THEN " &
 "DECIMAL(DECIMAL((CAST(SUBSTR(F00001,39,6) AS CHAR(6) CCSID 37) || '6'),7,0)/10000000,7,7)*-1 " &
 "WHEN CAST(SUBSTR(F00001,45,1) AS CHAR(1) CCSID 37) = 'P' THEN " &
 "DECIMAL(DECIMAL((CAST(SUBSTR(F00001,39,6) AS CHAR(6) CCSID 37) || '7'),7,0)/10000000,7,7)*-1 " &
 "WHEN CAST(SUBSTR(F00001,45,1) AS CHAR(1) CCSID 37) = 'Q' THEN " &
 "DECIMAL(DECIMAL((CAST(SUBSTR(F00001,39,6) AS CHAR(6) CCSID 37) || '8'),7,0)/10000000,7,7)*-1 " &
 "WHEN CAST(SUBSTR(F00001,45,1) AS CHAR(1) CCSID 37) = 'R' THEN " &
 "DECIMAL(DECIMAL((CAST(SUBSTR(F00001,39,6) AS CHAR(6) CCSID 37) || '9'),7,0)/10000000,7,7)*-1 " &
 "ELSE DECIMAL(DECIMAL(CAST(SUBSTR(F00001,39,7) AS CHAR(7) CCSID 37),7,0)/10000000,7,7) END AS PGA, " &
 "CAST(INT(SUBSTR(F00001,50,2)) || '/' || INT(SUBSTR(F00001,52,2)) || '/' || INT(SUBSTR(F00001,54,2)) AS CHAR(10) CCSID 37) AS DelqDate, " &
 "CAST(INT(SUBSTR(F00001,60,2)) || '/' || INT(SUBSTR(F00001,62,2)) || '/' || INT(SUBSTR(F00001,64,2)) AS CHAR(10) CCSID 37) AS CutDateArr, " &
 "CAST(INT(SUBSTR(F00001,70,2)) || '/' || INT(SUBSTR(F00001,72,2)) || '/' || INT(SUBSTR(F00001,74,2)) AS CHAR(10) CCSID 37) AS CutDateCurr " &
 "FROM BILLOUT1 WHERE CAST(SUBSTR(F00001,99,2) AS CHAR(2) CCSID 37)='00'", thisConnection)

 'Message(info)
 Dim thisCommand2 As New iDB2Command("SELECT CAST(SUBSTR(F00001,1,42) AS CHAR(42) CCSID 37) AS MESS1, " &
 "CAST(SUBSTR(F00001,44,42) AS CHAR(42) CCSID 37) AS MESS9, " &
 "FROM BILLOUT1 WHERE CAST(SUBSTR(F00001,99,2) AS CHAR(2) CCSID 37)='01'", thisConnection)
 Dim thisCommand3 As New iDB2Command("SELECT CAST(SUBSTR(F00001,1,42) AS CHAR(42) CCSID 37) AS MESS2, " &
 "CAST(SUBSTR(F00001,44,42) AS CHAR(42) CCSID 37) AS MESS10, " &
 "FROM BILLOUT1 WHERE CAST(SUBSTR(F00001,99,2) AS CHAR(2) CCSID 37)='02'", thisConnection)
 Dim thisCommand4 As New iDB2Command("SELECT CAST(SUBSTR(F00001,1,42) AS CHAR(42) CCSID 37) AS MESS3, " &
 "CAST(SUBSTR(F00001,44,42) AS CHAR(42) CCSID 37) AS MESS11, " &
 "FROM BILLOUT1 WHERE CAST(SUBSTR(F00001,99,2) AS CHAR(2) CCSID 37)='03'", thisConnection)
 Dim thisCommand5 As New iDB2Command("SELECT CAST(SUBSTR(F00001,1,42) AS CHAR(42) CCSID 37) AS MESS4, " &
 "CAST(SUBSTR(F00001,44,42) AS CHAR(42) CCSID 37) AS MESS12, " &
 "FROM BILLOUT1 WHERE CAST(SUBSTR(F00001,99,2) AS CHAR(2) CCSID 37)='04'", thisConnection)
 Dim thisCommand6 As New iDB2Command("SELECT CAST(SUBSTR(F00001,1,42) AS CHAR(42) CCSID 37) AS MESS5, " &
 "CAST(SUBSTR(F00001,44,42) AS CHAR(42) CCSID 37) AS MESS13, " &
 "FROM BILLOUT1 WHERE CAST(SUBSTR(F00001,99,2) AS CHAR(2) CCSID 37)='05'", thisConnection)
 Dim thisCommand7 As New iDB2Command("SELECT CAST(SUBSTR(F00001,1,42) AS CHAR(42) CCSID 37) AS MESS6, " &
 "CAST(SUBSTR(F00001,44,42) AS CHAR(42) CCSID 37) AS MESS14, " &
 "FROM BILLOUT1 WHERE CAST(SUBSTR(F00001,99,2) AS CHAR(2) CCSID 37)='06'", thisConnection)
 Dim thisCommand8 As New iDB2Command("SELECT CAST(SUBSTR(F00001,1,42) AS CHAR(42) CCSID 37) AS MESS7, " &
 "CAST(SUBSTR(F00001,44,42) AS CHAR(42) CCSID 37) AS MESS15, " &
 "FROM BILLOUT1 WHERE CAST(SUBSTR(F00001,99,2) AS CHAR(2) CCSID 37)='07'", thisConnection)
 Dim thisCommand9 As New iDB2Command("SELECT CAST(SUBSTR(F00001,1,42) AS CHAR(42) CCSID 37) AS MESS8, " &
 "CAST(SUBSTR(F00001,44,42) AS CHAR(42) CCSID 37) AS MESS16, " &
 "FROM BILLOUT1 WHERE CAST(SUBSTR(F00001,99,2) AS CHAR(2) CCSID 37)='08'", thisConnection)
 'bill info by account no
 Dim thisCommand1 As New iDB2Command("SELECT CAST(SUBSTR(F00001,1,9) AS CHAR(9) CCSID 37) AS AcctNo, CAST(SUBSTR(F00001,41,5) AS CHAR(5) CCSID 37) AS DaysCurrBC, " &
 "CAST(SUBSTR(F00001,46,5) AS CHAR(5) CCSID 37) AS DaysCurrLM, " &
 "CAST(SUBSTR(F00001,51,5) AS CHAR(5) CCSID 37) AS DaysCurrLY, " &
 "CAST(SUBSTR(F00001,1,24) AS CHAR(24) CCSID 37) AS Name " &
 "FROM BILLOUT1 WHERE CAST(SUBSTR(F00001,99,2) " &
 "AS CHAR(2) CCSID 37)='09' AND CAST(SUBSTR(F00001,1,9) AS CHAR(9) CCSID 37)='112012800'", thisConnection)
 Dim thisCommand10 As New iDB2Command("SELECT CAST(SUBSTR(F00001,1,24) AS CHAR(24) CCSID 37) AS Name " &
 "FROM BILLOUT1 WHERE CAST(SUBSTR(F00001,99,2) AS CHAR(2) CCSID 37)='10'", thisConnection)
 Dim thisReader As iDB2DataReader
 Dim thisReader1 As iDB2DataReader
 Dim thisReader10 As iDB2DataReader
 Try
 thisConnection.Open()
 thisReader = thisCommand.ExecuteReader(CommandBehavior.SingleRow)
 If thisReader.HasRows Then
 While thisReader.Read()
 Label2.Text = CStr(thisReader("PPA"))
 Label4.Text = CStr(thisReader("PGA"))
 Label6.Text = CStr(thisReader("DelqDate"))
 Label13.Text = CStr(thisReader("CutDateArr"))
 Label14.Text = CStr(thisReader("CutDateCurr"))
 End While
 End If
 thisReader.Close()
 thisCommand.Dispose()
 thisReader1 = thisCommand1.ExecuteReader()
 Dim dt As New DataTable()
 dt.Load(thisReader1)
 dgv1.DataSource = dt
 If thisReader1.HasRows Then
 While thisReader1.Read()

 Label8.Text = CStr(thisReader1("AcctNo"))
 Label15.Text = CStr(thisReader1("DaysCurrBC"))
 Label16.Text = CStr(thisReader1("DaysCurrLM"))
 Label17.Text = CStr(thisReader1("DaysCurrLY"))
 Label18.Text = CStr(thisReader1("Name"))
 End While
 End If
 thisReader1.Close()
 thisCommand1.Dispose()
 thisReader10 = thisCommand10.ExecuteReader(CommandBehavior.SingleRow)
 If thisReader10.HasRows Then
 While thisReader10.Read()

 Label18.Text = CStr(thisReader10("Name"))

 End While
 End If
 thisReader10.Close()
 thisCommand10.Dispose()
 Catch ex As Exception
 MessageBox.Show(ex.Message)
 Finally
 thisConnection.Close()
 End Try
 End Sub
End Class

Thanks for your help
  


BillOutFormat.pdf

You are not authorized to post a reply.

Acceptable Use Policy