|
View previous topic :: View next topic
|
| Author |
Message |
Ni3-db2
New User
Joined: 14 Dec 2016
Posts: 4
Location: India
|
|
|
|
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. |
|
| Back to top |
|
 |
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
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;
|
. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
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. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
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
|
|
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
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:
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:
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. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
| Quote: |
| I see your code will give this for Case1: |
I think, you missed to look at my order by.
| Quote: |
| 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: |
Your left outer join will give this and not just ABC.
| Code: |
ID1 A
ID1 B
ID1 C
ID1 X
ID1 X |
| Quote: |
| 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: |
This will come 4 times unless we use DISTINCT.
|
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
I was mentioning about the contents and not the ORDER. Let's keep the order aside for now.
| Rohit Umarjikar wrote: |
| Your left outer join will give this and not just ABC |
NO. My right table does not even select entries other than A,B,C,D. So how do you expect it to return the 2 extra Xs in case1 or the 4 Xs in case2? |
|
| Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
| Quote: |
| RahulG31, I think, you are missing the relation between two tables. |
I know. But from the original post, it is Not entirely clear whether the Table 2 will have an ID column (or possibly Not).
The tables may be like:
Table 1 as
And Table 2 as:
And then, what the TS wants may be something like:
| Code: |
ID CODE
1 X
1 X
1 X
1 B
1 A
1 C
1 D
2 X
2 X
2 X
2 B
2 A
2 C
2 D
And so on . . .
|
|
|
| Back to top |
|
|
Ni3-db2
New User
Joined: 14 Dec 2016
Posts: 4
Location: India
|
|
|
|
First of all thank you for your reply guys.
Sorry if i was not able to make requirment clear in first place.
Table 1 has only ID and there is no CODE column in it
Table 2 has CODE and no ID in it.
We match these tables based on some other common column.
Here is sample
Table 1
| Code: |
ID timestamp
abc 12:00
abc 13:00 |
Table 2
so one ID have three code. so here there will be total 6 rows.
The output expected is
Since A is one of the valid code.
if there is no valid code
output expected is
so if valid value of code is there same value should be repeated for that ID (if there are multiple ID rows are there in Table 1)
also in Table 2 there can be only one valid value of the CODE
that means there will be either A,B,C or D though it may have any other values of CODE but valid value can only be one.
Hope i am clear with requirement now. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
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 ? |
|
| Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
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; |
|
|
| Back to top |
|
|
Ni3-db2
New User
Joined: 14 Dec 2016
Posts: 4
Location: India
|
|
|
|
| i came up the sql what arun wote , that will not work as it will give multiple rows. |
|
| Back to top |
|
|
Ni3-db2
New User
Joined: 14 Dec 2016
Posts: 4
Location: India
|
|
|
|
One more try to explian req.
Table 1
| Code: |
ID TIME MERCHANT
ABC 12:00 1
ABC 13:00 1 |
Table 2
| Code: |
Code Merchant
A 1
B1 1
C1 1 |
Output
| Code: |
ABC 12:00 A
ABC 13:00 A
|
IF CODE IS NOT ONE OF THE VALID CODE (A,B,C,D)
That is if the table 2 is as below
Table 2
| Code: |
Code Merchant
A1 1
B1 1
C1 1 |
Output
| Code: |
ABC 12:00 X
ABC 13:00 X |
|
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
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. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
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 |
|
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
