REPORT FROM MULTIPLES QUERY BEST APPROACH

Analytics
Enthusiast

REPORT FROM MULTIPLES QUERY BEST APPROACH

I do not know the Best Approach and I am a total Newbie with databases and SQL. Learing from Several Platfrom.

I have to create a Report by pulling data from database Views and exporting it into Excel Sheet.

The Columns of the Excel sheet has to be in correct sequence. The first 7 columns of the excel sheet will always be filled with fixed data.

The values of remaining 7 columns of the excel sheet are based on individual Select Query.

Each Select Query return weekly data of something. So Data among each 7 queries is different based on the conditions used in each query.

Each Select query has a common field "Date" and Count of Something. So Resultset for each query is always 2 Columns.

Example:

Date((CURRENT_DATE -7) and CURRENT_DATE), Count(Something)

The Goal is to Auomate this Process instead of Copying and Pasting Each ResultSet(Column) for Week Range.

I will be using Insert Into or Insert execute 1 query at a time by looping through a Cursor. The Problem is I have no Idea how to write cursor or procedure. I do not have access to Database. I can only access to Views.

Kindly help me What should be best approach to Implment this solution. The only tool I have available is SQL Assistant for Teradata, MS DOS SCHEDULAR, MS EXCEL. Writing of NULLs are allowed in the last 7 Columns after Date Columm.



/*-----First Create a Permenant Table in a Datbase----*/


CREATE SET TABLE Schema.MASTER_TBL,NO FALLBACK
No BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM=DEFAULT,
DEFAULT MERGEBLOCKRATIO

(Col1 CHAR(3) CHRACTER SET LATIN NOT CASESPECIFIC NOT NULL default 'NA' as "Reigon",

Col2 CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL default 'Brazil' as "Country",

Col3 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL default 'PPP' as "Brand",

Col4 CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL default 'Opt All (2,TT)' as "KPI",

Col5 CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL default '(2, TT)' as "Source System",

Col6 CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL default 'Weekly' as "Frequency Target",

Col7 CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL default 'TTT-CA' as "Brand/Market",

Col8 DATE(format 'DD/MM/YYYY' not null default date '01/01/2000') as "Date",

Col9 Integer as "Opp-Wt-Total",

Col10 Integer as "Opp-wt-1213",

Col11 Integer as "Opp-wt-1318",

Col12 Integer as "Opo-ott-Total",

Col13 Integer as "Requested S",

Col14 Integer as "Requested C",

Col15 Integer as "C Redeme",

UNIQUE PRIMARY INDEX (Col8);

Does above Syntax for Creating Permenant Table Structure looks correct?

6 REPLIES
Enthusiast

Re: REPORT FROM MULTIPLES QUERY BEST APPROACH

Step2:

Insert all Fixed Values in a Table for 7 days along with Date Field. This Query Execute on 08/20/2013.

I want to Learn how to use CURSOR to loop through each each query independently.

I think this will allow me to Write the Resultset of each query Filling the remaining 7 Columns of the Excel Sheet.

Query1:

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

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)

...............Query2, Query3, Query4, Query5, Query6, Query7

EXPECTED OUTPUT:

NA Brazil PPP Opt - All (2, TT) (2,TT) weekly TTT-CA 8/21/2013 4,810 699   533 2,306 1,420  
Enthusiast

Re: REPORT FROM MULTIPLES QUERY BEST APPROACH

Expected OutPut Without Query Columns:

Region Country Brand KPI Source System F-Target Brand/Market Date
NA Brazil PPP Opt - All (2, TT) (2,TT) weekly TTT-CA 8/14/2013
NA Brazil PPP Opt - All (2, TT) (2,TT) weekly TTT-CA 8/15/2013
NA Brazil PPP Opt - All (2, TT) (2,TT) weekly TTT-CA 8/16/2013
NA Brazil PPP Opt - All (2, TT) (2,TT) weekly TTT-CA 8/17/2013
NA Brazil PPP Opt - All (2, TT) (2,TT) weekly TTT-CA 8/18/2013
NA Brazil PPP Opt - All (2, TT) (2,TT) weekly TTT-CA 8/19/2013
NA Brazil PPP Opt - All (2, TT) (2,TT) weekly TTT-CA 8/20/2013
Enthusiast

Re: REPORT FROM MULTIPLES QUERY BEST APPROACH

CREATE SET TABLE Schema.MASTER_TBL,NO FALLBACK
No BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM=DEFAULT,
DEFAULT MERGEBLOCKRATIO

(Col1 CHAR(3) CHRACTER SET LATIN NOT CASESPECIFIC NOT NULL default 'NA' as "Reigon",

Col2 CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL default 'Brazil' as "Country",

Col3 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL default 'PPP' as "Brand",

Col4 CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL default 'Opt All (2,TT)' as "KPI",

Col5 CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL default '(2, TT)' as "Source System",

Col6 CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL default 'Weekly' as "F Target",

Col7 CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL default 'TTT-CA' as "Brand/Market",

Col8 DATE(format 'DD/MM/YYYY' not null default date '01/01/2000') as "Date",

Col9 Integer as "Opp-Wt-Total",

Col10 Integer as "Opp-wt-1213",

Col11 Integer as "Opp-wt-1318",

Col12 Integer as "Opo-ott-Total",

Col13 Integer as "Requested S",

Col14 Integer as "Requested C",

Col15 Integer as "C Redeme",

PRIMARY INDEX (Col8);
Enthusiast

Re: REPORT FROM MULTIPLES QUERY BEST APPROACH

Kindly Guide me ..I will be highly obliged!

Regards,

J

Enthusiast

Re: REPORT FROM MULTIPLES QUERY BEST APPROACH

create table failed 3706 Syntax error data Type "chracter" does not match a define type name??

Why am I getting this error?

Enthusiast

Re: REPORT FROM MULTIPLES QUERY BEST APPROACH

Re your create table issue...

There is a typo... Change CHRACTER to CHARACTER in the definition of Col1...