Limit the Data load

[fawazqutami]

Hello guys,

I'm trying to limit my data load > 2013 ....

IH_BIL_CHARGE_SLIPS:

LOAD *; SQL

SELECT ID IH_BIL_CHS_ID,

       TIME,

       DATE_DATE,

       SELFPAYER_LC,

       PAYER1_LC,

       SELFPAYER_PLDIS_LC,

       PAYER1_PLDIS_LC,

       DISCOUNT,

       QTY,

       IH_POS_POS_ID,

       IH_HCP_HCP_ID,

       IH_EMR_VST_ID,

       FN_STR_MIT_ID,

       IH_EMR_PKG_ID,

       STATUS CHS_STATUS,

       FULFILLED,

       IS_PAID,

       RFND_ITEM,

       PKG_COMP,

       PKG_ITEM,

       DATE_CREATED CHS_DATE_CREATED,

       DATE_MODIFIED CHS_DATE_MODIFIED,

       CREATED_BY CHS_CREATED_BY,

  FROM QLIK.IH_BIL_CHARGE_SLIPS

  WHERE

    DATE_DATE > '01/01/2013';

But I've got the below message:

(ErrorSource: Microsoft OLE DB Provider for Oracle, ErrorMsg: One or more errors occurred during processing of command.: SQL SELECT ID IH_BIL_CHS_ID, TIME, DATE_DATE, SELFPAYER_LC, PAYER1_LC, SELFPAYER_PLDIS_LC, PAYER1_PLDIS_LC, DISCOUNT, QTY, IH_POS_POS_ID, IH_HCP_HCP_ID, IH_EMR_VST_ID, FN_STR_MIT_ID, IH_EMR_PKG_ID, STATUS CHS_STATUS, FULFILLED, IS_PAID, RFND_ITEM, PKG_COMP, PKG_ITEM, DATE_CREATED CHS_DATE_CREATED, DATE_MODIFIED CHS_DATE_MODIFIED, CREATED_BY CHS_CREATED_BY, FROM QLIK.IH_BIL_CHARGE_SLIPS WHERE DATE_DATE > '01/01/2013')

any help

[fawazqutami]

I think I solved it ......

Load * where Year(Date(DATE_DATE)) > '2013;

SQL Select .... From table name;

[oknotsen]

You are trying to match a date (which is stored as a number) with a piece of text. That will not work.

You will first have to convert that piece of text to an actual date (or the related number).

[oknotsen · Accepted solution]

That quote right before 2013 should not be in there.

Note that you are effectively loading the years 2014 and later where as in your initial script you seem to be trying to load the data of January 2nd 2013 and later.

[fawazqutami]

Thank you. you are right...

[immkhan2]

The standard date format of Oracle is DD-MON-YYYY (if it is not changed in control file of database). Define the format with Qlik date function and you can easily put the where clause.

[Digvijay_Singh]

Using Where clause in SQL SELECT will be faster than doing it in preceding load as records will be filtered at source. Try something like this if your date comparison is not working -

WHERE DATE_DATE > to_date('01-01-2013','DD-MM-YYYY');

[fawazqutami]

Thank you Digvijay