order by clause does not working

[ananyaghosh]

Hi,

I am using the following code:

[test]:

first 10000

LOAD

    PROC_ID,

    SEVERITY,

    ALERT_ID,

    CREATION_DATE as ALERT_CREATE_TIMESTAMP,

    //left (timestamp(CREATION_DATE),10) as ALERT_CREATE_DATE,

    Left( CREATION_DATE, Index( CREATION_DATE,' ') -1 ) as ALERT_CREATE_DATE,

    STATUS_TYPE,

    EVENT_ID

From [$(vFinalDest)EDI_CC_ALERT_FULL_LOAD.qvd](qvd)

order by CREATION_DATE desc

;

when I Load the data it gives me error for 'order by CREATION_DATE desc'

can anyone give me suggestion for this?

[sunny_talwar · Accepted solution]

You cannot use Order By clause in a Qvd or Excel load... try to fix the order of load while you create the qvd or use it in a resident load like this

[test]:

first 10000

LOAD

    PROC_ID,

    SEVERITY,

    ALERT_ID,

    CREATION_DATE as ALERT_CREATE_TIMESTAMP,

    //left (timestamp(CREATION_DATE),10) as ALERT_CREATE_DATE,

    Left( CREATION_DATE, Index( CREATION_DATE,' ') -1 ) as ALERT_CREATE_DATE,

    STATUS_TYPE,

    EVENT_ID

From [$(vFinalDest)EDI_CC_ALERT_FULL_LOAD.qvd](qvd);

Final:

NoConcatenate

LOAD *

Resident test

order by CREATION_DATE desc;

DROP Table test;

[Anil_Babu_Samineni]

That is limitation rule for Order By. So, you need Another table loading from one to another like

Final:

NoConcatenate

Load * Resident Table Order By ..;

[ananyaghosh]

Hi,

My 'order by' problem has been solved and one very critical problem has come to me as:-

when i specify 'first 10000' before load statement, I got proper data using the below statement:-

   Left( CREATION_DATE, Index( CREATION_DATE,' ') -1 ) as ALERT_CREATE_DATE

but whenever I replace the 'first 10000' code I got nothing in that column.

I have total 7834 rows, so it is not a problem of less amount of data loading. Please give me advise what may cause this problem.

Thanks,

Sandip Ghosh

[sunny_talwar]

That is very strange... I have never heard of this kind of issue before... would you be able to share a sample to show the issue?

[markp201]

What about using order by in a preceding load?