need QUARTER to load the TIME dimension table

[I_Server_Whale]

Hi all,
I'm a beginner to Datastage. I'm using version 6.0. I have created a job to load a time dimension table which consists of three stages as follows;

1. A Input(source) ODBC stage where one of the columns is date in the format "mm/dd/yyyy". Its SQl type is "Timestamp".

2. A transformer stage to extract the DAY, MONTH, YEAR and QUARTER info from the above ODBC source(Oracle).

3. A final stage to load the transformed data into the TIME dimension table.

I have successfully extracted the DAY, MONTH and YEAR from the source using the "DateYearFirstGetMonth(%InputDate%) " and so on from the "DS Transform" expression in the transformer.

BUT, I'm unable to figure out how to extract the QUARTER information from the source using the transformer. Is there any in-built transform for extracting QUARTER from a date?
Or do I have to write a custom SQL query? If so how do I write one? Is TO_CHAR(date_column,'Q') sufficient? Please help me out. Your help would be tremendously appreciated. Thanks a lot,

Whale.

[dsxuserrio]

Create a time dimension table upfront and load values from 1900 to 2100 or whatever range you want. For daily data take date (20050228 ) and join to the time dimension table to get quarter ,month week , or any info you want.

[I_Server_Whale]

Hi,
Thanks a ton. But I didn't understand the solution. It seems a bit advanced for me. In Informatica, I just had to give the SQL statement
TO_CHAR(date_column, 'Q')

and it extracted the quarter information from the source. Is there anything similar to that or any in-built transform in Datastage????

Thanks again. Help is greatly appreciated.

regards,
Whale.

[chulett]

In Informatica, I just had to give the SQL statement TO_CHAR(date_column, 'Q') and it extracted the quarter information from the source.

That's Oracle - not Informatica or DataStage - doing the work for you. If you want to do it that way, go ahead! Use the same sql statement in the Derivation of the field in your DataStage job's source stage. Assuming the 'Column Generated SQL' option is chosen. Otherwise, custom sql.

[ArndW]

Naveen,

If your input column is in the format "mm/dd/yyyy" and is called InDate then:

Code: Select all

OutLink.Quarter = OCONV(ICONV(InLink.InDate,"D4/MDY"),"DQ")

See the BASIC Guide Page C-12 for details.

[I_Server_Whale]

Hi Craig & ArndW,
Thanks a lot for your replies. I tried putting the expression
OutLink.Quarter = OCONV(ICONV(InLink.InDate,"D4/MDY"),"DQ") with my equivalent link names in the Derivation of the transformer, but still it is red in color.

Also, how do I choose "column generated SQL" option? And how and where do I write custom SQL?

I have the "SQL Type" as Char, length =6 and the "Data Element type" as QUARTER.TAG in the target table Is this correct? Or do I have to change them.
Once again, a million thanks to you guys,
regards,
Naveen.

[chulett]

I tried putting the expression OutLink.Quarter = OCONV(ICONV(InLink.InDate,"D4/MDY"),"DQ") with my equivalent link names in the Derivation of the transformer, but still it is red in color.

Hope you don't mean that literally. All you should have in the derivation is what you have on the right side of the equal sign:

Code: Select all

OCONV(ICONV(InLink.InDate,"D4/MDY"),"DQ")

Then you should be ok.

[I_Server_Whale]

Thanks a ton Craig. I realized that I was ending the statement with a semi-colon. The transformer stage is fine now. But the QUARTER column in the target table is empty.

I don't understand why. All other columns are being loaded fine except the QUARTER column has all empty values. What do you think the reason would be?

regards,
Naveen.

[chulett]

It means the conversion failed. Which should mean the incoming data doesn't fit the mask provided. If you are certain it is coming in MM/DD/YYYY format, try this slightly different derivation:

Code: Select all

OCONV(ICONV(InLink.InDate,"D/MDY[2,2,4]"),"DQ")

[ray.wurlod]

Derivation expressions in the Transformer stage are just that; expressions.

You specified that your derivation expression was

Code: Select all

OutLink.Quarter = OCONV(ICONV(InLink.InDate,"D4/MDY"),"DQ") 

In fact, you only need the right-hand side.

Code: Select all

OCONV(ICONV(InLink.InDate,"D4/MDY"),"DQ") 

[chulett]

Dang, there's an echo in here.

[ray.wurlod]

For some reason (ISP?) my posts are taking a long time to appear today.

[chulett]

I've had some weird problems, too. Seems like problems posting, "cannot find host", yada yada, then it finally goes through. Come back and I've posted the same thing three times, then have to scrape out two.

No worries.

[I_Server_Whale]

Hi Craig and Ray,
Thanks for your responses. I did put only the left hand side of the expression which is

Code: Select all

OCONV(ICONV(DSLink23.ORD_DT,"D/MDY[2,2,4]"),"DQ")

The transformer accepts the code.
But still the QUARTER column is empty in the target. My source(Oracle table) column "ORD_DT" has its SQL type as 'Time Stamp' and the "data element" as 'Time Stamp'. The date is in MM/DD/YYYY format. The other columns load fine, except the QUARTER.

The flow is as follows:

ORD_HEADER Stage(SOURCE) -----> TRANSFORMER ----> TIME_DIM

The target table has these columns,

TIME_DIM_KEY, ORD_DT, YEAR, MONTH, DAY, QUARTER

The QUARTER column has its "SQL type" as 'Char(6)' and the "Data Element" as 'QUARTER.TAG'. Are these correct?

Please let me know where I'm going wrong. Once again, many thanks for your help.

regards,
Naveen.

[chulett]

What option in the source OCI stage are you using to generate the sql? Column Generated? Full? Custom?

If you let DataStage build the sql and you tell it the field is a Timestamp then you are not getting it in MM/DD/YYYY format. It supplies it in "YYYY-MM-DD HH24:MI:SS" format via a TO_CHAR() function.

Can you verify your source query? Either that or post it for us?