View previous topic :: View next topic
|
Author |
Message |
Poha Eater
New User
Joined: 31 Aug 2016 Posts: 74 Location: India
|
|
|
|
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 |
|
|
Poha Eater
New User
Joined: 31 Aug 2016 Posts: 74 Location: India
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
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 |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Rahul,
The OP wants both the rows, so might need slight tweaking. |
|
Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
RahulG31, Nice shot! Another outer Select on your query should give OP two rows if required. |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
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 |
|
|
Poha Eater
New User
Joined: 31 Aug 2016 Posts: 74 Location: India
|
|
|
|
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 |
|
|
Poha Eater
New User
Joined: 31 Aug 2016 Posts: 74 Location: India
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
Quote: |
the query is not completing successfully and giving error |
Can you post the exact error message? |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
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 |
|
|
Poha Eater
New User
Joined: 31 Aug 2016 Posts: 74 Location: India
|
|
|
|
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
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 |
|
|
|