All Forums General
Abhishektd 7 posts Joined 06/11
26 Aug 2015
SQL (combine multiple insert-sel statements into one )

Hi ,
I want to merge following multiple insert - select statements into a single insert-select statement without using UNION/UNION ALL. Please suggest.
 
INSERT INTO Fact
SELECT DATE AS LOAD_DT
, key_id
, 200 AS S_id
, device_id
, seg_id
, insight_id
, 5040 AS m_id
, SUM(no_pmt) AS VAL
FROM Payment
GROUP BY 1,2,3,4,5,6,7;
 
INSERT INTO Fact
SELECT DATE AS LOAD_DT
, key_id
, 200 AS S_id
, device_id
, seg_id
, insight_id
, 5050 AS m_id
, SUM(AMOUNT) AS VAL
FROM Payment
GROUP BY 1,2,3,4,5,6,7;
 
INSERT INTO Fact
SELECT DATE AS LOAD_DT
, key_id
, 200 AS S_id
, device_id
, seg_id
, insight_id
, 5060 AS m_id
, COUNT(DISTINCT c_id) AS VAL
FROM Payment
GROUP BY 1,2,3,4,5,6,7;
 
 

Thanks & regards,

Abhi

Tags:
kirthi 65 posts Joined 02/12
26 Aug 2015
INSERT INTO FACT
SEL 
 LOAD_DT
,key_id
,S_id
,device_id
,seg_id
,insight_id 
,CASE WHEN T2.ROW_NUM =1 THEN 5040 
   WHEN T2.ROW_NUM=2 THEN 5050 
   ELSE 5060  END AS M_ID
,CASE WHEN T2.ROW_NUM =1 THEN T1.VAL1 
   WHEN T2.ROW_NUM=2 THEN T1.VAL2
   ELSE T1.VAL3  END AS VAL
(
SELECT DATE AS LOAD_DT
 , key_id
 , 200 AS S_id
 , device_id
 , seg_id
 , insight_id 
 , SUM(no_pmt) AS VAL1
 , SUM(AMOUNT) AS VAL2
 , COUNT(DISTINCT c_id) AS VAL3
 FROM Payment
 GROUP BY 1,2,3,4,5,6
 ) T1
  CROSS JOIN 
( SEL 
ROW_NUMBER() OVER (PARTITION BY 0 ORDER BY 'A' ) AS ROW_NUM
FROM SYS_CALENDAR.Calendar
Qualify ROW_NUM <=3
) T2

Please check if this suites your need.

Adeel Chaudhry 773 posts Joined 04/08
30 Aug 2015

Any specific reason for not using UNION/UNION ALL?

-- If you are stuck at something .... consider it an opportunity to think anew.

Abhishektd 7 posts Joined 06/11
31 Aug 2015

Hi Adeel,
I just want to compare the performace of both queries and then decide, which one to implement finally.

Thanks & regards,
Abhi

Abhishektd 7 posts Joined 06/11
31 Aug 2015

Hi Kirti,
 
Thanks for help. It seems I have to go with UNION ALL as the suggested query is not returning the appropriate data. I tried another way as below but that too didn't produce desired result :( .
 
create volatile table MID
( MID integer ) on commit preserve rows ;
 
insert into MID values ( 5040) ;
insert into MID values ( 5050) ;
insert into MID values ( 5060) ;
 
collect stats on MID column (MID) ;
 
INSERT INTO Fact
SELECT DATE AS LOAD_DT
, key_id
, 200 AS S_id
, device_id
, seg_id
, insight_id
, mid.mid as  m_id
, case when  mid.mid  = 5040 then SUM(no_pmt)
            when  mid.mid  = 5050 then SUM(AMOUNT)
         when  mid.mid  = 5060 then  COUNT(DISTINCT c_id) END AS VAL
FROM Payment  cross join MID
GROUP BY 1,2,3,4,5,6,7;

 

Thanks & regards,
Abhi

You must sign in to leave a comment.