|
View previous topic :: View next topic
|
| Author |
Message |
chavinash2004
New User
Joined: 30 Jun 2010
Posts: 37
Location: hyderabad
|
|
|
|
Hi Team,
Need help in below requirement.
I wrote below but it's working as expected:
| Code: |
SELECT A.MGR
,'|'
,A.CDEMGR
,'|'
,A.CDELVL3
,'|'
,B.MGR_PGM
,'|'
,CASE(C.MGR_ATR)
WHEN 'BBBBBB' THEN 'Y'
ELSE 'N'
END AS ATRFLG
FROM INVMGR A
,MGRPGM B
,MGR_ATTRB C
WHERE A.MGR = B.MGR
AND A.MGR = C.MGR
WITH UR; |
Example One:
| Code: |
------+---------+---------+---------+---------+---------+---------
MGR CDEMGR CDELVL3 MGR_PGM MGR_ATR
------+---------+---------+---------+---------+---------+---------
35 AICA FATCOR AVI AAAAAA
35 AICA FATCOR AVI BBBBBB
35 AICA FATCOR AVI CCCCCC
|
Getting output with above query.
| Code: |
------+---------+---------+---------+---------+----+---------
MGR CDEMGR CDELVL3 MGR_PGM ATRFLG
------+---------+---------+---------+---------+---------+---------
35 | AICA | FATCOR | AVI | Y
35 | AICA | FATCOR | AVI | N
35 | AICA | FATCOR | AVI | N
|
Expected output should be as below since MGR_ATR as "BBBBBB" so the ATRFLG should be "Y".
| Code: |
------+---------+---------+---------+---------+----+---------
MGR CDEMGR CDELVL3 MGR_PGM ATRFLG
------+---------+---------+---------+---------+---------+---------
35 | AICA | FATCOR | AVI | Y
|
Example Two:
| Code: |
------+---------+---------+---------+---------+---------+---------
MGR CDEMGR CDELVL3 MGR_PGM MGR_ATR
------+---------+---------+---------+---------+---------+---------
35 AICA FATCOR AVI AAAAAA
35 AICA FATCOR AVI CCCCCC
|
Getting output with above query.
| Code: |
------+---------+---------+---------+---------+----+---------
MGR CDEMGR CDELVL3 MGR_PGM ATRFLG
------+---------+---------+---------+---------+---------+---------
35 | AICA | FATCOR | AVI | N
35 | AICA | FATCOR | AVI | N
|
Expected output should be as below since there is no record with MGR_ATR as "BBBBBB" in example 2, so the ATRFLG should be "N".
| Code: |
------+---------+---------+---------+---------+----+---------
MGR CDEMGR CDELVL3 MGR_PGM ATRFLG
------+---------+---------+---------+---------+---------+---------
35 | AICA | FATCOR | AVI | N
|
The query is returning with all records either "Y" or "N" but i need only one record as a output. can someone please help me on this.
Thanks a lot in advance. |
|
| Back to top |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2209
Location: USA
|
|
|
|
There is not any restrictive option in your query, to selectively display only one record out of three or more presented in you example.
Probably, you need extra WHERE condition, or something else to limit the query output. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3087
Location: NYC,USA
|
|
|
|
Simple fix should solve, try.
| Code: |
SELECT A.MGR
,'|'
,A.CDEMGR
,'|'
,A.CDELVL3
,'|'
,B.MGR_PGM
,'|'
,CASE(C.MGR_ATR)
WHEN 'BBBBBB' THEN 'Y'
ELSE 'N'
END AS ATRFLG
FROM INVMGR A
,MGRPGM B
,MGR_ATTRB C
WHERE A.MGR = B.MGR
AND A.MGR = C.MGR
ORDER BY ATRFLG DESC
FETCH FIRST 1 ROWS ONLY
WITH UR; |
|
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2209
Location: USA
|
|
|
|
I guess, the TS needs to get 1 row FOR EVERY manager, not ONE ROW at all, for many managers?
I tried to make the TS thinking a little bit, but new ready-to-use answers are coming from everywhere… |
|
| Back to top |
|
|
chavinash2004
New User
Joined: 30 Jun 2010
Posts: 37
Location: hyderabad
|
|
|
|
Thank you Rohit for ur quick response. This is not working for second example.
| Rohit Umarjikar wrote: |
Simple fix should solve, try.
| Code: |
SELECT A.MGR
,'|'
,A.CDEMGR
,'|'
,A.CDELVL3
,'|'
,B.MGR_PGM
,'|'
,CASE(C.MGR_ATR)
WHEN 'BBBBBB' THEN 'Y'
ELSE 'N'
END AS ATRFLG
FROM INVMGR A
,MGRPGM B
,MGR_ATTRB C
WHERE A.MGR = B.MGR
AND A.MGR = C.MGR
ORDER BY ATRFLG DESC
FETCH FIRST 1 ROWS ONLY
WITH UR; |
|
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3087
Location: NYC,USA
|
|
|
|
| chavinash2004 wrote: |
Thank you Rohit for ur quick response. This is not working for second example.
|
What do you mean its not working? you will always get 1 row, if there is one as either Y or N. So show us what you got as a result for second case.
| sergeyken wrote: |
I guess, the TS needs to get 1 row FOR EVERY manager, not ONE ROW at all, for many managers?
I tried to make the TS thinking a little bit, but new ready-to-use answers are coming from everywhere… |
TS has duplicates (except last col) in the input and with the sample data its the same manager, so for second case either one should come out as a result of being N ( <> BBBBBB).
'ready-to-use answers'- as long as ithe trend is not repeated by same TS it is fine else they got to do research and spend time and give a try and then post. |
|
| Back to top |
|
|
chavinash2004
New User
Joined: 30 Jun 2010
Posts: 37
Location: hyderabad
|
|
|
|
I'm sorry if examples are not clear.
Let me provide you in detail.
My data will be like:
| Code: |
------+---------+---------+---------+---------+---------+---------
MGR CDEMGR CDELVL3 MGR_PGM MGR_ATR
------+---------+---------+---------+---------+---------+---------
35 AICA FATCOR AVI AAAAAA
35 AICA FATCOR AVI BBBBBB
35 AICA FATCOR AVI CCCCCC
3 AVIX EASLC AVIN DDDDDD
3 AVIX EASLC AVIN AAAAAAA
3 AVIX EASLC AVIN EEEEEEE
76 AGF FTCOR AVI CCCCCC
76 AGF FTCOR AVI AAAAAA
76 AGF FTCOR AVI ZZZZZZ
76 AGF FTCOR AVI BBBBBB
76 AGF FTCOR AVI IIIIII
|
My expected output should be as below:
| Code: |
------+---------+---------+---------+---------+---------+---------
MGR CDEMGR CDELVL3 MGR_PGM ATRFLG
------+---------+---------+---------+---------+---------+---------
35 AICA FATCOR AVI Y (Because there is one record with BBBBBB for this combination)
3 AVIX EASLC AVIN N (There is NO BBBBBB record for this combination)
76 AGF FTCOR AVI Y (Because there is one record with BBBBBB for this combination)
|
Hope this helps.
Thanks again Rohit and sergeyken.
| Rohit Umarjikar wrote: |
| chavinash2004 wrote: |
Thank you Rohit for ur quick response. This is not working for second example.
|
What do you mean its not working? you will always get 1 row, if there is one as either Y or N. So show us what you got as a result for second case.
| sergeyken wrote: |
I guess, the TS needs to get 1 row FOR EVERY manager, not ONE ROW at all, for many managers?
I tried to make the TS thinking a little bit, but new ready-to-use answers are coming from everywhere… |
TS has duplicates (except last col) in the input and with the sample data its the same manager, so for second case either one should come out as a result of being N ( <> BBBBBB).
'ready-to-use answers'- as long as ithe trend is not repeated by same TS it is fine else they got to do research and spend time and give a try and then post. |
|
|
| Back to top |
|
|
chavinash2004
New User
Joined: 30 Jun 2010
Posts: 37
Location: hyderabad
|
|
|
|
Thanks a lot for all your help.
Looks like i'm getting the expected result. I will post you if i need any help.
| chavinash2004 wrote: |
I'm sorry if examples are not clear.
Let me provide you in detail.
My data will be like:
| Code: |
------+---------+---------+---------+---------+---------+---------
MGR CDEMGR CDELVL3 MGR_PGM MGR_ATR
------+---------+---------+---------+---------+---------+---------
35 AICA FATCOR AVI AAAAAA
35 AICA FATCOR AVI BBBBBB
35 AICA FATCOR AVI CCCCCC
3 AVIX EASLC AVIN DDDDDD
3 AVIX EASLC AVIN AAAAAAA
3 AVIX EASLC AVIN EEEEEEE
76 AGF FTCOR AVI CCCCCC
76 AGF FTCOR AVI AAAAAA
76 AGF FTCOR AVI ZZZZZZ
76 AGF FTCOR AVI BBBBBB
76 AGF FTCOR AVI IIIIII
|
My expected output should be as below:
| Code: |
------+---------+---------+---------+---------+---------+---------
MGR CDEMGR CDELVL3 MGR_PGM ATRFLG
------+---------+---------+---------+---------+---------+---------
35 AICA FATCOR AVI Y (Because there is one record with BBBBBB for this combination)
3 AVIX EASLC AVIN N (There is NO BBBBBB record for this combination)
76 AGF FTCOR AVI Y (Because there is one record with BBBBBB for this combination)
|
Hope this helps.
Thanks again Rohit and sergeyken.
| Rohit Umarjikar wrote: |
| chavinash2004 wrote: |
Thank you Rohit for ur quick response. This is not working for second example.
|
What do you mean its not working? you will always get 1 row, if there is one as either Y or N. So show us what you got as a result for second case.
| sergeyken wrote: |
I guess, the TS needs to get 1 row FOR EVERY manager, not ONE ROW at all, for many managers?
I tried to make the TS thinking a little bit, but new ready-to-use answers are coming from everywhere… |
TS has duplicates (except last col) in the input and with the sample data its the same manager, so for second case either one should come out as a result of being N ( <> BBBBBB).
'ready-to-use answers'- as long as ithe trend is not repeated by same TS it is fine else they got to do research and spend time and give a try and then post. |
|
|
|
| Back to top |
|
|
chavinash2004
New User
Joined: 30 Jun 2010
Posts: 37
Location: hyderabad
|
|
|
|
One of my friend helped me to resolve this.
Here is the query
| Code: |
SELECT A.MGR
,'|'
,A.CDEMGR
,'|'
,A.CDELVL3
,'|'
,B.MGR_PGM
,'|'
,CASE(SUM(TAB.ATRFLG))
WHEN 0 THEN 'N'
ELSE 'Y'
END AS INV_MGR_ATRF
FROM INVMGR A
,INVMGR_PGM B
,(SELECT C.MGR,CASE(C.MGR_ATR)
WHEN 'BBBBBB' THEN 1
ELSE 0
END AS ATRFLG FROM MGR_ATTRB C) AS TAB
WHERE A.MGR = B.MGR
AND B.MGR_PGM IN ('AAA','YYY','ZZZ')
AND A.MGR = TAB.MGR
GROUP BY A.MGR
,A.CDEMGR
,A.CDELVL3
,B.MGR_PGM
WITH UR;
|
|
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2209
Location: USA
|
|
|
|
When dealing with SQL queries you must be familiar with constructions such as GROUP BY (and a number of others). Otherwise there is no chance to create any useful product.
Your post demonstrated that you’ve had no idea of GROUP BY, despite some hints given by others. |
|
| Back to top |
|
|
chavinash2004
New User
Joined: 30 Jun 2010
Posts: 37
Location: hyderabad
|
|
|
|
Thanks for the feedback Serge.
Can you please help me similar query in better way.
| sergeyken wrote: |
When dealing with SQL queries you must be familiar with constructions such as GROUP BY (and a number of others). Otherwise there is no chance to create any useful product.
Your post demonstrated that you’ve had no idea of GROUP BY, despite some hints given by others. |
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3087
Location: NYC,USA
|
|
|
|
TRY, this is as simple as it goes, delete pipes' if needed and retry.
| Code: |
SELECT A.MGR
,'|'
,A.CDEMGR
,'|'
,A.CDELVL3
,'|'
,B.MGR_PGM
,'|'
,MAX(CASEC.MGR_ATR WHEN 'BBBBBB' THEN 'Y' ELSE 'N' END AS ATRFLG)
FROM INVMGR A
,MGRPGM B
,MGR_ATTRB C
WHERE A.MGR = B.MGR
AND B.MGR = C.MGR << It should be B.MGR and not A.MGR, review.
GROUP BY
A.MGR
,A.CDEMGR
,A.CDELVL3
,B.MGR_PGM
WITH UR; |
|
|
| Back to top |
|
|
chavinash2004
New User
Joined: 30 Jun 2010
Posts: 37
Location: hyderabad
|
|
|
|
Thank you Rohit. I tried this and compared with previous output both are matching.
Thanks a lot for help me on this.
| Rohit Umarjikar wrote: |
TRY, this is as simple as it goes, delete pipes' if needed and retry.
| Code: |
SELECT A.MGR
,'|'
,A.CDEMGR
,'|'
,A.CDELVL3
,'|'
,B.MGR_PGM
,'|'
,MAX(CASEC.MGR_ATR WHEN 'BBBBBB' THEN 'Y' ELSE 'N' END AS ATRFLG)
FROM INVMGR A
,MGRPGM B
,MGR_ATTRB C
WHERE A.MGR = B.MGR
AND B.MGR = C.MGR << It should be B.MGR and not A.MGR, review.
GROUP BY
A.MGR
,A.CDEMGR
,A.CDELVL3
,B.MGR_PGM
WITH UR; |
|
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3087
Location: NYC,USA
|
|
|
|
| Welcome! |
|
| Back to top |
|
|
|
|
|
