When I use the below code I get a date range from 31JAN2011 to 30JUN2017. When I pull this data in teradata studio the date is in the format of 'yyyy-mm-dd'.
I have tried using datepart, or using the date like this date'2016-12-31'. I have also tried doing '2015-12-31' <= load_date <= '2016-12-31'. Nothing seems to be working for me.
Any ideas are appreciated.
proc sql ;
connect to TERADATA(SERVER=TPRDDB user=&USER password= "&PW" mode=teradata SESSIONS=200);
create table FDR_NCC_MASTER_RECORD as select * from connection to TERADATA
(
select
LOAD_DATE,
origacct,
credline,
extstat_pm
from EDWTD01P_ETL_STAGE_V.FDR_NCC_CONSUMER
where LOAD_DATE BETWEEN '2015-12-31' and '2016-12-31' and
extstat_pm not in('B','Z','C') or
extstat_pm = ''
order by origacct,load_date
;
);
disconnect from TERADATA;
quit;
I think the error is probably in the other parts of your WHERE condition.
create table FDR_NCC_MASTER_RECORD as
select * from connection to TERADATA
(
select LOAD_DATE
, origacct
, credline
, extstat_pm
from EDWTD01P_ETL_STAGE_V.FDR_NCC_CONSUMER
where LOAD_DATE BETWEEN '2015-12-31' and '2016-12-31'
and extstat_pm not in('B','Z','C')
or extstat_pm = ''
order by origacct , load_date
)
;
What did you mean by the last condition? Are you intendting that to match NULL values in Teradata? And if it did (which I doubt it will) then that means you all taking all observations with missing EXSTAT_PM not matter what value of LOAD_DATE they have.
Also you could explicitly let Teradata know that the you meant date literals, but ti is probably not required.
where LOAD_DATE BETWEEN DATE'2015-12-31' and DATE'2016-12-31'
and (extstat_pm not in('B','Z','C') or extstat_pm is null)
I think your should use date literals, not strings:
where LOAD_DATE BETWEEN '31JAN2011'd and '30JUN2017'd and...
Note the d after the dates.
When I run that i get the following error:
Teradata prepare: Syntax error, expected something like an 'AND' keyword between a string or a Unicode character literal and
the word 'd'.
I think the error is probably in the other parts of your WHERE condition.
create table FDR_NCC_MASTER_RECORD as
select * from connection to TERADATA
(
select LOAD_DATE
, origacct
, credline
, extstat_pm
from EDWTD01P_ETL_STAGE_V.FDR_NCC_CONSUMER
where LOAD_DATE BETWEEN '2015-12-31' and '2016-12-31'
and extstat_pm not in('B','Z','C')
or extstat_pm = ''
order by origacct , load_date
)
;
What did you mean by the last condition? Are you intendting that to match NULL values in Teradata? And if it did (which I doubt it will) then that means you all taking all observations with missing EXSTAT_PM not matter what value of LOAD_DATE they have.
Also you could explicitly let Teradata know that the you meant date literals, but ti is probably not required.
where LOAD_DATE BETWEEN DATE'2015-12-31' and DATE'2016-12-31'
and (extstat_pm not in('B','Z','C') or extstat_pm is null)
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.