Shank Shank - 4 months ago 8
SQL Question

Conditional SQL Statement DB2 Based on columns

I'm not sure if this is possible, I have a java program which runs a given query (JDBC) and outputs the results into an excel file. The connection/db info is IBM as400, V7R1M0.

Let's say I have the following tables:

==================== =================================
| TABLE1 | | TABLE2 |
==================== =================================
| Company | CODE | | ID | CODE1 | CODE2 | CODE3 |
-------------------- --------------------------------
| 001 | E1 | | 1 | 10 | 18 | 21 |
| 005 | E3 | | 2 | 14 | 19 | 23 |
| 009 | E1 | | 3 | 17 | 20 | 25 |
| 015 | E2 | | 4 | 5 | 11 | 15 |
==================== =================================


My expected results will be like:

==============================
RESULTS
==============================
| Company | CODE | CODE3 |
------------------------------
| 005 | E3 | 21 |
==============================


Note(s):



  • TABLE1.CODE will tell us what column from TABLE2 to choose from.

  • TABLE1.COMPANY's CODE changes randomly

  • I only need one row from TABLE2, assume ID = 1 always.




I'm not sure how to approach this. I read that IBM AS400 doesn't allow
IF ... ELSE.


My idea is kinda-of like (Pseudocode):

SET Value = SELECT CODE FROM TABLE1 WHERE Company ='005'
IF Value = 'E1'
SELECT CODE1 FROM TABLE1 WHERE ID = 1;
ELSE IF Value = 'E2'
SELECT CODE2 FROM TABLE1 WHERE ID = 1;
...


EDIT

I know I can do this in Java. but I don't want to change it. Can it be done from SQL statement?

Answer

You can express this as a SQL query:

select t1.company, t1.code,
       (case when t1.code = 'E1' then t2.code1
             when t1.code = 'E2' then t2.code2
             when t1.code = 'E3' then t2.code3
        end) as t1_code
from table1 t1 join
     table2 t2
     on t2.id = 1;