Sorting Date columns

MOHAMMAD.ISSAQ · Post by **MOHAMMAD.ISSAQ** » Wed May 07, 2008 4:51 am

I have a job where i'm soring a DATE column using sort stage.
The job is running fine but the sort column(DATE column) is blank at the output.

When i change the datatype to VARCHAR the output is fine.

Can anyone pls tell me what's the problem with DATE columns?

Pls provide pointers to this..

ArndW · Post by **ArndW** » Wed May 07, 2008 4:56 am

Server jobs don't have a DATE type so there shouldn't be a difference for the sort stage. Where are you changing the datatype?

MOHAMMAD.ISSAQ · Post by **MOHAMMAD.ISSAQ** » Wed May 07, 2008 5:10 am

First of all thanks for the reply.

At the Input file stage(sequential file stage) the SQL type is DATE i've changed it to VARCHAR, similarly for the Output file stage i've change SQL type to VARCHAR from DATE.

If DATE type is not there in server jobs then "What's this?"

I didn't understand what you've told Arndw?

Could you please explain me..

ArndW · Post by **ArndW** » Wed May 07, 2008 5:14 am

So your job only has a source sequential stage, a sort stage and an output sequential file stage? If not, then in what stages are you using this column - chances are high that it's value is being changed elsewhere.

MOHAMMAD.ISSAQ · Post by **MOHAMMAD.ISSAQ** » Wed May 07, 2008 5:34 am

Sorry for not providing all the information.

Yes you're correct i'm using a sequential file stage at input and ouput, and a sort stage in between.

I have only one job.The output of the file is nowhere used..

Could you pls tell me why it's not displaying the DATE column data?

And one more thing you've told about the DATE datatype in server jobs?

Pls help me out..

ArndW · Post by **ArndW** » Wed May 07, 2008 6:03 am

I have just created a text file with 100 lines and contents like "2007-01-01", "2008-02-02" then wrote a job to read, sort and write it to another text file. Regardless of whether I define it as DATE(10) or VarChar(10) it works correctly.

ray.wurlod · Post by **ray.wurlod** » Wed May 07, 2008 3:15 pm

My guess is that you have some empty or null dates and these are sorting to the beginning, leading you to believe that they are all coming out empty. Scroll down until you find a date.

MOHAMMAD.ISSAQ · Post by **MOHAMMAD.ISSAQ** » Wed May 07, 2008 11:29 pm

This is a sort tried only with 10 records with no NULL values.

Is there in a format we need to follow while sorting DATE columns.

Arndw,
Could you please tell me what're the settings you've put while sorting?

Please provide pointers to this...

ArndW · Post by **ArndW** » Thu May 08, 2008 4:02 am

I didn't use any special sort settings, just defined the first column as a key.

MOHAMMAD.ISSAQ · Post by **MOHAMMAD.ISSAQ** » Thu May 08, 2008 5:44 am

I given the DATE column as key also but still the output is same.
I think in sequential file stage the key has no significance.

I'll explain the job in detail say:My job has one Input and Ouput sequentail file stage in between one sort stage is present which is sorting the DATE column.

I have three columns in the Input sequential file(The same metadata at Output also):
Empno - Integer
Ename - Varchar
HireDate - Date

The data i've given as :

EMPNO|ENAME|HIREDATE
7644|SCOTT|13/11/2006
7553|ALLEN|12/01/2000
3455|TOM|11/01/1998

The input file stage is reading the output properly and the job is successfully ran.
The final output (sorted output) i got as:

EMPNO|ENAME|HIREDATE
7644|SCOTT|
7553|ALLEN|
3455|TOM|

That means it's not at all sorting the data.

DS Gurus please provide your valuable inputs to this...

muruganr117 · Post by **muruganr117** » Thu May 08, 2008 6:25 am

MOHAMMAD.ISSAQ wrote:I given the DATE column as key also but still the output is same.
I think in sequential file stage the key has no significance.

I'll explain the job in detail say:My job has one Input and Ouput sequentail file stage in between one sort stage is present which is sorting the DATE column.

I have three columns in the Input sequential file(The same metadata at Output also):
Empno - Integer
Ename - Varchar
HireDate - Date

The data i've given as :

EMPNO|ENAME|HIREDATE
7644|SCOTT|13/11/2006
7553|ALLEN|12/01/2000
3455|TOM|11/01/1998

The input file stage is reading the output properly and the job is successfully ran.
The final output (sorted output) i got as:

EMPNO|ENAME|HIREDATE
7644|SCOTT|
7553|ALLEN|
3455|TOM|

That means it's not at all sorting the data.

DS Gurus please provide your valuable inputs to this...

Hi Issaq,

Please check whether FIRST ROW IS HEADER COLUMNS Enabled in input seq file properties? if not please enable it and execute again.

regards

MOHAMMAD.ISSAQ · Post by **MOHAMMAD.ISSAQ** » Thu May 08, 2008 6:35 am

It's already enabled...

MOHAMMAD.ISSAQ · Post by **MOHAMMAD.ISSAQ** » Thu May 08, 2008 6:36 am

It's already enabled...

ArndW · Post by **ArndW** » Thu May 08, 2008 7:12 am

in the source does "view data" show the data correctly for the date? If you sort via "date" then the order won't be as expected, since the format is dd/mm/yyyy; in order to correctly sort this ascending or descending you should convert the field to the internal format using ICONV(In.Date,"d4/DMY') and sort on the numeric result.

muruganr117 · Post by **muruganr117** » Thu May 08, 2008 8:11 pm

ArndW wrote:I have just created a text file with 100 lines and contents like "2007-01-01", "2008-02-02" then wrote a job to read, sort and write it to another text file. Regardless of whether I define it as DATE(10) or VarChar(10) it works correctly.

Hi ArndW,

Kindly excuse, i could not view the complete content of previous post.
i gave a try, date format is dd/mm/yyyy,i got the output as NULL in date field. I had an alternative for this,used ICONV, OCONV and added Transformer stage in between & got the data sorted as per Date ,
But still it does not form the final solution, Can you please let me know whether the text file created by you contained Date values alone or can you post the sample text file data prepared during test, because when i defined as VarChar i got output, but not in sorted manner though for dd/mm/yyyy format, when data type is DATE, output is still NULL?

Thank you for inputs

regards