SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to use an if-then statement in DB2 SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jamlogg
Starting Member

USA
10 Posts

Posted - 06/24/2011 :  14:20:48  Show Profile  Reply with Quote
I have a formula in a report which has an if-then statement. I am trying to convert that to DB2 SQL in Windows and wanted to know how to do that. I have use a case statement, but i m not getting any values for it.


Below is the original if-then formula:

if left({table1.col1},7) in left({table2.col1},7) then
{table2.col1}[1 to 7]
else if {table1.col1}[3 to 7] in {table2.col1}[8 to 12]
then {table2.col1}[6 to 12] else
''


Below is the case statement in SQL:

SELECT CASE
WHEN LEFT( table1.col1,7) = LEFT(table2.col1,7)
THEN LEFT(RIGHT(table2.col1,1),7)
WHEN LEFT(RIGHT(table1.col1,3),7) = LEFT(RIGHT(table2.col1,8),12) THEN
LEFT(RIGHT(table2.col1,6),12)
else ' ',.....table3.col1,.....

I tried using 'substr' in place of left/right for the SQL, but it gave me errors that the numeric argument was out of range. So this case statement runs in the SQL query but does not give any results as compared to the report formula above that does give values. Could anybody know why the SQL case is giving empty rows? Any help will be appreciated!

K.G

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 06/24/2011 :  14:51:18  Show Profile  Visit russell's Homepage  Reply with Quote
See this for substring function in DB2.

This
LEFT(RIGHT(table2.col1,1),7)

ought to generate an error. RIGHT(expr, 1) has a length of 1.
Now you're trying a LEFT(expr, 7) against it...
Go to Top of Page

jamlogg
Starting Member

USA
10 Posts

Posted - 06/24/2011 :  16:37:33  Show Profile  Reply with Quote
Then which way would this work because I want the string to start at 1 and the length to be 7. so left(exp,1) is the starting point and right(exp,7) is the length of string.


quote:
Originally posted by russell

See this for substring function in DB2.

This
LEFT(RIGHT(table2.col1,1),7)

ought to generate an error. RIGHT(expr, 1) has a length of 1.
Now you're trying a LEFT(expr, 7) against it...



K.G
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 06/24/2011 :  17:41:18  Show Profile  Visit russell's Homepage  Reply with Quote
SUBSTR(expr, 1, 7)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000