1. Registered User
Join Date
Jul 2013
Posts
12

We use DB2 V11

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:

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’.

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

2. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328
show data for each of the tables in question with the layout of the table, what SQL you have thus far.also, use code tags to keep data formatted remove the spaces between the brackets [ CODE ] then you put you data between [ / CODE ]

Here's how it would look:
Code:
` then you put you data between`

3. Registered User
Join Date
Jul 2013
Posts
12
Pffff, i am not so familiar with HTML.
Is this a DB2 forum or a HTML forum?

4. :-)
Join Date
Jun 2003
Location
Posts
5,516
Originally Posted by deebeetwee
Pffff, i am not so familiar with HTML.
Is this a DB2 forum or a HTML forum?
So, you're another one of those with the inflated sense of entitlement, thinking that other people should just shut up and do your job for you?

5. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
deebeetwee,

I guessed that the query might look like this...
Code:
```SELECT COALESCE(T300.part_zakl_cd , T416.part_zakl_cd) AS cd
, SUM(Number) AS sum_number
FROM  T552
INNER JOIN
T400
ON   T400.BBO_key = T552.BBO_key
LEFT  OUTER JOIN
T300
ON   T400.rek_nr <> -2
AND  ...
...
LEFT  OUTER JOIN
T414
ON   T400.rek_nr =  -2
AND  ...
...
LEFT  OUTER JOIN
T416
ON   ...
...
GROUP BY
COALESCE(T300.part_zakl_cd , T416.part_zakl_cd)
;```
To go further,
would you show me a complete set of sample data(from which the final result should be ‘P’ 929 and ‘Z’ 67), by something like this way...
Code:
```SELECT * FROM T552;
------------------------------------------------------------------------------

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

4 record(s) selected.

SELECT * FROM T400;
------------------------------------------------------------------------------

BBO_KEY     REK_NR      BBP_KEY
----------- ----------- -------
23          -2 xxxxx
15           0 yyyyy
44           1 zzzzz

3 record(s) selected.

SELECT * FROM T300;
...
...

SELECT * FROM T414;
...
...

SELECT * FROM T416;
...
...```
Note: I showed values of BBP_KEY as xxxxx(, yyyyy, zzzzz), because no data type information was not known.

6. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328
Tonkuma,
you show a lot more patience and consideration than I ever could. After the ops reply yesterday I would have just ignored the thread.

7. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
deebeetwee,

would you mind to answer my post?

I want to know wheather my suggested query was worked or not?

It is not necessary to express some of your thanks/gratitude for me.
I only want to know the results of the query.

If my proposed idea was not enough concrete, please say that!
I may want to consider more specific queries.
Last edited by tonkuma; 08-19-14 at 17:24.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•