need QUARTER to load the TIME dimension table
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
need QUARTER to load the TIME dimension table
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.
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.
Last edited by I_Server_Whale on Tue Dec 05, 2006 4:26 pm, edited 1 time in total.
-
- Participant
- Posts: 82
- Joined: Thu Dec 02, 2004 10:27 pm
- Location: INDIA
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
need QUARTER to load the TIME dimension table
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.
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.
Last edited by I_Server_Whale on Tue Dec 05, 2006 4:27 pm, edited 1 time in total.
Re: need QUARTER to load the TIME dimension table
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.naveendronavalli wrote:In Informatica, I just had to give the SQL statement TO_CHAR(date_column, 'Q') and it extracted the quarter information from the source.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Naveen,
If your input column is in the format "mm/dd/yyyy" and is called InDate then:
See the BASIC Guide Page C-12 for details.
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")
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
need QUARTER to load the TIME dimension table
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.
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.
Re: need QUARTER to load the TIME dimension table
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:naveendronavalli wrote: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.
Code: Select all
OCONV(ICONV(InLink.InDate,"D4/MDY"),"DQ")
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
stuck @ QUARTER
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.
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.
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")
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Derivation expressions in the Transformer stage are just that; expressions.
You specified that your derivation expression was
In fact, you only need the right-hand side.
You specified that your derivation expression was
Code: Select all
OutLink.Quarter = OCONV(ICONV(InLink.InDate,"D4/MDY"),"DQ")
Code: Select all
OCONV(ICONV(InLink.InDate,"D4/MDY"),"DQ")
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: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
stuck @ QUARTER
Hi Craig and Ray,
Thanks for your responses. I did put only the left hand side of the expression which is
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.
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")
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.
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?
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers