All Forums Database
MBSconnection 29 posts Joined 07/13
15 Aug 2013
Combine Multiple Select Statements

How do I combine multiple Select queries?

I have 7 queries run separately. Each query return 2 columns. Count(of something), (date)

Date Range is Fixed for each query Set to 1 Week only.

I need to see my Answer Sheet to Show Date Column + Count Column of Each Query in a ONE ANSWER SHEET in TERADATA instead of Running all queries separately and have 7 Answer Sheets OPEN.

Appreciate everyone Help in Advance.

Regards,

MK

MBSconnection 29 posts Joined 07/13
15 Aug 2013
SELECT (COUNT (DISTINCT RP.REGISTER_ID), CAST (RPE.CEMAIL_DATETM AS DATE)

FROM
	SCHEMA_1.REGISTER_TBL RP
INNER JOIN		
	SCHEMA_2.MPROGRAM MP
	ON RP.MPRG_NBR= MP.MPRG_NBR

INNER JOIN
	SCHEMA_2.RPEMAIL_ADDR RPE

	ON RP.REGISTER_ID=RPE.REGISTER_ID
	AND RP.MPRG_NBR=RPE.MPRG_NBR

WHERE 	RP.MPRG_NBR IN (100)
	AND RPE.SUBSET_ID ='O'

AND RPE.CEMAIL_DATETM BETWEEN (CURRENT_DATE -7) and CURRENT_DATE
        
GROUP BY CAST ( RPE.CEMAIL_DATETM  AS DATE)
  ORDER BY CAST (RPE.CEMAIL_DATETM AS DATE)

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

SELECT (COUNT (DISTINCT RP.REGISTER_ID), CAST (RP.REGISTER_DATETM AS DATE)

FROM
	SCHEMA_1.REGISTER_TBL RP
INNER JOIN
	SCHEMA_2.MPROGRAM MP
	ON RP.MPRG_NBR= MP.MPRG_NBR

INNER JOIN
	SCHEMA_2.RPEMAIL_ADDR RPE

	ON RP.REGISTER_ID=RPE.REGISTER_ID
	AND RP.MPRG_NBR=RPE.MPRG_NBR

WHERE 
	WHERE RP.MPRG_NBR IN (100)
	AND RPE.SUBSET_ID ='I'
	AND RP.STATUS_ID = 'ACTION'
	
AND RP.REGISTER_DATETM BETWEEN (CURRENT_DATE -7) and CURRENT_DATE

GROUP BY CAST ( RP.REGISTER_DATETM  AS DATE)
   ORDER BY CAST (RP.REGISTER_DATETM AS DATE)

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

SELECT (COUNT (DISTINCT RP.REGISTER_ID), CAST (RP.REGISTER_DATETM AS DATE)  /* QUERY FOR (9999) */

FROM
	SCHEMA_1.REGISTER_TBL RP
INNER JOIN
	SCHEMA_2.MPROGRAM MP
	ON RP.MPRG_NBR= MP.MPRG_NBR


INNER JOIN
	SCHEMA_2.RPEMAIL_ADDR RPE

	ON RP.REGISTER_ID=RPE.REGISTER_ID
	AND RP.MPRG_NBR=RPE.MPRG_NBR

LEFT OUTER JOIN
	ECTRL.LCTRL LC
	ON CAST(RP.SSOURCE AS DECIMAL(16,0)) = LC.LID
	AND LC.LTYPE = 'ECTR'

LEFT OUTER JOIN
	ECTRL.LCTRL SC
	ON LC.SID = SC.SID

WHERE 
		RP.MPRG_NBR IN (100)
		AND LC.SID IN (9999) 
	AND RPE.SUBSET_ID ='I'
	AND RP.STATUS_ID = 'ACTION'
		AND RP.REGISTER_DATETM BETWEEN (CURRENT_DATE -7) and CURRENT_DATE

	GROUP BY CAST ( RP.REGISTER_DATETM  AS DATE)
   ORDER BY CAST (RP.REGISTER_DATETM AS DATE)

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

SELECT (COUNT (DISTINCT RP.REGISTER_ID), CAST (RP.REGISTER_DATETM AS DATE)  /* QUERY FOR (8888) */

FROM
	SCHEMA_1.REGISTER_TBL RP
INNER JOIN
	SCHEMA_2.MPROGRAM MP
	ON RP.MPRG_NBR= MP.MPRG_NBR


INNER JOIN
	SCHEMA_2.RPEMAIL_ADDR RPE

	ON RP.REGISTER_ID=RPE.REGISTER_ID
	AND RP.MPRG_NBR=RPE.MPRG_NBR

LEFT OUTER JOIN
	ECTRL.LCTRL LC
	ON CAST(RP.SSOURCE AS DECIMAL(16,0)) = LC.LID
	AND LC.LTYPE = 'ECTR'

LEFT OUTER JOIN
	ECTRL.LCTRL SC
	ON LC.SID = SC.SID

WHERE 
		RP.MPRG_NBR IN (100)
		AND LC.SID IN (8888)              
	AND RPE.SUBSET_ID ='I'
	AND RP.STATUS_ID = 'ACTION'
		AND RP.REGISTER_DATETM BETWEEN (CURRENT_DATE -7) and CURRENT_DATE

	GROUP BY CAST ( RP.REGISTER_DATETM  AS DATE)
   ORDER BY CAST (RP.REGISTER_DATETM AS DATE)

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

SELECT COUNT (DISTINCT RP.MCPR_ID), CAST (CA.CAS_DATETM AS DATE)

FROM
        SCHEMA_1.REGISTER_TBL RP

INNER JOIN

        SCHEMA_1.Customer_TBL CA
		ON RP.REGISTER_ID = CA.REGISTER_ID
WHERE
                CA.MPRG_NBR IN (100)
                AND CA.L_NBR IN (215)
                AND CA.T_CODE IN ('Rose_A')

AND CA.CAS_DATETM BETWEEN (CURRENT_DATE -7) and CURRENT_DATE

                
                GROUP BY CAST (CA.CAS_DATETM AS DATE)
   				ORDER BY CAST (CA.CAS_DATETM AS DATE)

----------------------------------------------------------------------------------------------------------
SELECT COUNT (DISTINCT RP.MCPR_ID), CAST (CA.CAS_DATETM AS DATE)

FROM
        SCHEMA_1.REGISTER_TBL RP

INNER JOIN

        SCHEMA_1.Customer_TBL CA
		ON RP.REGISTER_ID = CA.REGISTER_ID
WHERE
                CA.MPRG_NBR IN (100)
                AND CA.L_NBR IN (215)
                AND CA.T_CODE IN ('Rose_B')

AND CA.CAS_DATETM BETWEEN (CURRENT_DATE -7) and CURRENT_DATE

                
                GROUP BY CAST (CA.CAS_DATETM AS DATE)
   				ORDER BY CAST (CA.CAS_DATETM AS DATE)


--------------------------------------------------------------------------------------------------------------------
SELECT COUNT (DISTINCT RP.MCPR_ID), CAST (CA.CAS_DATETM AS DATE)

FROM
        SCHEMA_1.REGISTER_TBL RP

INNER JOIN

        SCHEMA_1.Customer_TBL CA
		ON RP.REGISTER_ID = CA.REGISTER_ID
WHERE
                CA.MPRG_NBR IN (100)
                AND CA.L_NBR IN (215)
                AND CA.T_CODE IN ('Rose_C')

AND CA.CAS_DATETM BETWEEN (CURRENT_DATE -7) and CURRENT_DATE

                
                GROUP BY CAST (CA.CAS_DATETM AS DATE)
   				ORDER BY CAST (CA.CAS_DATETM AS DATE)

---------------------------------------------------------------------------------------------------------
OUTPUT 

 

Every Query Return Week Range of Data with TWO Columns. Date Columns and Count Column.

"Data" for COUNT may or may not POPULATE for each ROW for individual Query but it will always Populate the "DATE"for each ROW. 

 

For Example August 1, 2013 to August 7, 2013.

COUNT(Something), DATE()

 

1 08/01/2013 10122 08/01/2013 21 08/01/2013 2244 08/01/2013 001 08/01/2013 002 08/01/2013

08/02/2013 23445 08/02/2013 12 08/02/2013 12 08/02/2013 005 08/02/2013 003 08/02/2013

6 08/03/2013 23244 08/03/2013 54 08/03/2013 08/03/2013 08/03/2013 004 08/03/2013

08/04/2013 08/04/2013 33 08/04/2013 054 08/04/2013 123 08/04/2013 005 08/04/2013

8 08/05/2013 45656 08/05/2013 01 08/05/2013 543 08/05/2013 183 08/05/2013 08/05/2013

10 08/06/2013 08/06/2013 09 08/06/2013 08/06/2013 186 08/06/2013 922 08/06/2013

55 08/07/2013 13545 08/07/2013 65 08/07/2013 184 08/07/2013 184 08/07/2013 988 08/07/2013

 

There are 14 Columns all togateher, If I were to RUN each individual Query seperately. After Combining all Queries I would like to have All DATE columns

should be Merge to 1 COLUMN and 7 DATA COLUMN. TOTAL of 8 COLUMN.

 

 

I would like to do DATA DUMP of my SINGLE COMBINE QUERY TO a TEMP TABLE that would continue to APPEND each Week. All above Queries are from VIEWS. No Table Access is available.

 

MBSconnection 29 posts Joined 07/13
15 Aug 2013

Looking forward for all the help I can get, Appreciate it in Advance.
 

KS42982 137 posts Joined 12/12
15 Aug 2013

You can do UNION ALL between all these queries and get 7 rows in one answer sheet in one shot.

MBSconnection 29 posts Joined 07/13
15 Aug 2013

Union Work but it doesn't give me what I want.
I think what I may be looking for is something I have found in Oracle but not sure how would I use it in Teradata SQL
http://www.oracle-developer.net/display.php?id=515
 

 

 

dnoeth 4628 posts Joined 11/04
16 Aug 2013

Just put each SELECT in a Dervived Table and Join them on the DATE.
Additionally you might reduce the number of queries by combining those with the same JOINs, but different WHERE into a single query, e.g. the last three:
 

SELECT 
   COUNT(DISTINCT CASE WHEN CA.T_CODE IN ('Rose_A') THEN RP.MCPR_ID END)
  ,COUNT(DISTINCT CASE WHEN CA.T_CODE IN ('Rose_B') THEN RP.MCPR_ID END)
  ,COUNT(DISTINCT CASE WHEN CA.T_CODE IN ('Rose_C') THEN RP.MCPR_ID END)

Dieter

Dieter

MBSconnection 29 posts Joined 07/13
19 Aug 2013

Thanks for your response.
What is the Syntax for derived table query? How do I Union derived table query? 
Thank you for your help!

dnoeth 4628 posts Joined 11/04
19 Aug 2013

A Derived Table (aka Inline View in Oracle) is a nested SELECT, i.e. a SELECT statement instead of table/view reference in the FROM clause, e.g.

SELECT t1.datetm, t1,cnt, t2.cnt
FROM
 (
   SELECT COUNT (DISTINCT RP.REGISTER_ID) AS cnt,
       CAST (RPE.CEMAIL_DATETM AS DATE) AS datetm
   FROM
       SCHEMA_1.REGISTER_TBL RP
   INNER JOIN     
       SCHEMA_2.MPROGRAM MP
       ON RP.MPRG_NBR= MP.MPRG_NBR
   INNER JOIN
       SCHEMA_2.RPEMAIL_ADDR RPE
       ON RP.REGISTER_ID=RPE.REGISTER_ID
       AND RP.MPRG_NBR=RPE.MPRG_NBR
   WHERE   RP.MPRG_NBR IN (100)
       AND RPE.SUBSET_ID ='O'
       AND RPE.CEMAIL_DATETM BETWEEN (CURRENT_DATE -7) AND CURRENT_DATE         
   GROUP BY CAST ( RPE.CEMAIL_DATETM  AS DATE)
 ) AS t1
JOIN
 (
   SELECT COUNT (DISTINCT RP.MCPR_ID) AS cnt,
       CAST (CA.CAS_DATETM AS DATE) AS datetm
   FROM
       SCHEMA_1.REGISTER_TBL RP
   INNER JOIN
       SCHEMA_1.Customer_TBL CA
       ON RP.REGISTER_ID = CA.REGISTER_ID
   WHERE  
           CA.MPRG_NBR IN (100)
       AND CA.L_NBR IN (215)
       AND CA.T_CODE IN ('Rose_B')
       AND CA.CAS_DATETM BETWEEN (CURRENT_DATE -7) AND CURRENT_DATE
   GROUP BY CAST (CA.CAS_DATETM AS DATE)
 ) AS t2
ON t1.datetm = t2.datetm

You have to follow some rules:

  • any calculated column must have an alias: e.g. col1*col2 AS col3
  • the Derived Table must have an alias, e.g. FROM (SELECT ...) AS t1 
  • no ORDER BY within the Derived Table

Dieter

Dieter

MBSconnection 29 posts Joined 07/13
22 Aug 2013

Thanks Dieter,
The code is not working, I made a little change.

SELECT t1.datetm, t1,cnt, t2.cnt
FROM
 ( 
 SELECT COUNT(DISTINCT RP.REGIS_PRSNA_ID) AS cnt, 
 CAST (RP.REGIS_DATETM AS DATE) AS datetm
FROM
        iCRM_LOAD.REGIS_PRSNA RP
INNER JOIN
        iCRM.MKTNG_PGM MP
        ON RP.MKTNG_PGM_NBR = MP.MKTNG_PGM_NBR
INNER JOIN
        iCRM.REGIS_PRSNA_EMAIL_ADDR RPE
        ON RP.REGIS_PRSNA_ID = RPE.REGIS_PRSNA_ID
        AND RP.MKTNG_PGM_NBR = RPE.MKTNG_PGM_NBR
WHERE
        RP.MKTNG_PGM_NBR IN (115)
        AND RPE.SUBSCRPTN_OPT_IND = 'I'
        AND RP.PRSNA_STATUS_CODE = 'AC' 
AND RP.REGIS_DATETM BETWEEN (CURRENT_DATE -7) and CURRENT_DATE  
GROUP BY CAST (RP.REGIS_DATETM  AS DATE)
  ) AS t1
   
JOIN
 (
     
SELECT COUNT (DISTINCT RP.REGIS_PRSNA_ID) AS cnt,
	 CAST (RP.REGIS_DATETM AS DATE) AS datetm
FROM
        iCRM_LOAD.REGIS_PRSNA RP
INNER JOIN
        iCRM.MKTNG_PGM MP
        ON RP.MKTNG_PGM_NBR = MP.MKTNG_PGM_NBR
INNER JOIN
        iCRM.REGIS_PRSNA_EMAIL_ADDR RPE
        ON RP.REGIS_PRSNA_ID = RPE.REGIS_PRSNA_ID
        AND RP.MKTNG_PGM_NBR = RPE.MKTNG_PGM_NBR

LEFT OUTER JOIN
                ETL_CTRL.LOAD_CONTROL LC
                ON CAST(RP.SYS_SOURCE AS DECIMAL(18,0)) = LC.LOAD_ID
                AND LC.LOAD_TYPE = 'ETL'
LEFT OUTER JOIN
                ETL_CTRL.SOURCE_CONTROL SC
                ON LC.SOURCE_ID = SC.SOURCE_ID
WHERE
                RP.MKTNG_PGM_NBR IN (115)
                AND LC.SOURCE_ID IN (1213)
        AND RPE.SUBSCRPTN_OPT_IND = 'I'
        AND RP.PRSNA_STATUS_CODE = 'AC'
		AND RP.REGIS_DATETM BETWEEN (CURRENT_DATE -7) and CURRENT_DATE
GROUP BY CAST (RP.REGIS_DATETM AS DATE)
) AS t2
 ON t1.datetm = t2.datetm
   

Kindly, Look at it.
Select Failed. 5628: Column t1 not found in t2 or t1
Regards,
MBC

Qaisar Kiani 337 posts Joined 11/05
24 Aug 2013

Replace the comma with dot in the first line.... t1,cnt should be t1.cnt 

Divine786 10 posts Joined 08/13
29 Aug 2013

UNION ALL between every query will solve this issue.

You must sign in to leave a comment.