Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need help on a SQL query
Goto page 1, 2  Next
 
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Krishnadeva Reddy

New User


Joined: 14 Nov 2007
Posts: 36
Location: Chennai

PostPosted: Thu Jul 21, 2016 11:30 pm    Post subject: Need help on a SQL query
Reply with quote

Hello,

I have 3 tables. Table A contains Customer details and key is Cust_id and status as ACT or INACT. Table B contains Org details and key is Org_id and status as ACT or INACT or SUSP, and company code. Table C is join table between Table A and Table B.

One Cust_id can be associated with more than one Org_id and one Org_id can have more than one company code.

Report Output: List of customers that are ACT and not having a single ACT org_id.

Lets say Cust_id XYZ is active and attached to 3 Org_id's - O1(ACT), O2(INACT), O3(INACT) - should not be on report.
Cust_id ABC is active and attached to 3 org_id's - O1(INACT), O2(INACT), O3(INACT) - should be on the report.

Code:

Table A:
Cus_id   Status   Date
123        ACT         1/1/2016
123        INACT         (NULL)
100        ACT         2/1/2015
100        INACT     (NULL)

Table B:
Id   Org_id   Company_cd       Status       End_dt
101   21         AG               ACT        (NULL)
102   21         AK               INACT       (NULL)
103   21         AK               SUSP        2/1/2015
105   22         AL               INACT       (NULL)
106   22         AK               ACT       (NULL)
107   22         AG               SUSP          3/1/2014
108   27         AG               INACT       (NULL)

Table C:
Id   Org_id   Cus_id
202   21           100
203   22           123
201   27           123

Expected Output:
Cus_id     Org_id   Status
123          27        INACT



I tried with below query:
Select X.Cus_id, Y.Org_id, Y.Status
From TableA X, TableB Y, TableC Z
Where X.Cus_id = Z.Cus_id
AND Y.Org_id = Z.Org_id
AND X.Status = 'ACT'
AND Z.Org_id in (
Select Y.Org_id
From TableB Y
Where Y.Status NOT IN ('ACT', 'SUSP')
AND Y.End_dt IS NULL
)

When I ran the query I got rows with status ACT also from table Y. Please advise.
Back to top
View user's profile Send private message

RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Fri Jul 22, 2016 12:10 am    Post subject: Reply to: Need help on a SQL query
Reply with quote

It seems that an Org_id can have multiple status ACT and INACT (both at the same time) with end_dt as null. It doesn't look logical if you are not using company code in query.

And your subquery is of No help either.

