Concatenate o/p of two select statements

Database

Concatenate o/p of two select statements

Hi,

I have a situation in which i need to concatenate o/p of two sel queries something like below:

Sel col1,col2,col3 from table1 || sel max(eff_date) from table2

This is just for illustration, the first select is actually a big select query and i want to append max of eff_date to each row of the o/p

But, the syntax above doesn't work. How can we achieve this ?

Thx

13 REPLIES
N/A

Re: Concatenate o/p of two select statements

Sel
col1,col2,col3,
(sel max(eff_date) from table2)
from table1

Dieter

Re: Concatenate o/p of two select statements

The above solution would work perfectly with the scenario you mentioned. However, if you have more than one column to concatenate in your results and there is absolutely NO relation between 2 tables then you can try something like below too.

SEL col1,col2,col3, MAX(eff_date), COUNT(table2.xyz), ...
FROM table1, table2
WHERE 1=1
GROUP BY 1,2,3

Re: Concatenate o/p of two select statements

Actually no need for WHERE 1=1, it would work without that too.

SEL col1,col2,col3, MAX(eff_date), COUNT(table2.xyz), ... 
FROM table1, table2
GROUP BY 1,2,3

Re: Concatenate o/p of two select statements

Query below is the another way to achieve the result....

SEL Col1,Col2,Col3,MaxCol FROM table1,
(SEL MAX(eff_date) MaxCol FROM table2)t

Re: Concatenate o/p of two select statements

SEL col1,col2,col3, MAX(eff_date), COUNT(table2.xyz), ... 

FROM table1, table2 

GROUP BY 1,2,3


sel col1,col2.. 

  DW_ACCT_ID_MAX.MAX_ID

FROM (SELECT MAX(ACCT_ID) MAX_ID FROM table2) DW_ACCT_ID_MAX,

 table1 DT

JOIN table3 ACCT

  ON  ACCT.BUSN_TRUST_ACCT_EFF_DT = DT.DW_EFF_DT

  AND ACCT.DW_EFF_DT <= DT.DW_EFF_DT AND DT.DW_EFF_DT <= ACCT.DW_EXPR_DT

  AND ACCT.CURR_IN=1

    AND  ACCT.RCV_IN=1

........

and many joins are there below..

In my current query, this is being done like above. But is this not an efficient way to do as DW_ACCT_ID_MAX and DT are inner joined with no join condition and would result into a product join ?

KS42982 - Its is the same solution as yours. So, which is a better solution , diether's one or yours ?

Re: Concatenate o/p of two select statements

Hi Teradatauser2,

All the queries which is posted here will get product join except the query which is posted from Mathuram especially when you are performing with more than one table process.

if we are going to show max/or any one common value along with all the data is based on single table then we can go with the below query

sel empno, max(empno) over() from employee

So when it comes to more than one table process and to show one common value in all the rows is based on one table then we can go with Mathuram query which will avoid product join.

if you would like to merge all the tables which are processed then we can go with the below

sel col1, table2.*, max(table2.c1)over() from table1

Regards,

Mohan K

Re: Concatenate o/p of two select statements

To add, the query posted by Dieter is a scalar sub query which will work with TD 13 onwards..

Hence I am not sure about whether this will get product join or not..

Re: Concatenate o/p of two select statements

I don't think any of the above queries (except Dieter's) would work without having product join.

To ask your question on what is the better solution, then in my opinion, as in your scenario - where you need only 1 column from table 2, you should go with Dieter's solution as that will not cause product join. When you need more than one columns, you should go with other options mentioned in this thread.

Re: Concatenate o/p of two select statements

1

2

SEL Col1,Col2,Col3,MaxCol FROM table1,

(SEL MAX(eff_date) MaxCol FROM table2)t


Hi Mathuram,

Could you please explain you query. I couldn't uderstand it. Where we get maxcol value in the upper select ? (from the below query)

where to specify the table from which we get col1,col2 col3

i tesed this, but giving me error, i am surely missing to understanding sometheing here

sel Tran_Amt,Principal_Amt, Interest_Amt ,maxcol from table1,

(sel max(tran_id) maxcol from financial.savings_tran ) table1

from   tduser.checking_tran