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

Query to compare 2 values of 1 column based on where clause


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

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Fri Mar 09, 2018 10:45 am
Reply with quote

Hi All,

There is a table called ROLES which is having 3 fields : MASTER_ID, ROLE_CODE & DIRECTORY_ID. For each MASTER-ID there are 3 values of ROLE_CODE (the values are 'OWN','PAY' & 'INS') and for each value of ROLE_CODE there is one DIRECTORY-ID. This DIRECTORY-ID can either be same or different.

Ex : -

Code:

MASTER_ID        ROLE_CODE         DIRECTORY_ID
-------------        --------------        ------------------
123456                  OWN                ABCDEF
123456                  PAY                  GHIJKL
123456                  INS                  ABCDEF
789012                  OWN                MNOPQR
789012                  PAY                  MNOPQR
789012                  INS                  MNOPQR


I want to fetch those MASTER_ID which is not having the same DIRECTORY_ID in ROLE_CODE 'OWN' & 'PAY'.

Code:

Expected Output :

MASTER_ID        ROLE_CODE         DIRECTORY_ID
-------------        --------------        ------------------
123456                  OWN                ABCDEF
123456                  PAY                  GHIJKL


Is it possible to write sql query for this. If yes, Please tell me how can we write this because when i trying to write it i am not getting from where i can start as i am a beginner in DB2 Querying and i have never written any query for scenario like this, so not getting from where i can start.


Many Thanks !
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Fri Mar 09, 2018 11:15 am
Reply with quote

Hi,

This is the query which is i am trying, not sure even if it will work

Code:
SELECT MASTER_ID,DIRECTORY_ID,ROLE_CODE FROM VACYONR1.ROLES   
WHERE ROLE_CODE IN ('OWN','PAY')                             
GROUP BY DIRECTORY_ID HAVING COUNT(DIRECTORY_ID)>1           



and this is the output i am getting

Code:

DSNT408I SQLCODE = -122, ERROR:  COLUMN OR EXPRESSION IN THE SELECT LIST IS NOT
         VALID                                                                 
DSNT418I SQLSTATE   = 42803 SQLSTATE RETURN CODE                               
DSNT415I SQLERRP    = DSNXOGB SQL PROCEDURE DETECTING ERROR                     
DSNT416I SQLERRD    = -200  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION           
DSNT416I SQLERRD    = X'FFFFFF38'  X'00000000'  X'00000000'  X'FFFFFFFF'       
         X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION


I have cross-checked the table name and column name, they are exactly the same.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Fri Mar 09, 2018 10:16 pm
Reply with quote

Try if this approach works.

Code:
select tablea.* from table a
where a.role_code in ('OWN','PAY')
and master_id in (
(select b.MASTER_ID
from table b
where b.role_code in ('OWN','PAY')
group by b.MASTER_ID
having count(DISTINCT(b.DIRECTORY_ID)) = 2 ))
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Fri Mar 09, 2018 10:53 pm
Reply with quote

I would do it this way:
Code:
SELECT A.MASTER_ID FROM ROLES A
 WHERE A.ROLE_CD = 'OWN'
    AND NOT EXISTS ( SELECT '1'
                     FROM ROLES B
                WHERE B.MASTER_ID = A.MASTER_ID
                   AND B.ROLE_CD = 'PAY'
                   AND B.DIRECTORY_ID = A.DIRECTORY_ID )
          WITH UR;
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Fri Mar 09, 2018 11:36 pm
Reply with quote

Rahul,

The OP wants both the rows, so might need slight tweaking.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Sat Mar 10, 2018 12:43 am
Reply with quote

I thought the OP only wanted the MASTER_ID and the data shown was for representation of those rows containing that one MASTER_ID.
Quote:
I want to fetch those MASTER_ID which is not having the same DIRECTORY_ID in ROLE_CODE 'OWN' & 'PAY'.

Anyways, OP must have got the idea on how to do it.

.
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Sat Mar 10, 2018 1:06 am
Reply with quote

I was looking at the OP's 'Expected output' in code tags.
Quote:
Anyways, OP must have got the idea on how to do it.
Agree.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Sat Mar 10, 2018 1:42 am
Reply with quote

RahulG31, Nice shot! Another outer Select on your query should give OP two rows if required.
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Sat Mar 10, 2018 2:35 am
Reply with quote

Rohit Umarjikar wrote:
Another outer Select on your query should give OP two rows if required.
May be not...
Code:
SELECT *                 
  FROM ROLES A                     
 WHERE A.ROLE_CD IN ('OWN','PAY')                   
   AND EXISTS (SELECT '1'                         
                 FROM ROLES B   
                WHERE B.MASTER_ID     = A.MASTER_ID   
                  AND B.ROLE_CD       IN ('OWN','PAY')
                  AND B.DIRECTORY_ID  <> A.DIRECTORY_ID)   
WITH UR;   
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Mon Mar 12, 2018 3:09 pm
Reply with quote

Thank you all for your replies. Thanks a lot. I am going to try these and let you know the results. Sorry for the late reply, for some reason i was not able to access internet from last 2 days.
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Wed Mar 14, 2018 7:13 am
Reply with quote

Hi Rohit,

I tried your query and it worked and gave expected results. Thanks a lot for helping me out.

This is the query which i ran :
Code:

SELECT A.MASTER_ID,A.DIRECTORY_ID,A.ROLE_CODE FROM ROLES A 
WHERE A.ROLE_CODE IN ('OWN','PAY')                                   
AND MASTER_ID IN (                                                   
(SELECT B.MASTER_ID                                                 
FROM ROLES B                                               
WHERE B.ROLE_CODE IN ('OWN','PAY')                                   
GROUP BY B.MASTER_ID                                                 
HAVING COUNT(DISTINCT(B.DIRECTORY_ID)) = 2 ))                       


Further, i was trying to use the DIRECTORY_ID from the result of this query to fetch the data from the row which exists in another table, i.e. (NMADX). This NMADX table has a NAME field for every DIRECTORY_ID.

I was trying to use the above query in sub-query using WHERE DIRECTORY_ID IN clause but the query is not completing successfully and giving error. Can you please suggest how can i handle this situation.


Thanks !
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Wed Mar 14, 2018 7:29 pm
Reply with quote

Quote:
the query is not completing successfully and giving error
Can you post the exact error message?
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 Mar 15, 2018 12:08 am
Reply with quote

Quote:
I was trying to use the above query in sub-query using WHERE DIRECTORY_ID IN clause but the query is not completing successfully and giving error. Can you please suggest how can i handle this situation.

You will get error because in above SELECT query (which is working) there are other columns apart from DIRECTOR_ID. if you just need DIRECTOR_ID look up then remove rest all columns from SELECT and use it in IN , that should work.
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Sat Mar 17, 2018 9:11 am
Reply with quote

Hi Arun,
As Rohit has mentioned, i was using multiple columns in SELECT statement of my sub-query and that is why it was resulting in error.


Hi Rohit,
When i changed the sub-query from multiple columns in SELECT to only DIRECTORY_ID column it worked fine and i received the expected results. Thanks again for pointing me in right direction icon_smile.gif


Thanks Rahul for your responses. Appreciate that !


Below is the query which i used :

Code:
SELECT DIRECTORY_ID,NAME FROM NMADX WHERE   
DIRECTORY_ID IN                                       
 (SELECT A.DIRECTORY_ID FROM ROLES A         
  WHERE A.ROLE_CODE IN ('OWN','PAY')                 
  AND MASTER_ID IN                                   
   (SELECT B.MASTER_ID                               
    FROM ROLES B                             
    WHERE B.ROLE_CODE IN ('OWN','PAY')               
    GROUP BY B.MASTER_ID                             
    HAVING COUNT(DISTINCT(B.DIRECTORY_ID)) = 2 ))     
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 Compare only first records of the fil... SYNCSORT 7
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts How to load to DB2 with column level ... DB2 6
No new posts RC query -Time column CA Products 3
Search our Forums:

Back to Top