Sorting Date columns
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 78
- Joined: Fri Mar 02, 2007 4:54 am
- Location: CHENNAI
Sorting Date columns
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..
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..
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?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 78
- Joined: Fri Mar 02, 2007 4:54 am
- Location: CHENNAI
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..
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..
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 78
- Joined: Fri Mar 02, 2007 4:54 am
- Location: CHENNAI
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..
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..
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 78
- Joined: Fri Mar 02, 2007 4:54 am
- Location: CHENNAI
I didn't use any special sort settings, just defined the first column as a key.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 78
- Joined: Fri Mar 02, 2007 4:54 am
- Location: CHENNAI
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...
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...
-
- Participant
- Posts: 40
- Joined: Sun Jan 21, 2007 1:52 pm
- Location: Chennai
- Contact:
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
-
- Participant
- Posts: 78
- Joined: Fri Mar 02, 2007 4:54 am
- Location: CHENNAI
-
- Participant
- Posts: 78
- Joined: Fri Mar 02, 2007 4:54 am
- Location: CHENNAI
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 40
- Joined: Sun Jan 21, 2007 1:52 pm
- Location: Chennai
- Contact:
Hi ArndW,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.
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