I have a below query,i tried it but not able to come up with the exact solution.
I have two tables.
Table 1 has a column ID and table 2 have column CODE
NOW for every ID in table 1 there can be multiple rows in table 2 with different values of CODE.
I have to write a query such that for every ID in table 1 search (run through) table 2 to select a code which is valid( it will be valid if it has any of the value A,B,C,D) IF it is not valid make the code as X.
finally sort the output in specific order of code like X,B,A,C,D
Any suggestions/ help will be greatly appreciated.
I think you can do something as below. The first 2 columns should give you what you want and then you may get rid of the last column if needed to. This is Not tested as I don't have the access to mainframe as of now.
Code:
SELECT
A.ID,
CASE B.CODE
WHEN 'A' THEN 'A'
WHEN 'B' THEN 'B'
WHEN 'C' THEN 'C'
WHEN 'D' THEN 'D'
ELSE 'X'
END,
CASE B.CODE
WHEN 'A' THEN '3A'
WHEN 'B' THEN '2B'
WHEN 'C' THEN '4C'
WHEN 'D' THEN '5D'
ELSE '1X'
END AS MY_ORDER
FROM TABLE1 A, TABLE2 B
ORDER BY MY_ORDER;
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
Nitin,
Welcome to the forums!
I have not touched DB2 in a while, but should n't we be checking for something like this? This is not tested either and does n't take care of the order.
Code:
SELECT
T1.ID,
VALUE(T2.CODE,'X')
FROM TABLE1 T1
LEFT JOIN
(SELECT ID,CODE
FROM TABLE2
WHERE CODE IN ('A','B','C','D')) T2
ON T1.ID = T2.ID
It might help if the OP can post the structure of both the tables (relevant columns) and some sample data too.
RahulG31, I think, you are missing the relation between two tables.
Arun, I would like that approach but what if table2 doesn't have a matching row? in your case it will still be marked as 'X' but OP wants to mark X only for the entries other than (ABCD).
Nitin,
Welcome!
Before you expect a solution to the problem, please state the problem correctly along with the table structure, sample data and the desired output because right now it is a guess work.
However, based on what is stated, try this.
Code:
select T3.ID1,
T3.code2
from
(SELECT
T1.ID as ID1,
T2.code1 as code2,
case when T2.code1 = 'X' then 1
when T2.code1 = 'B' then 2
when T2.code1 = 'A' then 3
when T2.code1 = 'C' then 4
when T2.code1 = 'D' then 5
end
FROM TABLE1 T1,
(SELECT ID,case when CODE NOT IN ('A','B','C','D') then 'X' else CODE end as code1
FROM TABLE2 ) T2
where T1.ID = T2.ID
order by 3) as T3
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
Rohit,
Yes, that was an assumption I made that all table1 IDs are available in the second table unless the OP has stated otherwise, or what he wants in such a scenario.
Apart from that, the interpretations are different here. My understanding was like this, the OP can clarify if it is incorrect.
Let's say an ID=ID1 has the CODEs A,B,C,E,F. Since it has ANY one of the 'valid' IDs, my output will have only these:
Code:
ID1 A
ID1 B
ID1 C
Let's say another ID=ID2 has the CODEs E,F,G,H since none of them falls in the list of 'valid' CODEs, so the output will have these:
Code:
ID2 X
I see your code will give this for Case1:
Code:
ID1 A
ID1 B
ID1 C
ID1 X
ID1 X
I'd wait for the OP to clarify the requirement before we proceed any further.
Did you care to look at the solutions given so far? Please try it.
All you got to do is ,replace ID by the right columns in the joins between tables.Try solution given by Arun first, modify to have it sorted per your expectations.
What should happen if there is no entry in table 2 ?
Since, I like my own query, I would simply modify that as:
Code:
SELECT ID, MIN(CODE)
FROM
(
SELECT
A.ID as ID,
CASE B.CODE
WHEN 'A' THEN 'A'
WHEN 'B' THEN 'B'
WHEN 'C' THEN 'C'
WHEN 'D' THEN 'D'
ELSE 'X'
END as CODE,
CASE B.CODE
WHEN 'A' THEN '3A'
WHEN 'B' THEN '2B'
WHEN 'C' THEN '4C'
WHEN 'D' THEN '5D'
ELSE '1X'
END AS MY_ORDER
FROM TABLE1 A, TABLE2 B
WHERE A.MATCHING_COLUMN = B.MATCHING_COLUMN
ORDER BY 1,3,2
)
GROUP BY ID;
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
Quote:
i came up the sql what arun wote , that will not work as it will give multiple rows
But in your recent post you mentioned,
Quote:
if valid value of code is there same value should be repeated for that ID
Can you show what have you tried and what are you getting out of it? Based on the new details, you might need to change it to JOIN on both ID and MERCHANT. However the question on whether ID, MERCHANT keys from table1 will always be available in table2, and if not what would be the expected output still remains.
I still don't get why Arun's query doesn't work for what you wanted except sorting. So let us know in what case his query will fail.
Please try this.
Code:
select t4.ID,
t4.code
from
(SELECT
ID,
code,
case
when code = 'B' then 2
when code = 'A' then 3
when code = 'C' then 4
when code = 'D' then 5
end
FROM TABLE1 T1, TABLE2 T2
where
t1.Merchant = t2.Merchant
AND EXISTS ( select 1 form table2 t3
where t1.Merchant = t3.Merchant
and t3.code in ('A','B','C','D'))
UNION
SELECT
ID,
'X',
1
FROM TABLE1 T1, TABLE2 T2
where
t1.Merchant = t2.Merchant
AND EXISTS ( select 1 form table2 t3
where t1.Merchant = t3.Merchant
and t3.code not in ('A','B','C','D'))
AND NOT EXISTS ( select 1 form table2 t3
where t1.Merchant = t3.Merchant
and t3.code in ('A','B','C','D'))
order by 3) t4
