Concatenate o/p of two select statements

Database
Not applicable

Re: Concatenate o/p of two select statements

yes,thanks guys

Enthusiast

Re: Concatenate o/p of two select statements

Hi teradatauser2,

 

 

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

 

In the above query t is the derived table, so table1, t will do Cartesian Product Join, that is all rows of one table are joined to all rows of another table.

 

MaxCol is the derived column in the derived table joined with all the rows of table1, so that i called MaxCol column in the upper SELECT.

 

 

Correct your query like below,

SEL Tran_Amt, Principal_Amt, Interest_Amt , maxcol FROM tduser.checking_tran,

(SEL max(tran_id) maxcol FROM financial.savings_tran)dt

Enthusiast

Re: Concatenate o/p of two select statements

RECURSIVE function logic

create multiset volatile table tb1

(id integer

,nm varchar(5)

)  

primary index (id,nm) 

on commit preserve rows;

insert into tb1 values (10,'xy');

insert into tb1 values (10,'yz');

insert into tb1 values (10,'zx');

insert into tb1 values (20,'ab');

insert into tb1 values (20,'bc');

create multiset volatile table tb3

as

(

sel id,nm,rank () over (partition by id order by nm) rn1

from

tb1

) with data primary index (id,nm) on commit preserve rows;

with recursive rslt (id,nm,rn1,lvl)

as(

sel id,cast ( nm  as varchar(20)),rn1,0 as lvl

from

tb3

where

rn1 = 1

union all

sel

rslt.id,

rslt.nm || ',' ||b.nm,

b.rn1,

rslt.lvl +1 as lvl

from

tb3  b

inner join

rslt

on

rslt.id = b.id

where

rslt.rn1 < b.rn1

)

 sel id,nm

 from rslt

 qualify rank() over (partition by id  order by lvl desc ) = 1;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Above logic used for only single grouping colum.

If we use multiple grouping statements for same, it will NOT be concatenated , Please see which we’ve written logic for multiple grouping

-----------------------Logic which we've written---------------------------------

CREATE MULTISET  TABLE OLAP_DEV.TB1

(

 DEALER_WID   DECIMAL(10,0)

, DIVN_WID  DECIMAL(10,0)

, EVTLOC_WID  DECIMAL(10,0)

, MONTH_WID   DECIMAL(10,0)

, MM_WID  DECIMAL(10,0)

,EVENT_NAME   VARCHAR(2000)

)

INSERT INTO TB1

SELECT * FROM WC_EVENT_MM_A_TEST;

SELECT * FROM TB3

CREATE MULTISET TABLE TB3

AS

(

SEL DEALER_WID,DIVN_WID, EVTLOC_WID,MONTH_WID,MM_WID, EVENT_NAME,   RANK () OVER (PARTITION BY DEALER_WID,DIVN_WID, EVTLOC_WID,MONTH_WID,MM_WID ORDER BY EVENT_NAME) RN1

FROM

TB1

)

WITH DATA ;

WITH RECURSIVE RSLT (DEALER_WID,DIVN_WID, EVTLOC_WID,MONTH_WID,MM_WID, EVENT_NAME,RN1,LVL)

AS(

SEL DEALER_WID,DIVN_WID, EVTLOC_WID,MONTH_WID,MM_WID,CAST ( EVENT_NAME  AS VARCHAR(1000)),RN1,0 AS LVL

FROM

TB3

WHERE

RN1 = 1

UNION ALL

SEL

--RSLT.ID,

RSLT.DEALER_WID,

RSLT.DIVN_WID,

RSLT.EVTLOC_WID,

RSLT.MONTH_WID,

RSLT.MM_WID,

TRIM(RSLT.EVENT_NAME) || ',' ||TRIM(B.EVENT_NAME),

B.RN1,

RSLT.LVL +1 AS LVL

FROM

TB3  B

INNER JOIN

RSLT

ON

--RSLT.ID = B.ID

RSLT.DEALER_WID=B.DEALER_WID

AND RSLT.DIVN_WID=B.DIVN_WID

AND RSLT.EVTLOC_WID=B.EVTLOC_WID

AND RSLT.MONTH_WID=B.MONTH_WID

AND RSLT.MM_WID=B.MM_WID

WHERE

RSLT.RN1 < B.RN1

)

 SEL DEALER_WID,DIVN_WID, EVTLOC_WID,MONTH_WID,MM_WID, EVENT_NAME

 FROM RSLT

 QUALIFY RANK() OVER (PARTITION BY DEALER_WID,DIVN_WID, EVTLOC_WID,MONTH_WID,MM_WID  ORDER BY LVL DESC ) = 1;

this logic is not working when we are using multiple grouping values.

Please help me out from this situation!

Thanks in advance

Eagerly awaiting  

Fan

Re: Concatenate o/p of two select statements

Hi Mahesh,

I would suggest using ROW_NUMBER() instead of RANK() and change the recursive UNION ALL WHERE clause to:

...

WHERE

  RSLT.RN1 + 1 = B.RN1

Best Regards,

Igor