Results 1 to 7 of 7

Thread: Difficult query

  1. #1
    Join Date
    Jul 2013
    Posts
    8

    Unanswered: Difficult query

    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:

    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

    Please advice

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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
    Dave

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

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by deebeetwee View Post
    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?
    ---
    "It does not work" is not a valid problem statement.

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

    briefly looking your description,
    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. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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.
    Dave

  7. #7
    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 18: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
  •