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
 

 

difficult query (db2 v11)

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
revdpoel

New User


Joined: 01 Nov 2006
Posts: 56

PostPosted: Thu Aug 14, 2014 11:55 pm    Post subject: difficult query (db2 v11)
Reply with quote

Table T552 looks like this

Dag-key Automaat BBO-key SSP-key Number
1 AA 23 98 876
2 BB 23 66 21
3 BB 15 2 67
4 KL 44 2 32

BBO-key points to table T400, which has the attributes rek-nr and BBP-key.
This BBP-key points to table T300, which has an attribute part-zakl-cd. This attribute can have the value ‘P’ or ‘Z’.

SSP-key points to table T414, which also has the attributes rek-nr and BSP-key.
BSP-key points to table T416, which has an attribute part-zakl-cd. This attribute can have the value ‘P’ or ‘Z’.

When a BBO-key points to a value of rek-nr in T400 with a value of -2 it means it is not applicable. Let’s say this is BBO-key 23.
For that row we have to read T414 with the SSP-key

When a Savings-key points to a value of rek-nr in T414 with a value of -2 it means it is not applicable. Let’s say this Savings-key 2.
For that row we have to read T400 with the BBO-key

I have to count the numbers and divide them into part-zakl-cd.

Example 1:

Read row with dag-key 1.
BBO-key 23 points to reknr-2 in T400. So for that row we have to read T414 with SSP-key 98. Let’s say bsp-key in this T414 row points to a row in T416 where part-zakl-cd = ‘P’ then we have to count the number of 876 for ‘P’.

Read row with dag-key 3.
BBO-key 15 points to a reknr <> -2 in T400. So for that row we have to read T400 with BBO-key 15. Let’s say BBP-key in this T400 row points to a row in T300 where part-zakl-cd = ‘Z’ then we have to count the number of 67 for ‘Z’.

Suppose row 2 gives us a number of 21 for part-zakl-cd ‘P’ and row 2 gives us a number of 32 for part-zakl-cd ‘P’, then the result is

‘P’ 876+21+32 = 929
‘Z’ 67
Back to top
View user's profile Send private message

Rohit Umarjikar

Senior Member


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

PostPosted: Mon Aug 18, 2014 9:20 pm    Post subject:
Reply with quote

I tried to understand so check below query,
Code:

select a.number,count(*) as total_876_P
from T552 a,T414 b, T416 c, T400 d
where  a.BBO-Key      = d.??   
and    d.reknr        = -2
and    b.bsp-key      = c.??
and    c.part-zakl-cd = ‘P’   
group by a.number

UNION

select a.number,count(*) as total_876_Z
from T552 a, T400 b, T300 c
where  a.BBO-Key      = b.BBP-key??   
and    b.reknr        <> -2
and    b.bbp-key      = c.??
and    c.part-zakl-cd = ‘Z’   
group by a.number


Quote:
Suppose row 2 gives us a number of 21 for part-zakl-cd ‘P’ and row 2 gives us a number of 32 for part-zakl-cd ‘P’, then the result is

‘P’ 876+21+32 = 929
‘Z’ 67

Can you please explain this?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


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