Combine Multiple Select Statements

Database
Enthusiast

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

10 REPLIES
Enthusiast

Re: Combine Multiple Select Statements

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.

Enthusiast

Re: Combine Multiple Select Statements

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

Enthusiast

Re: Combine Multiple Select Statements

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

Enthusiast

Re: Combine Multiple Select Statements

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


Junior Contributor

Re: Combine Multiple Select Statements

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

Enthusiast

Re: Combine Multiple Select Statements

Thanks for your response.

What is the Syntax for derived table query? How do I Union derived table query? 

Thank you for your help!

Junior Contributor

Re: Combine Multiple Select Statements

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

Enthusiast

Re: Combine Multiple Select Statements

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

Enthusiast

Re: Combine Multiple Select Statements

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