need QUARTER to load the TIME dimension table

[I_Server_Whale]

Hi Craig,
You are absolutely RIGHT. Great!
I'm using the default "Generated query" option. Not 'Stored Procedure' nor 'User-defined Query'. Is that what you mean by Column Generated? Full? Custom?. Please let me know. This what I see when I click the "View SQL" tab on the SOURCE ODBC stage.

Code: Select all

SELECT ORDER_ENTRY_PROJECT.ORD_HEADER.ORD_ID, ORDER_ENTRY_PROJECT.ORD_HEADER.CUST_ID, ORDER_ENTRY_PROJECT.ORD_HEADER.ORD_DT, ORDER_ENTRY_PROJECT.ORD_HEADER.BILL_TO_ADDR1, ORDER_ENTRY_PROJECT.ORD_HEADER.BILL_TO_ADDR2, ORDER_ENTRY_PROJECT.ORD_HEADER.BILL_TO_CITY, ORDER_ENTRY_PROJECT.ORD_HEADER.BILL_TO_STATE, ORDER_ENTRY_PROJECT.ORD_HEADER.BILL_TO_ZIP, ORDER_ENTRY_PROJECT.ORD_HEADER.SHIP_TO_ADDR1, ORDER_ENTRY_PROJECT.ORD_HEADER.SHIP_TO_ADDR2, ORDER_ENTRY_PROJECT.ORD_HEADER.SHIP_TO_CITY, ORDER_ENTRY_PROJECT.ORD_HEADER.SHIP_TO_STATE, ORDER_ENTRY_PROJECT.ORD_HEADER.SHIP_TO_ZIP FROM ORDER_ENTRY_PROJECT.ORD_HEADER;

And when I view the data at the source stage. It does give me the date as you said like "YYYY-MM-DD HH24:MI:SS" format.

How I do get it in the MM/DD/YYYY format? Or is there any way where I can just use TO_CHAR() manually?

Thanks a lot Craig. I appreciate your help.

Regards,
Naveen.

[chulett]

Luckily, you don't need to get your date out in a different format, you just need to match your Iconv date mask with what is coming in.

We'll try substringing off the time, since we don't need it and then changing the mask around a bit:

Code: Select all

OCONV(ICONV(InLink.InDate[1,10],"D-YMD[4,2,2]"),"DQ")

Give that a shot!

[I_Server_Whale]

Hi Craig,
It works! :D ....Thank you so much. But is there any other way, without using the OCONV and ICONV. Or is it that I have to use them when I deal with dates?

Because in Informatica, all I had to do was this

Code: Select all

TO_CHAR(date_column, 'Q')

But I love DS.

Anyways,
Thank you, Thank you very much,

regards,
Naveen.

[chulett]

Waaaay back at the beginning of the thread, I mentioned this. You can do the exact same thing in DataStage because all you are doing is asking the source database (in this case Oracle) to do the conversion for you.

In your source OCI stage, in the derivation of the QUARTER field, you could put:

Code: Select all

TO_CHAR(ORD_DT, 'Q')

and it would generate the sql to select the quarter directly from Oracle without you having to take all of these 'extra' steps. Just like... that other product.

There's a little more to it as it depends on what method you pick to generate the sql. I prefer 'Column Generated' and this would be the easiest way to accomplish this. 'Custom SQL' should be avoided unless you absolutely need to use it and I wouldn't touch 'Fully Generated' with a 10 foot pole. But that's just me.

[ray.wurlod]

You could take a look at the QUARTER.TAG Transform, but this is not quite what you want. It expects a slightly different (again!) input date format, and returns yyyyQn.

You could, however, use this and the expression earlier as the definition for a new Transform that does exactly what you want.