Load last 3 years Data from Sql Select

[SatyaPaleti]

Hi Friends,

I am loading data from SQL. 

Sql file contains data from 2004 but i need to load only last 3 years data. How can we do it?

Here is the script I am using

Test:

load 

CUSTID    as   [Customer Id],

CNAME    as    [Customer Name]

CJD           as    [Customer Join Date] ;     CJD is in MM/DD/YYYY format

SQL Select CUSTID  CNAME  CJD  from CUST

Where     year(CJD) > ='2017';         

When I am loading data it's throwing an error.  Could some can guide me How to fetch last 3 years data in this case

Thanks and Regards,

Satya

[ignacio_pena]

Hello Satya,

I think this script should work:

Test:

load 

CUSTID    as   [Customer Id],

CNAME    as    [Customer Name]

CJD           as    [Customer Join Date] ;    

SQL Select 

CUSTID,  CNAME,  CJD 

from CUST

Where     TO_DATE(CJD, 'MM/DD/YYYY') > ='01/01/2017'; 

Another option in the SQL where is:

SQL Select 

CUSTID,  CNAME,  CJD 

from CUST

Where    EXTRACT(YEAR FROM CJD) = '2017';

Regards,

[SatyaPaleti]

Hi,

Thank you so much for your response.  I have used both of your queries but still code is not getting executed. Following error I am facing

/QTQVC/OPEN_STREAM failed after 00:00:00 Key = SQL_ERROR (ID:00 Type:E Number:001 Comma without preceding colon (after OPEN ?).)

Thanks and Regards,

Satya

[jwang2016]

you can use the code below to load only last 3 years data from the table.

select * from table_name where field_year in (select distinct field_year from table_name order by 1 desc limit 3)

[SatyaPaleti]

Hi,

Thanks for your responce. Year field is not available

Thanks and Regards,

Satya

[jwang2016]

get the year from the field that contains the year info.

[mikecrengland]

Is CJD a date data type? you may need to convert it.

mike