Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: Select query should return twice

    Hi;

    Please consider the two input tables and joined use of JOIN

    SELECT
    t1.CD_PLT
    , t1.BASE_NAME
    , t1.WKLY_CA
    , t2.MONTH_CA
    , T2.C_year
    , t2.IN_DT
    FROM
    TABLE1 T1
    INNER JOIN
    table2
    ON t2.CD_PLT = T1.CD_PLT
    AND T2.BASE_NAME = T1.BASE_NAME

    Code:
    Getting Output
    
    CD_PLT       BASE_NAME      WKLY_CA     MONTH_CA       C_YEAR      IN_DT             
    ----------- ------------ -------------- ----------  ----------   ---------   
    AAAAA            6065         221         1000        2013       2013-01-13 
    
    BBBBB            7066         555         3500        2013       2013-01-12
    
    CCCCC            8065         777         4670        2014       2014-01-13
    Code:
    EXPECTED OUTPUT
    
    CD_PLT       BASE_NAME      WKLY_CA     MONTH_CA       C_YEAR      IN_DT             
    ----------- ------------ -------------- ----------  ----------   ---------   
    AAAAA            6065         221         1000        2013       2013-01-13 
    AAAAA            6065         221         1000        2013 	 2013-01-13 
    
    BBBBB            7066         555         3500        2013       2013-01-12
    BBBBB            7066         555         3500        2013       2013-01-12
    
    CCCCC            8065         777         4670        2014       2014-01-13 
    CCCCC            8065         777         4670        2014       2014-01-13
    In the Table2 Month_CA column will update by user while the updation,after the updation the SELECT query should returns row by twice for the same record.
    How to get it ?
    Thanks

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In the query you are selecting:
    , t1.WKLY_CA
    , t2.MONTH_CA
    , T2.C_year
    , t2.IN_DT

    I would select all T1 columns in one query, and then UNION ALL to another query that selects T2 colums. But I don't know how to get blank lines after each pair.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1) You provided too little information and inconsistent information.

    (1-1) It reminds me you had did same things(too little information) in this thread.
    http://www.dbforums.com/db2/1670347-...ate-field.html

    (1-2) See (3) for "inconsistent information".

    (1-3) Please use your imagination wheather(or not) other people who saw your post could answer your queastion by using only the information you supplied.
    Other people don't know the background of your issue.
    And please review your message before posting it.


    (2)
    In the Table2 Month_CA column will update by user while the updation,after the updation ...
    Without supplying the update statement, no one might understand what you want to say.


    (3)
    SELECT
    t1.CD_PLT
    , t1.BASE_NAME
    , t1.WKLY_CA
    , t2.MONTH_CA
    , T2.C_year
    , t2.IN_DT
    FROM
    TABLE1 T1
    INNER JOIN
    table2
    ON t2.CD_PLT = T1.CD_PLT
    AND T2.BASE_NAME = T1.BASE_NAME
    I couldn't understand why this statement didn't result syntax error.
    Because no T2 appeared as table-reference nor as correlation-name in the statement.

    Note: I could suppose that T2 might be a correlation-name of table2.
    But, why did you removed correlation-name T2 from your statement to give extra burdon for other poeple?


    (4) Please supply the data of table1 and table2 which were produced your "Getting Output".
    And the data of table1 and table2 data should include boundary/exceptional data.
    Last edited by tonkuma; 10-19-11 at 10:45. Reason: Add Note for (3)

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    An example that I thought that you were provided too little information.

    An easy way to get your "EXPECTED OUTPUT" from your "Getting Output" might be...
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH Getting_Output
     (   CD_PLT , BASE_NAME , WKLY_CA , MONTH_CA , C_YEAR , IN_DT       ) AS (
    VALUES
      ( 'AAAAA' , 6065      , 221     , 1000     , 2013  , '2013-01-13' )
    , ( 'BBBBB' , 7066      , 555     , 3500     , 2013  , '2013-01-12' )
    , ( 'CCCCC' , 8065      , 777     , 4670     , 2014  , '2014-01-13' )
    )
    SELECT go.*
     FROM  (VALUES 0 , 175) AS p(z)
         , Getting_Output go
     ORDER BY
           cd_plt
    ;
    ------------------------------------------------------------------------------
    
    CD_PLT BASE_NAME   WKLY_CA     MONTH_CA    C_YEAR      IN_DT     
    ------ ----------- ----------- ----------- ----------- ----------
    AAAAA         6065         221        1000        2013 2013-01-13
    AAAAA         6065         221        1000        2013 2013-01-13
    BBBBB         7066         555        3500        2013 2013-01-12
    BBBBB         7066         555        3500        2013 2013-01-12
    CCCCC         8065         777        4670        2014 2014-01-13
    CCCCC         8065         777        4670        2014 2014-01-13
    
      6 record(s) selected.

  5. #5
    Join Date
    Sep 2011
    Posts
    220
    Hi;

    Apology for the provided too little information..Changes to be needed for the same query which is in the previous post" How to match.."

    In that query
    Code:
    SELECT
           t1.CD_PLT
         , t1.BASE_NAME 
         , t1.PRE_NAME
         , t1.SUF_NAME
         , T1.WKLY_CA -->this column should return the value  in the first row
         , t2.WKLY_CA -->this column should return the value  in the second row
         , p. per_year
         , t2.IN_DT
     FROM
    :
    :
    In the Table1 T1.WKLY_CA value is updating dynamically by the some other program


    We should return the result set as it is in the above query,except T2.WKLY_CA .


    The T2.WKLY_CA will be be updated by the user what is in T1.WKLY_CA

    UPDATE TABLE2
    SET WKLY_CA = :WS-WKLY_CA

    WHERE
    CD_PLT = :WS-CD-PLT
    AND BASE_NAME = :WS-BASE_NMAE
    AND PRE_NAME = :WS-PRE_NAME
    AND SUF_NAME = :WS-SUF_NAME
    So that We need for both rows for the same CD_PLT,BASE_NAME,PRE_NAME...in the result set at a time...like below

    But In the first row WKLY_CA should come from Table1 T1
    In the Second row WKLY_CA should come from Table2 T2

    Expected Output

    Code:
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  WKLY_CA 	PER_YEAR     IN_DT                     
    ----------- ------------ -------------- ---------- -------    ------------   --------- 
    ABACC            6065        REWS           AE     221          2013         2013-01-13 --->  WKLY_CA should come from Table1  T1   
    ABACC            6065        REWS           AE     221          2013         2013-01-13 --->  WKLY_CA sholud come from Table2  T2
    Obiviously,After updation T1.WKLY_CA will be having different value while fetching(because T1.WKLY_CA value will be updating dynamically)

    So every time we need two rows for the same record..Only the difference is WKLY_CA coming from Table1 in the first row and WKLY_CA coming from Table2 int the second row

    Please let me know,if any more

    Thanks;

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I couldn't understand why you need two rows.

    Isn't your query enough?
    Code:
    SELECT
           t1.CD_PLT
         , t1.BASE_NAME 
         , t1.PRE_NAME
         , t1.SUF_NAME
         , T1.WKLY_CA -->this column should return the value  in the first row
         , t2.WKLY_CA -->this column should return the value  in the second row
         , p. per_year
         , t2.IN_DT
     FROM
    ...
    
    ;
    Before update, T1.WKLY_CA was same as T2.WKLY_CA.
    After update, T1.WKLY_CA may be different from T2.WKLY_CA.

    I felt no problem in that result.

  7. #7
    Join Date
    Sep 2011
    Posts
    220
    Hi;

    Below is the complete query
    Code:
    SELECT
           T1.CD_PLT
         , T1.BASE_NAME 
         , T1.PRE_NAME
         , T1.SUF_NAME
         , T1.WKLY_CA
         , T2.WKLY_CA
         , p. per_year
         , T2.IN_DT
     FROM
           (SELECT YEAR(current_date)     FROM sysibm.sysdummy1 UNION ALL
            SELECT YEAR(current_date) + 1 FROM sysibm.sysdummy1 UNION ALL
            SELECT YEAR(current_date) + 2 FROM sysibm.sysdummy1
           ) p(per_year)
     INNER JOIN
           (SELECT DISTINCT
                   *
             FROM  table1
           ) t1
       ON  0=0
     LEFT  OUTER JOIN
           (SELECT T2.*,MAX( YEAR(IN_DT) , YEAR(current_date) ) AS norm_year	
     
     FROM   TABLEE2 T2
      )T2
       ON  t2.CD_PLT    = T1.CD_PLT
       AND T2.PRE_NAME  = T1.PRE_NAME
       AND T2.BASE_NAME = T1.BASE_NAME
       AND T2.SUF_NAME  = T1.SUF_NAME
       AND t2.norm_year = p. per_year
     ORDER BY
           CD_PLT
         , BASE_NAME DESC
         , SUF_NAME
         , PRE_NAME
         , per_year
    ;
    In the above query.For ex..While fetching the T1.WKLY_CA value is 2450 and T2.WKLY_CA value is 2000 means

    We should return the both rows to the front end program like below
    Code:
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  WKLY_CA 	PER_YEAR     IN_DT                     
    ----------- ------------ -------------- ---------- -------    ------------   --------- 
    ABACC            6065        REWS           AE     2450          2011         2011-01-13 
    ABACC            6065        REWS           AE     2000          2011         2011-01-13
    .,from there that front end program will fetch the only one row
    which WKLY_CA value is greater and that the particular row will be displayed in the screen like below
    Code:
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  WKLY_CA 	PER_YEAR     IN_DT                     
    ----------- ------------ -------------- ---------- -------    ------------   --------- 
    ABACC            6065        REWS           AE     2450          2011         2011-01-13
    Please see the rows WKLY_CA is the only column for both tables(T1 and T2)
    (Select query we used two columns T1.WKLY_CA and T2.WKLY_CA)

    After updation,T2.WKLY_CA will be having value 2450 obiviously and while fetching again

    the T1.WKLY_CA value will be different in some times(T1.WKLY_CA will be updating periodically by the fraction of the seconds interval)

    For Ex..

    If T1.WKLY_CA is 6000 means ,so we should return both rows to the front end program like below
    Code:
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  WKLY_CA 	PER_YEAR     IN_DT                     
    ----------- ------------ -------------- ---------- -------    ------------   --------- 
    ABACC            6065        REWS           AE     6000          2011         2011-01-13 
    ABACC            6065        REWS           AE     2450          2011         2011-01-13
    and they will decide

    based on which row is having greater value in WKLY_CA and display in the screen..

    Please let me ,if any more regd..

    Thanks;

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    We should return the both rows to the front end program like below

    ...
    ...
    ,from there that front end program will fetch the only one row
    which WKLY_CA value is greater and that the particular row will be displayed in the screen like below

    ...
    Let DB2 to choose greater value, like
    MAX(T1.WKLY_CA , T2.WKLY_CA) AS WKLY_CA

    So, the front end program could be simplified to display fetched data(a row) in the screen only.

  9. #9
    Join Date
    Sep 2011
    Posts
    220
    Thanks for the reply...

    But the thing is...it could be a best way to send two records..
    Because the front end program will do some validation other than Greater value row..

    Please let me know,if any other possibilities to return two rows for that..

    Thanks;

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... the front end program will do some validation other than Greater value row..
    What validation?
    Let DB2 to validate them.
    If related some variables in the front end program and/or parameters received the front end program,
    put those variables in SQL as host variable.

  11. #11
    Join Date
    Sep 2011
    Posts
    220
    Hi;
    HTML Code:
    If related some variables in the front end program and/or parameters received the front end program,
    put those variables in SQL as host variable. 
    We are having the discussion for the related above suggestion.

    Below is another one...

    Need to perform delete operation before performing the SELECT query.

    Before performing the above posted complete SELECT query,We need to check any future IN_DT available for the
    same CD_PLT,BASE_NAME,PRE_NAME and SUF_NAME

    If available means need to delete the old IN_DT row from the Table while the Current date reaches the future IN_DT

    Consider the below two examples
    Code:
    Example1  
    
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  WKLY_CA 	PER_YEAR     IN_DT                     
    ----------- ------------ -------------- ---------- -------    ------------   --------- 
    ABACC            6065        REWS           AE     1000          2011         2011-01-13 
    ABACC            6065        REWS           AE     2450          2011         2011-12-11
    Code:
    Example2
    
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  WKLY_CA 	PER_YEAR     IN_DT                     
    ----------- ------------ -------------- ---------- -------    ------------   --------- 
    BBBBB            6063        REWS           BB     9999          2011         2010-11-11 
    BBBBB            6063        REWS           BB     2350          2011         2011-11-22 
    BBBBB            6063        REWS           BB     2350          2012         2012-02-02
    For the CD_PLT = ABACC ,WKLY_CA value is 1000 on the IN_DT '2011-01-13'(means the CD_PLT is able to collect 1000 WKLY_CA from 2011-01-13)
    For the same ABACC,WKLY_CA value is 2450 on the IN_DT '2011-12-11'(means the CD_PLT is able to collect 2450 WKLY_CA from 2011-12-11)

    So that the first record will be available in the Table2 up to 2011-12-10..When the user enter the screen and perform thee SELECT query on 2011-12-11
    means the first record needs to be deleted from the Table2



    Expected output (on 2011-12-11) Example1

    Code:
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  WKLY_CA 	PER_YEAR     IN_DT                     
    ----------- ------------ -------------- ---------- -------    ------------   --------- 
    ABACC            6065        REWS           AE     2450          2011         2011-12-11
    Expected output (on 2011-11-22) Example2
    Code:
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  WKLY_CA 	PER_YEAR     IN_DT                     
    ----------- ------------ -------------- ---------- -------    ------------   --------- 
    BBBBB            6063        REWS           BB     2350          2011         2011-11-22 
    BBBBB            6063        REWS           BB     2350          2012         2012-02-02
    Expected output (on 2012-02-02) Example2
    Code:
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  WKLY_CA 	PER_YEAR     IN_DT                     
    ----------- ------------ -------------- ---------- -------    ------------   --------- 
    BBBBB            6063        REWS           BB     2350          2012         2012-02-02
    Please help;
    Thanks in advance

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You have changed the problem.

    In your original post, you wrote clearly "row by twice for the same record" and Expected output also showed according to the phrase.
    ...
    Code:
    EXPECTED OUTPUT
    
    CD_PLT       BASE_NAME      WKLY_CA     MONTH_CA       C_YEAR      IN_DT             
    ----------- ------------ -------------- ----------  ----------   ---------   
    AAAAA            6065         221         1000        2013       2013-01-13 
    AAAAA            6065         221         1000        2013 	 2013-01-13 
    
    BBBBB            7066         555         3500        2013       2013-01-12
    BBBBB            7066         555         3500        2013       2013-01-12
    
    CCCCC            8065         777         4670        2014       2014-01-13 
    CCCCC            8065         777         4670        2014       2014-01-13
    In the Table2 Month_CA column will update by user while the updation,after the updation the SELECT query should returns row by twice for the same record.
    ...
    Even after you added more(and slightly changed your requirements) in your third post,
    IN_DT are same in two rows.
    Only difference was WKLY_CA.
    ...

    For Ex..

    If T1.WKLY_CA is 6000 means ,so we should return both rows to the front end program like below

    Code:
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  WKLY_CA 	PER_YEAR     IN_DT                     
    ----------- ------------ -------------- ---------- -------    ------------   --------- 
    ABACC            6065        REWS           AE     6000          2011         2011-01-13 
    ABACC            6065        REWS           AE     2450          2011         2011-01-13
    ...

    If you want to discuss about new problem,
    you shoud open another thread or my discussion and thought until now were in vain.

    I'll not respond anymore in this thread.
    Last edited by tonkuma; 10-28-11 at 13:35.

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although I wrote "I'll not respond anymore in this thread.",
    I want to add one more...

    The forum is not a face-to-face dialogue nor a realtime chat.

    Please read again http://www.dbforums.com/db2/854783-m...e-posting.html
    In a nutshell, provide as much information relevent to the problem as possible. At the same time, let the post be short , to the point and not long stories ..

Posting Permissions

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