I'm working on an ASP.Net application accessing couple of database tables from AS400 mainframe server database. In the code there's an SQL Query, which seems very complicated to me, the person who did the actual coding is no more with our firm.
Right now the query is producing some kinda result which is wrong. So can anyone please help me out in finding what's wrong with the SQL query? The SQL query is attached below:
Me.OdbcSelectCommand2.CommandText = "SELECT SAMFILE.STAYP.SCLINT, SAMFILE.STAYP.SADMHH, SAMFILE.STAYP.SADMYY, SAMFILE.STAYP.SADMMM, SAMFILE.STAYP.SBLDG, SAMFILE.STAYP.SADMDD, SAMFILE.STAYP.STRMCD, SAMFILE.STAYP.STRMHH, SAMFILE.STAYP.STRMYY, SAMFILE.STAYP.STRMMM, SAMFILE.STAYP.STRMDD, SAMFILE.CLIENTP.CNAME, SAMFILE.CLIENTP.CRACE1, SAMFILE.CLIENTP.CRACE2, SAMFILE.CLIENTP.CRELIG, SAMFILE.CLIENTP.CSEX, SAMFILE.CLIENTP.CBTHHH, SAMFILE.CLIENTP.CBTHYY, SAMFILE.CLIENTP.CBTHMM, SAMFILE.CLIENTP.CBTHDD, SAMFILE.REFSRP.REFSDS, SAMFILE.STAYP.SMDPM, SAMFILE.STAYP.SREFRL, SAMFILE.STAYP.SADRG1, SAMFILE.STAYP.SADRG2, SAMFILE.RACETP.RACEDS, SAMFILE.RELIGP.RELGDS, SAMFILE.STAYP.SADMAG FROM SAMFILE.REFSRP, SAMFILE.RELIGP, SAMFILE.RACETP, { oj SAMFILE.STAYP LEFT OUTER "JOIN SAMFILE.CLIENTP ON SAMFILE.STAYP.SCLINT = SAMFILE.CLIENTP.""CLNT#""} WHERE SAMFILE.REFSRP.REFSCD = SAMFILE.STAYP.SREFRL AND SAMFILE.RELIGP.RELGCD = SAMFILE.CLIENTP.CRELIG AND SAMFILE.RACETP.RACECD = SAMFILE.CLIENTP.CRACE1" // After some loop conditions, the OdbcSelectCommand2 is used in 4 concatenations like this. OdbcSelectCommand2.CommandText = OdbcSelectCommand2.CommandText & " AND (({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT(digits(SADMHH), digits(SADMYY)) }, '-') }, digits(SADMMM)) }, '-') }, digits(SADMDD)) } <= { d '" & End_date_conv & "' })"OdbcSelectCommand2.CommandText = OdbcSelectCommand2.CommandText & " AND ({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT(digits(STRMHH), digits(STRMYY)) }, '-') }, digits(STRMMM)) }, '-') }, digits(STRMDD)) } >= { d '" & Start_date_conv & "' })"OdbcSelectCommand2.CommandText = OdbcSelectCommand2.CommandText & " OR ({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT({ fn CONCAT(digits(SADMHH), digits(SADMYY)) }, '-') }, digits(SADMMM)) }, '-') }, digits(SADMDD)) } <= { d '" & End_date_conv & "' })"OdbcSelectCommand2.CommandText = OdbcSelectCommand2.CommandText & " AND (SAMFILE.STAYP.STRMCD = 0))" // To get the desired result, one more table is suppossed to be used, i.e MOVESP table, but its a tremendously huge table containing records of more than 30+ years and each id is having multiple records and to get the expected result it should be used in the code as something like this. But everytime I try this, it throws an exception "System.outofmemoryexception". I tried increasing the physical and virtual RAM and even timeout durations too but all in vain. OdbcSelectCommand2.CommandText = OdbcSelectCommand2.CommandText & " AND (SAMFILE.MOVESP.MCODE = 'A'))"
Note:
This query works but it fetches wrong information. In the below query I want to replace the last condition (SAMFILE.STAYP.STRMCD = 0) with this new condition (SAMFILE.MOVESP.MCODE = ""A"") and I believe this will make it to fetch the right values to display on the crystal report. But whenever I try to include this new code alongwith other necessary changes it shoots up an erorr message. And the other changes which I make are like :
1. Adding a new element by the name "MCODE" in "as400ds2.xsd"
2. Adding [New System.Data.Common.DataColumnMapping("MCODE", "MCODE")] into the OdbcDataAdapter.TableMappings.AddRange section.
Thanking you all in anticipation
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
