Select query should return twice

Billa007 · #1 (**permalink**) 10-19-11, 08:41

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

Marcus_A · #2 (**permalink**) 10-19-11, 09:05

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.

tonkuma · #3 (**permalink**) 10-19-11, 09:39

(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.
How to match the year in Date field

(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)

Quote:

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)

Quote:

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.

tonkuma · #4 (**permalink**) 10-19-11, 11:52

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.

Billa007 · #5 (**permalink**) 10-20-11, 07:33

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;

tonkuma · #6 (**permalink**) 10-20-11, 09:46

I couldn't understand why you need two rows.

Isn't your query enough?

Quote:

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.

Billa007 · #7 (**permalink**) 10-21-11, 04:26

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;

tonkuma · #8 (**permalink**) 10-22-11, 02:01

Quote:

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.

Billa007 · #9 (**permalink**) 10-22-11, 03:06

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;

tonkuma · #10 (**permalink**) 10-22-11, 03:25

Quote:

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

Billa007 · #11 (**permalink**) 10-28-11, 09:46

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

tonkuma · #12 (**permalink**) 10-28-11, 12:01

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.

Quote:

...

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.

Quote:

...

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.

tonkuma · #13 (**permalink**) 10-28-11, 13:04

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 Must Read before posting

Quote:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech