Problem in passing run time value for the date filed in Teradata view

Database

Problem in passing run time value for the date filed in Teradata view

Hi , I need to create a custom view from multiple tables and wanted to make that custom view so efficient so that it can accept the values from the end users at the run time. For ex below query I need to put it in a custom view and when I ran that custom view it should ask the value for those dates so that I can give this at run time and get the results.

SEL c.week_of_year

                                , vnw.acct_id

                                , aclh.cust_loc_id

                                , b.hshld_mthky_cd

                                , CASE                WHEN b.cnxns_sgmnt_cd IS NULL THEN lc.CLRTS_CONNEXIONS_CD

                                                ELSE b.cnxns_sgmnt_cd

                                                END      AS cnxns_sgmnt_cd

                                , CASE                WHEN b.cnxns_lvl_cd IS NULL THEN lc.CLRTS_CONNEXIONS_LVL

                                                ELSE b.cnxns_lvl_cd

                                                END      AS cnxns_lvl_cd

                                , CASE                WHEN b.prizm_sgmnt_cd IS NULL THEN lc.prizm_segment_cd

                                                ELSE b.prizm_sgmnt_cd

                                                END      AS prizm_sgmnt_cd

                                , CASE                WHEN b.prizm_lvl_cd IS NULL THEN lc.prizm_flag

                                                ELSE b.prizm_lvl_cd

                                                END      AS prizm_lvl_cd                               

FROM edwafflviews.vacct_non_wirls vnw

JOIN sys_calendar.CALENDAR c

ON vnw.acct_orgnl_srv_dt=c.calendar_date

JOIN edwviews.acct_cust_loc_hist aclh

ON vnw.acct_id=aclh.acct_id

AND aclh.acct_cust_loc_eff_dt<='?Month_End_Date'

AND aclh.acct_cust_loc_end_dt>'?Month_End_Date'

Any inputs will be highly appreciable. If the above is not possible then please provide some alternatives.

2 REPLIES
Junior Contributor

Re: Problem in passing run time value for the date filed in Teradata view

There's no way to create a view like that (probably in any existing database).

But you can put it in a macro:

REPLACE MACRO mymacro (Month_End_Date DATE) AS (
SEL c.week_of_year
, vnw.acct_id
, aclh.cust_loc_id
, b.hshld_mthky_cd
, CASE WHEN b.cnxns_sgmnt_cd IS NULL THEN lc.CLRTS_CONNEXIONS_CD
ELSE b.cnxns_sgmnt_cd
END AS cnxns_sgmnt_cd
, CASE WHEN b.cnxns_lvl_cd IS NULL THEN lc.CLRTS_CONNEXIONS_LVL
ELSE b.cnxns_lvl_cd
END AS cnxns_lvl_cd
, CASE WHEN b.prizm_sgmnt_cd IS NULL THEN lc.prizm_segment_cd
ELSE b.prizm_sgmnt_cd
END AS prizm_sgmnt_cd
, CASE WHEN b.prizm_lvl_cd IS NULL THEN lc.prizm_flag
ELSE b.prizm_lvl_cd
END AS prizm_lvl_cd
FROM edwafflviews.vacct_non_wirls vnw
JOIN sys_calendar.CALENDAR c
ON vnw.acct_orgnl_srv_dt=c.calendar_date
JOIN edwviews.acct_cust_loc_hist aclh
ON vnw.acct_id=aclh.acct_id
AND aclh.acct_cust_loc_eff_dt<= :Month_End_Date
AND aclh.acct_cust_loc_end_dt> :Month_End_Date
;)

Then it's EXEC mymacro(date '2014-03-31');

Or you simply create the view without those conditions and the user adds them at runtime in a WHERE-conditiion (but without prompt).

Re: Problem in passing run time value for the date filed in Teradata view

I agree but the issue is ... The above query is just a sample one.. My original query is the big one and there I have multiple derived attributes in a select clause which is coming from two sub queries A and B where the format is like below:

select A.*, B.*, derived columns 

from A Left outer join B

on A.acctid = b.acctid

as u suggest I can create a view of the above query and let users put the date filter conditions in the where clause but that too will not give the expected results if user put  the date filter in the where clause which is coming from B table....