BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Steelers_In_DC
Barite | Level 11

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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)

View solution in original post

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Steelers_In_DC
Barite | Level 11

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'.

 

Tom
Super User Tom
Super User

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)

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2732 views
  • 0 likes
  • 3 in conversation