|
View previous topic :: View next topic
|
| Author |
Message |
Krishnadeva Reddy
New User
Joined: 14 Nov 2007
Posts: 37
Location: Chennai
|
|
|
|
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 |
|
 |
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
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 |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
How are you using id?
|
|
| Back to top |
|
|
Krishnadeva Reddy
New User
Joined: 14 Nov 2007
Posts: 37
Location: Chennai
|
|
|
|
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 |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| Quote: |
| AND TABLE2.COMPANY_ID = 'AG' |
Isn't hardcoding for the given case?
See, if this blows out 
| 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 |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| I thought , TS represented the test data as it wouldn't work for others. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| 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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| cust 123 is connected to active company AL, so why should it be in the expected result ? |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Why not join A and B and get the desired result ? |
|
| Back to top |
|
|
AnandhaMuruganB
New User
Joined: 02 Aug 2016
Posts: 2
Location: India
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
GuyC,
beacaue the cust id is per org id, 27 is only contains one INACT. |
|
| Back to top |
|
|
AnandhaMuruganB
New User
Joined: 02 Aug 2016
Posts: 2
Location: India
|
|
|
|
| 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| How do you find org_id without tablec? |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
I don't think correlated query needs all the tables again as
a. outer query already filtering all ACT accounts and getting org_id
b Next task is for that org_id even if any one has (ACT/SUSP) status
then that org_id shouldn't appear
However, TS is least bothered for these replies as TS never turned back with these options. So its waste of time spending on this post henceforth and topic may be locked. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
when a topic does not reach a conclusion after a reasonable number of replies
it should be locked !
and now it is. |
|
| Back to top |
|
|
|
|
