Year and Month prompt to create a date ( Teradata)

[Former Member]

Hi there,

I have created a sql in teradata which retrieves the last 13 months from the inputed date. Below is the SQL

select yr_nbr,mth_nbr

from TABLE.d_perd_mstr_vw

where expr_dt BETWEEN

ADD_MONTHS(cast( ('15/'||'03'||'/'||'2014' ( DATE, format 'DD/MM/YYYY' )) AS DATE),-13)

AND cast( ('15/'||'03'||'/'||'2014' ( DATE, format 'DD/MM/YYYY' )) AS DATE)

AND perd_type_cd='CM'

ORDER BY 1 ,2

I tried to create the same in the universe with Year and Month as prompt but getting errors. Below is the sql

TABLE.d_perd_mstr_vw.expr_dt BETWEEN

ADD_MONTHS(cast( ('15/'|| TABLE.d_perd_mstr_vw.mth_nbr = @Prompt('Enter values for Mth Nbr:','A','Period Master\Mth Nbr',Mono,constrained,Persistent)||'/'||TABLE.d_perd_mstr_vw.yr_nbr = @Prompt('Enter values for Yr Nbr:','A','Period Master\Yr Nbr',Mono,constrained,Persistent) ( DATE, format 'DD/MM/YYYY' )) AS DATE),-13)

AND cast( ('15/'|| TABLE.d_perd_mstr_vw.mth_nbr = @Prompt('Enter values for Mth Nbr:','A','Period Master\Mth Nbr',Mono,constrained,Persistent)||'/'||TABLE.d_perd_mstr_vw.yr_nbr = @Prompt('Enter values for Yr Nbr:','A','Period Master\Yr Nbr',Mono,constrained,Persistent) ( DATE, format 'DD/MM/YYYY' )) AS DATE)

Any inputs please.

Thanks

Abhi

[Former Member]

Hi Abhi,

Is this a single source or a multi source universe?

Also can you explain in little detail why you have so many @prompt variables in the expression..

Also please explain from a requirement stand point what you are trying to achieve..

[Former Member]

Hi,

I got it resolved. Took out TABLE.d_perd_mstr_vw.mth_nbr =   and TABLE.d_perd_mstr_vw.yr_nbr = from the above query.

Thanks

Abhi