and also just clarify if you have org_id as key (which can't be the case as I see multiple) OR is it a composite key of multiple columns.

.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Jul 22, 2016 12:55 am    Post subject:
Reply with quote

See if this works.
Code:
Select X.Cus_id, Y.Org_id, Y.Status
 From TableA X, TableB Y, TableC Z
 Where
        X.Status = 'ACT'
 AND X.Cus_id = Z.Cus_id
 AND Y.Org_id = Z.Org_id
 AND Y.id        = Z.id
 AND NOT EXIST( select 1 from TableB X1
                          where X1.Id = Y.Id
                             and X1.Org_Id - Y.Org_id
                             and X1.Status NOT IN ('ACT','SUSP'))
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Fri Jul 22, 2016 1:02 am    Post subject: Reply to: Need help on a SQL query
Reply with quote

How are you using id?
Code:
 AND Y.id        = Z.id
Back to top
View user's profile Send private message
Krishnadeva Reddy

New User


Joined: 14 Nov 2007
Posts: 36
Location: Chennai

PostPosted: Fri Jul 22, 2016 1:16 am    Post subject: Reply to: Need help on a SQL query
Reply with quote

Yes, I can use company code to say Company_cd = 'AG'.

And yes 'Id" cannot be used as there is no association between Id in table B and table C.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Fri Jul 22, 2016 1:31 am    Post subject: Reply to: Need help on a SQL query
Reply with quote

How much data do you have on the tables? I have to ask this as it's easier to do a NOT IN for Cus_id but if you have millions of Cus_id then it probably won't work/take a long time.

If not much data then try this (I know it only gives cus_id but try it and let us know):
Code:
    SELECT CUS_ID FROM TABLE1
    WHERE CUS_ID NOT IN (
    SELECT CUS_ID FROM TABLE1, TABLE3, TABLE2
    WHERE CUS_ID_STATUS = 'ACT'
      AND TABLE1.CUS_ID = TABLE3.CUS_ID
      AND TABLE3.ORG_ID = TABLE2.ORG_ID
      AND TABLE2.COMPANY_ID = 'AG'
      AND TABLE2.END_DT IS NULL
      AND TABLE2.STATUS IN ('ACT','SUSP'))
      AND CUS_ID_STATUS = 'ACT'   

.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Jul 22, 2016 2:36 am    Post subject:
Reply with quote

Quote:
AND TABLE2.COMPANY_ID = 'AG'
Isn't hardcoding for the given case?
See, if this blows out icon_smile.gif
Code:
Select X.Cus_id, Y.Org_id, Y.Status
  From TableA X, TableB Y, TableC Z,
  (select Org_id, count(*) as eligible from TableB
    where Status IN ('ACT','SUSP')
      ANd End_dt IS NULL
     group by Org_id ) as X1
  Where
         X.Status = 'ACT'
  AND X.Cus_id = Z.Cus_id
  AND Y.Org_id = Z.Org_id
  AND Y.id        = Z.id
  AND X1.Org_id = Z.Org_id
  AND X1.Org_id = X1.Org_id
  AND x1.eligible = 0
  AND x1.eligible = x1.eligible
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 331
Location: USA

PostPosted: Fri Jul 22, 2016 3:15 am    Post subject: Reply to: Need help on a SQL query
Reply with quote

Yes, it's hardcoded and that is what the OP said:
Quote:
Yes, I can use company code to say Company_cd = 'AG'.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Jul 22, 2016 3:24 am    Post subject:
Reply with quote

I thought , TS represented the test data as it wouldn't work for others.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Jul 22, 2016 10:34 am    Post subject:
Reply with quote

There needs a modification to my query, I shall post tomorrow but see if you can pick the idea and make it work
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Aug 01, 2016 3:30 pm    Post subject:
Reply with quote

cust 123 is connected to active company AL, so why should it be in the expected result ?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Mon Aug 01, 2016 5:16 pm    Post subject:
Reply with quote

Why not join A and B and get the desired result ?
Back to top
View user's profile Send private message
AnandhaMuruganB

New User


Joined: 02 Aug 2016
Posts: 2
Location: India

PostPosted: Wed Aug 03, 2016 10:33 am    Post subject: Reply to: Need help on a SQL query
Reply with quote

Hi,
Please try the below query. I believe that it will give you the desired output.
For every Org_id filtered by the main query, it is going to check the sub-query (Correlated).

SELECT
A.Cust, B.Org_id, B.Status
from
TABLEA X,
TABLEB y,
TABLEC z
WHERE
x.STATUS='ACT'
AND
X.Cust_id=Z.Cust_id
AND
Z.Org_id=Y.Org_id
AND
Y.Org_id NOT IN
-- Correlated subquery
(
SELECT
ORG_ID
FROM
TABLEB Y1
where
B1.Org_id=B.Org_id
and
B.Status = 'ACT'
)
with ur;
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed Aug 03, 2016 4:24 pm    Post subject:
Reply with quote

Quote:
Why not join A and B and get the desired result ?

Because there is no relationship.
Anand,
1. code tags missing
2. wrong Alias names referenced all over.
3. end_dt , the null condition is missing.
4. How do you ensure to block the cus_id if there is a even single stat 'ACT' per org_id?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed Aug 03, 2016 4:35 pm    Post subject:
Reply with quote

GuyC,
beacaue the cust id is per org id, 27 is only contains one INACT.
Back to top
View user's profile Send private message
AnandhaMuruganB

New User


Joined: 02 Aug 2016
Posts: 2
Location: India

PostPosted: Wed Aug 03, 2016 6:55 pm    Post subject:
Reply with quote

Rohit Umarjikar wrote:
Quote:
Why not join A and B and get the desired result ?

Because there is no relationship.
Anand,
1. code tags missing
2. wrong Alias names referenced all over.
3. end_dt , the null condition is missing.
4. How do you ensure to block the cus_id if there is a even single stat 'ACT' per org_id?


Thank you Rohit. I have modified the query as below and tried to answer your questions. Please see if this will help.

SELECT
X.Cust, Y.Org_id, Y.Status
from
TABLEA X,
TABLEB y,
TABLEC z
WHERE
x.STATUS='ACT'
AND
X.Cust_id=Z.Cust_id
AND
Z.Org_id=Y.Org_id
AND
NOT EXISTS
-- Correlated subquery
(
SELECT
'1'
FROM
TABLEB Y1
where
Y1.Org_id=Y.Org_id
and
Y1.Status = 'ACT'
)
with ur;

Main Query:
SELECT
X.Cust, X.STATUS Y.Org_id, Y.Status
from
TABLEA X,
TABLEB y,
TABLEC z
WHERE
x.STATUS='ACT'
AND
X.Cust_id=Z.Cust_id
AND
Z.Org_id=Y.Org_id

1. code tags missing - Do you mean the code snippet from mainframe.
I am sorry, currently i do not have access to mainframes

2. wrong Alias names referenced all over - Thank you. I have corrected it now.

3. end_dt , the null condition is missing. - Requirement doesn't imply any filter condition based on END_DT column
It talks only about Status of the Cust_id and Org_id which is readily available in column Status of TableB. If the status has to checked up to date, then we have to modify accordingly

4. How do you ensure to block the cus_id if there is a even single stat 'ACT' per org_id? - Given below, I have tried to spilit the main query result and subquery result set
as i expected it to run. please correct if i am wrong.


Main query is expected to pull 7 rows in the result set by joining the 3 tables.
But for each row pulled from main query, sub query will be executed and "NOT EXISTS" clause check will be performed.
Ex: First 3 rows in the result has Y.Org_id as 22.
When the correlated sub query is run for this Org_id (22), it Exists since there is a row with status "ACT" in table B.
So, the "NOT EXISTS" clause fails and the row is not pulled to the final result set. Whereas "NOT EXISTS" clause passes for the 4th row and it will be reported to final output.

Main Query:

SELECT
X.Cust, X.STATUS Y.Org_id, Y.Status
from
TABLEA X,
TABLEB y,
TABLEC z
WHERE
x.STATUS='ACT'
AND
X.Cust_id=Z.Cust_id
AND
Z.Org_id=Y.Org_id

Result Set from Main Query:

X.Cust_id | X.Cust_Status | Y.Org_id | Y.Company_cd | Y.Status | Is the row Filtered with respect to Correlated subquery
123 ACT 22 AL INACT Yes, Filtered
123 ACT 22 AK ACT Yes, Filtered
123 ACT 22 AG SUSP Yes, Filtered
123 ACT 27 AG INACT No, Reported to the output
100 ACT 21 AG ACT Yes, Filtered
100 ACT 21 AK INACT Yes, Filtered
100 ACT 21 AK SUSP Yes, Filtered

SUB QUERY:
AND NOT EXISTS
-- Correlated subquery
(
SELECT
'1'
FROM
TABLEB Y1
where
Y1.Org_id=Y.Org_id
and
Y1.Status = 'ACT'
)
with ur;
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed Aug 03, 2016 10:04 pm    Post subject:
Reply with quote

1.
Quote:
I am sorry, currently i do not have access to mainframes
You don't have to, since you missed to notice the tags while you post , there is something named 'Code' in that little sq box, you need to make a use of that, BBCode.
3.
Quote:
Requirement doesn't imply any filter condition based on END_DT column
Surely it does if you notice the query posted by TS.
4. How does your query substantiate my first solution given above?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Aug 04, 2016 12:40 pm    Post subject:
Reply with quote

Rohit Umarjikar wrote:
Quote:
Why not join A and B and get the desired result ?

Because there is no relationship.


I don't agree, Because according to TS Table C is join between Table A and Table B.


So the result should be achievable by joining A & B
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Thu Aug 04, 2016 5:13 pm    Post subject:
Reply with quote

How do you find org_id without tablec?
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Tue Aug 09, 2016 2:49 pm    Post subject:
Reply with quote

Hi,

I guess checking for ORG_ID in subquery will not work here..

We will need ti check if that customer has a single active row present then do not select it as Rahul suggested in one of the answer

Check if below works..

Code:
Select X.Cus_id, Y.Org_id, Y.Status
From TableA X, TableB Y, TableC Z
Where X.Cus_id = Z.Cus_id
AND Y.Org_id = Z.Org_id
AND X.Status = 'ACT
NOT EXISTS
(
SELECT 1 FROM TABLE1, TABLE3, TABLE2
    WHERE TABLE1_CUSTID = X.CUST_ID
      AND TABLE1.CUS_ID = TABLE3.CUS_ID
      AND TABLE3.ORG_ID = TABLE2.ORG_ID
      AND TABLE2.END_DT IS NULL
      AND TABLE2.STATUS IN ('ACT','SUSP'))
      FETCH FIRST ONE ROW ONLY 
)
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us