Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jamlogg
Starting Member
10 Posts |
 Posted - 2011-06-24 : 14:20:48
|
| 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
5072 Posts |
Posted - 2011-06-24 : 14:51:18
|
| See this for substring function in DB2.ThisLEFT(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... |
 |
|
|
jamlogg
Starting Member
10 Posts |
Posted - 2011-06-24 : 16:37:33
|
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.ThisLEFT(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 |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-06-24 : 17:41:18
|
| SUBSTR(expr, 1, 7) |
|
|
|
|
|
|
|
|
How to use an if-then statement in DB2 SQL