Group by Date column

ravij · Post by **ravij** » Thu Dec 06, 2007 6:05 am

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.

chulett · Post by **chulett** » Thu Dec 06, 2007 8:39 am

Are any of the dates null? All it takes is one null coming into the Aggregator to get a null result out the other end.

ravij · Post by **ravij** » Thu Dec 06, 2007 9:12 am

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.

sachin1 · Post by **sachin1** » Thu Dec 06, 2007 10:00 am

if your date has time in it, please put varchar for that column in output.

sachin1 · Post by **sachin1** » Thu Dec 06, 2007 10:01 am

if your date has time in it, please put varchar for that column in output.

ravij · Post by **ravij** » Thu Dec 06, 2007 7:46 pm

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.

ray.wurlod · Post by **ray.wurlod** » Thu Dec 06, 2007 7:49 pm

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?

ravij · Post by **ravij** » Thu Dec 06, 2007 8:34 pm

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:

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

But the job is running successfully with warning messages, its not aborting.

Ray I am not able to read your message fully. For that do I need to have or do something?

Plz help me.

chulett · Post by **chulett** » Thu Dec 06, 2007 10:55 pm

You've got a new problem now and should (technically) start a new topic. However, try leaving the date in internal format, from what I recall that's what the DB2 stage expects.

ray.wurlod · Post by **ray.wurlod** » Fri Dec 07, 2007 12:48 am

Otherwise, determine what format DB2 expects dates to be in. Perhaps by selecting some.

ravij · Post by **ravij** » Sat Dec 08, 2007 6:02 am

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

chulett · Post by **chulett** » Sat Dec 08, 2007 7:59 am

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.

DSXchange

Group by Date column

Group by Date column

Re: Group by Date column

Re: Group by Date column