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

[Solved]DB2: Need helping below DB2 query


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

New User


Joined: 30 Jun 2010
Posts: 37
Location: hyderabad

PostPosted: Fri Jul 16, 2021 7:13 pm
Reply with quote

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
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2140
Location: USA

PostPosted: Fri Jul 16, 2021 8:17 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Fri Jul 16, 2021 8:18 pm
Reply with quote

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
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2140
Location: USA

PostPosted: Fri Jul 16, 2021 8:24 pm
Reply with quote

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
View user's profile Send private message
chavinash2004

New User


Joined: 30 Jun 2010
Posts: 37
Location: hyderabad

PostPosted: Fri Jul 16, 2021 8:28 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Fri Jul 16, 2021 11:11 pm
Reply with quote

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
View user's profile Send private message
chavinash2004

New User


Joined: 30 Jun 2010
Posts: 37
Location: hyderabad

PostPosted: Fri Jul 16, 2021 11:51 pm
Reply with quote

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
View user's profile Send private message
chavinash2004

New User


Joined: 30 Jun 2010
Posts: 37
Location: hyderabad

PostPosted: Sun Jul 18, 2021 4:14 pm
Reply with quote

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
View user's profile Send private message
chavinash2004

New User


Joined: 30 Jun 2010
Posts: 37
Location: hyderabad

PostPosted: Sun Jul 18, 2021 5:01 pm
Reply with quote

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
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2140
Location: USA

PostPosted: Sun Jul 18, 2021 5:29 pm
Reply with quote

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
View user's profile Send private message
chavinash2004

New User


Joined: 30 Jun 2010
Posts: 37
Location: hyderabad

PostPosted: Sun Jul 18, 2021 5:48 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Mon Jul 19, 2021 8:50 pm
Reply with quote

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
View user's profile Send private message
chavinash2004

New User


Joined: 30 Jun 2010
Posts: 37
Location: hyderabad

PostPosted: Tue Jul 20, 2021 1:21 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Tue Jul 20, 2021 3:52 pm
Reply with quote

Welcome!
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 Need help with ADABAS query (COBOL-AD... All Other Mainframe Topics 0
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
Search our Forums:

Back to Top