IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SQL query to run through list of values in table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Ni3-db2

New User


Joined: 14 Dec 2016
Posts: 4
Location: India

PostPosted: Wed Dec 14, 2016 9:52 am
Reply with quote

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
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Wed Dec 14, 2016 11:50 am
Reply with quote

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
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Wed Dec 14, 2016 8:42 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Wed Dec 14, 2016 11:15 pm
Reply with quote

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
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Dec 15, 2016 1:12 am
Reply with quote

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.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Dec 15, 2016 2:23 am
Reply with quote

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.
Code:
ID2 X
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Dec 15, 2016 2:47 am
Reply with quote

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
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Thu Dec 15, 2016 3:08 am
Reply with quote

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
Code:
ID
1
2
3

And Table 2 as:
Code:
CODE
A
B
C
D
E
F
G

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
View user's profile Send private message
Ni3-db2

New User


Joined: 14 Dec 2016
Posts: 4
Location: India

PostPosted: Thu Dec 15, 2016 9:37 am
Reply with quote

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

Code:
CODE
A
B1
C1


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.

Code:
abc  A
abc  A


if there is no valid code

output expected is

Code:
abc X
abc X


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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Dec 15, 2016 11:20 am
Reply with quote

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
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Thu Dec 15, 2016 11:47 am
Reply with quote

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
View user's profile Send private message
Ni3-db2

New User


Joined: 14 Dec 2016
Posts: 4
Location: India

PostPosted: Thu Dec 15, 2016 5:42 pm
Reply with quote

i came up the sql what arun wote , that will not work as it will give multiple rows.
Back to top
View user's profile Send private message
Ni3-db2

New User


Joined: 14 Dec 2016
Posts: 4
Location: India

PostPosted: Thu Dec 15, 2016 5:48 pm
Reply with quote

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
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Dec 15, 2016 7:12 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Dec 15, 2016 10:12 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts RC query -Time column CA Products 3
Search our Forums:

Back to Top