QVD Loader

[mohiahmed]

Hello all,

I need your help on a loader.  I am loading data onto a QVD and when I put a date criteria on there it seems to fail. Any pointers would be a great help. 

Many thanks in advance

AGREEMENT:

SQL

SELECT

          ID,

         SOURCESYSTEMID,

         AGREEMENTNUMBER,

         "EFF_STA_DTE",

         "EFF_END_DTE",

         CURRENTLPIRATE

FROM E_AFPG_BASE.AGREEMENT

where EFF_END_DTE >='31/12/3499';

//where Date(Floor(EFF_END_DTE)) >='31/12/3499';

STORE AGREEMENT INTO (QVD);

drop table AGREEMENT;

[YoussefBelloum]

Hi,

AGREEMENT:

SQL

SELECT

          ID,

        SOURCESYSTEMID,

        AGREEMENTNUMBER,

        "EFF_STA_DTE",

        "EFF_END_DTE",

        CURRENTLPIRATE

FROM E_AFPG_BASE.AGREEMENT

where EFF_END_DTE >='31/12/3499';

//where Date(Floor(EFF_END_DTE)) >='31/12/3499';

STORE AGREEMENT INTO 'C:\Users\h04818318\Desktop\QlikView\ALFA\AGREEMENT.QVD' (QVD);

drop table AGREEMENT;

[Mark_Little]

Hi,

What error do you receive?

I would make sure the date format match frist

So something like

DATE(EFF_END_DTE,'DD/MM/YYYY') >= '31/12/3499'     //Also do you mean a year of 3499?

If that doesn't work it maybe that the field isn't seen as Date so maynbe also need to look at DATE#() function.

DATE(DATE#(EFF_END_DTE,'DD/MM/YYYY'),'DD/MM/YYYY') >= '31/12/3499'

Basically the DATE#() changes a string to a date, you need to make sure the supplied format in the function matches the format in the field.

Mark

[OmarBenSalem]

Can you please share a screen shot on the error message?

[mohiahmed]

Thanks for the help guys, but still not working.  Getting the below error message

SQL##f - SqlState: 37000, ErrorCode: 4294963590, ErrorMsg: [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: Data Type "DATE#" does not match a Defined Type name.

AGREEMENT:

SQL

SELECT

          ID,

        SOURCESYSTEMID,

        AGREEMENTNUMBER,

        "EFF_STA_DTE",

        "EFF_END_DTE",

        CURRENTLPIRATE

FROM E_AFPG_BASE.AGREEMENT

Where DATE(DATE#(EFF_END_DTE,'DD/MM/YYYY'),'DD/MM/YYYY') >= '31/12/3499'

STORE AGREEMENT INTO 'C:\Users\h04818318\Desktop\QlikView\ALFA\AGREEMENT.QVD' (QVD)

[OmarBenSalem · Accepted solution]

That's normal since you're doing it in the select part in which you should use the language of your database, in this case teradata; please do as follow:

AGREEMENT:

load *

Where DATE(DATE#(EFF_END_DTE,'DD/MM/YYYY'),'DD/MM/YYYY') >= '31/12/3499';

SQL

SELECT

          ID,

        SOURCESYSTEMID,

        AGREEMENTNUMBER,

        "EFF_STA_DTE",

        "EFF_END_DTE",

        CURRENTLPIRATE

FROM E_AFPG_BASE.AGREEMENT

;

STORE AGREEMENT INTO 'C:\Users\h04818318\Desktop\QlikView\ALFA\AGREEMENT.QVD' (QVD)

[mohiahmed]

Thanks mate, still getting an error

SQL##f - SqlState: 22003, ErrorCode: 4294963761, ErrorMsg: [Teradata][ODBC Teradata Driver][Teradata Database] A character string failed conversion to a numeric value.

AGREEMENT:

SQL

SELECT

          ID,

        SOURCESYSTEMID,

        AGREEMENTNUMBER,

        "EFF_STA_DTE",

        "EFF_END_DTE",

        CURRENTLPIRATE

FROM E_AFPG_BASE.AGREEMENT

where EFF_END_DTE >='31/12/3499'

[mohiahmed]

Thanks mate, that is working well.  Downloading the data now and not failing over. 

Once again, many thanks

[mohiahmed]

Hi Omar, I need your help again if possible please.

When I was running the above query, it was running and then didn't bring any data.  I looked up at Teradata and noticed that date was backwards and tried that too and still noting coming up.

Teradata query I am using is to get the data.

Select * from E_AFPG_BASE.

AGREEMENT

Where EFF_END_DTE>= cast( '3499/12/31' as date)

For loader I have tried Where DATE(DATE#(EFF_END_DTE,'YYYY/MM/DD'),'YYYY/MM/DD') >= '3499/12/31';

Will I be able to use Cast and possibly date format is not recognised somehow?

[OmarBenSalem]

what if you do as follow:

AGREEMENT :

SQL

SELECT

          ID,

        SOURCESYSTEMID,

        AGREEMENTNUMBER,

        "EFF_STA_DTE",

        "EFF_END_DTE",

        CURRENTLPIRATE

FROM E_AFPG_BASE.AGREEMENT

Where EFF_END_DTE>= cast( '3499/12/31' as date)

;

STORE AGREEMENT INTO 'C:\Users\h04818318\Desktop\QlikView\ALFA\AGREEMENT.QVD' (QVD)