|
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
|
|
|
|
|
current column is of type "Text" in sql server
I am converting table pl/sql. What would be an equivalent datatype in pl/sql.
|
|
|
|
|
Have a look at CLOB or NCLOB types. I think they are the closest match. If you don't need more than 4000 bytes of text then you can also use VARCHAR2.
|
|
|
|
|
thanks for ur response. what is the difference between CLOB and NCLOB? I am going to be using this column to store html text
|
|
|
|
|
Civic06 wrote: what is the difference between CLOB and NCLOB
CLOB is used for single- and multi-byte characters wheras NCLOB is used for unicode text.
|
|
|
|
|
Thanks. One more question: either type will only take up the amount of space depending on how much is stored in the column? In other words, its not fixed length right?
|
|
|
|
|
Civic06 wrote: its not fixed length right
No it's not. Single CLOB can store gigabytes of data, so if it would be fixed length, it would eat out the disk space quite fast.
The minimum storage size is size of one block but depending on the CHUNK parameter, it can be multiple blocks.
|
|
|
|
|
Apologies if this isn't the correct forum.
OK, the situation is that our web app has a feature to send autometed emails. The emails may potentially contain the Euro symbol ('€'). The DB server holds the currency symbol, and it can be SELECTed correctly. However, when the symbol is used in an auto-email, it's replaced with a '?'
The sending is performed using CDO, encaspulated into an SP. The sp itself sets up the various requisites through sp_OASetProperty, and references the CDO schema...eg:
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
I'm able to correctly send a mail with a Euro symbol in it though Outlook, using the same exchange server.
Anyone have any idea what may be causing this / seen it before? Most of the links I've found seem to hint at setting Message.BodyPart.Codepage / CharSet, but I'm unable to find out how to do the same thing in T-SQL....
C# has already designed away most of the tedium of C++.
|
|
|
|
|
You could try setting the body to HTML if it's plain text and that may make a difference.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Hi and thanks for the quick reply.
Unfortunately, it's already an HTML email. I've just tried swapping to a text body just in case, and same result.
Cheers for the suggestion though!
C# has already designed away most of the tedium of C++.
|
|
|
|
|
Not sure about this, but try setting CDO.Message's BodyPart.Charset to utf-8.
|
|
|
|
|
Thanks for the reply....
Yes, that's what I suspect is needed, however, I'm unable to find how to do it in T-SQL....
I've tried varous combinations of OASets, but keep getting errors, and can't seem to find the "correct" way to do it...
Thanks again for your time...
C# has already designed away most of the tedium of C++.
|
|
|
|
|
I haven't really used that (always used either sql mail or database mail). I'd guess it could be something like :
sp_OASetProperty @iMsg, 'BodyPart.Charset.Value','"utf-8"'
Or some of it's variations. I guess you'll have to google around or test several variations.
If it would be possible for you I'd suggest that you would use database mail (if having SQL Server 2005 or above). It's a whole lot simpler and purely smtp.
modified on Monday, December 8, 2008 5:14 PM
|
|
|
|
|
Got it in the end.....
EXEC @hr = sp_OASetProperty @iMsg, 'BodyPart.Charset', 'windows-1252'
Seems to do the trick....Many thanks for the help there chap.
C# has already designed away most of the tedium of C++.
|
|
|
|
|
RichardGrimmer wrote: Many thanks for the help there chap
You're welcome.
|
|
|
|
|
How to select the column names that contains a value of say 0 in the first row
Please help 
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
I think you could write a little procedure (or program). If you need the columns only from one table, just select the first row and loop through columns. If you need info from several tables, you could use system tables and based on the info from there, loop tables, fetch first row and then loop through columns. You'll need to use dynamic statements based on system table information.
|
|
|
|
|
If you want it in SQL , so :
SELECT names FROM tableName WHERE id IN ( SELECT MIN(id) FROM tableName )
You should identify first row by a field , in this case I used id
|
|
|
|
|
Mohammad Dayyan wrote: SELECT names FROM tableName WHERE id IN ( SELECT MIN(id) FROM tableName )
Hey Mohammad,
More help please 
By selecting column names, I'll get all the columns even if they contain a value other than the minimum in the sub select statement, are you sure it can be done from sql??
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
It should work, but I think sou can use this query too :
SELECT name, MIN(id)
FROM tableName ;
|
|
|
|
|
Hey Mohammad,
Thank you for your kind help, but do you understand my question??
I want to get the column names of a table that has for example the value '1' in the first and only row of that table
Example:
A B C
-- -- --
0 0 1
The output should be:
C
Please tell me if you can make this happen.
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Thanks Muammar. I've got it right now 
I think you should use several queries
select A from tablename where A=1
select B from tablename where B=1
select C from tablename where C=1
|
|
|
|
|
No, thanks to you Mohammad,
That should do it, but do you have any idea how can we combine the 3 select statements so they would return a single row??
Thank you for being there until now
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Thanks Mohammad, UNION ALL was what I needed to combine multiple select statements and get them in one column
Column Name
------------
A
B
C
D
Column Value
------------
0
0
1
0
Though, I still need to combine those tow columns in one output, can you help in that too my friend
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Muammar© wrote: Thank you for being there until now
Oh no problem Muammar, I'm happy to help someone .
Muammar© wrote: That should do it, but do you have any idea how can we combine the 3 select statements so they would return a single row??
I'm so sorry , I don't know
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
