Group by Date column
Moderators: chulett, rschirm, roy
Group by Date column
Hi,
I am combining three inputs using Link Colletor stage and want to aggregate two columns group by three columns in the Aggregator Stage. In group by columns I have Date column and other two are smallint columns. When I run the job I getting the values for the smallint columns but getting no data for Date column. Its just passing null values.
My Job design is like this:
Src--->3Trns stages--->Link Collect--->Agg---->Tnnsfor--->Db2Stage
Can't we groupby the Date Column in the Aggregator stage? If so plz help me to solve this issue.
Thanks in advance.
I am combining three inputs using Link Colletor stage and want to aggregate two columns group by three columns in the Aggregator Stage. In group by columns I have Date column and other two are smallint columns. When I run the job I getting the values for the smallint columns but getting no data for Date column. Its just passing null values.
My Job design is like this:
Src--->3Trns stages--->Link Collect--->Agg---->Tnnsfor--->Db2Stage
Can't we groupby the Date Column in the Aggregator stage? If so plz help me to solve this issue.
Thanks in advance.
Ravi
There is no null Date record coming as Input, but after the aggregation I am not able to find any value inthe Date column. Just now I gone through the search that I found Date columns cannot be passed through as Group by columns. If to pass convert into internal then pass to output and then use oconv to get req format.
My question is when i convert to internal format before passing to Agg stage what should be the DataType of the Date column? If I change Dae type to Integer or char type, after the Agg stage again I need to change the datatype as Date. Am I correct? Please suggest me.
Actually I tried this way, I converted the date into integer by using substrings function(and Datatype made as Integer) and passed thru Agg stage afterthat in Transformer again I used substrings function to make it as date format(Ex: from 20071223 to 2007-12-23) and datatype made as Date and tried to load into Db2 db but it is rejecting data as it is treating the converted date as NULL. I don't know what could be the reason.
Appreciate your time and help.
My question is when i convert to internal format before passing to Agg stage what should be the DataType of the Date column? If I change Dae type to Integer or char type, after the Agg stage again I need to change the datatype as Date. Am I correct? Please suggest me.
Actually I tried this way, I converted the date into integer by using substrings function(and Datatype made as Integer) and passed thru Agg stage afterthat in Transformer again I used substrings function to make it as date format(Ex: from 20071223 to 2007-12-23) and datatype made as Date and tried to load into Db2 db but it is rejecting data as it is treating the converted date as NULL. I don't know what could be the reason.
Appreciate your time and help.
Ravi
Re: Group by Date column
if your date has time in it, please put varchar for that column in output.
Re: Group by Date column
if your date has time in it, please put varchar for that column in output.
sorry, I don't have time in my Date column. Its like DD/MM/YYYY. Can I do inthis way. My source Date column is Integer datatype(YYYYMMDD) and my target column is Date datatype(YYYY-MM-DD). Can I use substrings function to make my src col(YYYYMMDD) into date format(YYYY-MM-DD) and insert into tgt database(DB2)?
Plz help me to solve this issue.
Plz help me to solve this issue.
Ravi
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Why do you claim that you can't group by date in the Aggregator stage?
I don't believe you're right.
Have you tried converting the date to DataStage internal format (integer) and grouping by that?
I don't believe you're right.
Have you tried converting the date to DataStage internal format (integer) and grouping by that?
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.
Ray thanks for your reply. I could solve that problem by converting the Date column into internal format.
But now I got new issue. After aggregation I converted the Date column into external format(Date format like: YYYY-MM-DD) and loading into DB2 database as the Date Column is Key column inthe target db. Its not loading the data into the target db, when I put my target as Seq file its loading properly and for db loading its giving Warning msg like:
Ray I am not able to read your message fully. For that do I need to have or do something?
Plz help me.
But now I got new issue. After aggregation I converted the Date column into external format(Date format like: YYYY-MM-DD) and loading into DB2 database as the Date Column is Key column inthe target db. Its not loading the data into the target db, when I put my target as Seq file its loading properly and for db loading its giving Warning msg like:
But the job is running successfully with warning messages, its not aborting.CopyOfSFIPFactFncChkRetAnl11..trns_FactFncChkRetAnl: At row 1, link "to_FactFncChkRetAnl", while processing column "CHK_RET_DT"
Value treated as NULL
Attempt to convert String value "2007-12-03" to Date type unsuccessful
Ray I am not able to read your message fully. For that do I need to have or do something?
Plz help me.
Ravi
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hi my dear DataStage gurus,
I have come up with the new issue now. I have date col(ex: DDMMYYYY) of type Char. Now I want to convert that date into YYYY-MM-DD format and load that col of Date type into DB2 Database. When I used Substrings and converted the date(DDMMYYYY) into required format(YYYY-MM-DD) and loaded into db its loading some diff value.
Cann't we load the date value of Char type into Db2 db column of Date type by converting it into dateformat using Substrings function?
Plz help me to find the solution for this issue.
Thanks in advance
I have come up with the new issue now. I have date col(ex: DDMMYYYY) of type Char. Now I want to convert that date into YYYY-MM-DD format and load that col of Date type into DB2 Database. When I used Substrings and converted the date(DDMMYYYY) into required format(YYYY-MM-DD) and loaded into db its loading some diff value.
Cann't we load the date value of Char type into Db2 db column of Date type by converting it into dateformat using Substrings function?
Plz help me to find the solution for this issue.
Thanks in advance
Ravi
New problem = new post. This has nothing to do with grouping by a date column so shouldn't be discussed here. Otherwise we'd just allow one post per user (probably with a subject line like 'Hi' or 'Urgent') that just gets longer and longer.
As noted earlier, did you try sending the internal formatted date to DB2? From what I recall, that is what the stage is expecting. Also take a moment to search the forums for 'DB2 date' (search for 'all words' if exact doesn't help) as you are not the first person on the planet with this question.
As noted earlier, did you try sending the internal formatted date to DB2? From what I recall, that is what the stage is expecting. Also take a moment to search the forums for 'DB2 date' (search for 'all words' if exact doesn't help) as you are not the first person on the planet with this question.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers