DATA Step, Macro, Functions and more

teradata pass through, getting wrong date range

Accepted Solution Solved
Reply
Valued Guide
Posts: 858
Accepted Solution

teradata pass through, getting wrong date range

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;


Accepted Solutions
Solution
a week ago
Super User
Super User
Posts: 6,499

Re: teradata pass through, getting wrong date range

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


All Replies
Super User
Super User
Posts: 7,400

Re: teradata pass through, getting wrong date range

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.

Valued Guide
Posts: 858

Re: teradata pass through, getting wrong date range

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

 

Solution
a week ago
Super User
Super User
Posts: 6,499

Re: teradata pass through, getting wrong date range

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)
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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