We use an ODBC Stage to connect to Datacom tables. Some date fields on the database are stored in 3 separate fields DT_YY, DT_MM, DT_DD. Is it possible to concatenate these fields in the select of the query?
Tried
DT_YY || DT_MM || DT_DD - failed, datacom doesn't like the ||
DT_YY + DT_MM + DT_DD - worked, but with unintended results 2008 + 01 + 01 returned 2010
Also tried aliases to set the year to 20080000 and month to 100 but the select didn't recognize the alias fields.
Any suggestions?
ODBC Derivation?
Moderators: chulett, rschirm, roy
I can assemble the date within datastage but if I can assemble the date in the query it would allow me to use the query to select only data that has changed in the last 30 days. Reading 4M rows to get the 1000 that changed seems clunky but perhaps that's what will have to be done.
I thought the derivation might be able to concatenate the fields together (similiar to derivation in the transformer stage) but you correctly point out that this is a Datacom SQL question.
Perhaps our DBA could create a view that concatenates the fields.
Thanks for the quick response.
I thought the derivation might be able to concatenate the fields together (similiar to derivation in the transformer stage) but you correctly point out that this is a Datacom SQL question.
Perhaps our DBA could create a view that concatenates the fields.
Thanks for the quick response.
Should also be possible to query those separate field in such a manner as to get only records which have changed in the last 30 days. Might be a little goofy looking but I would think it could be done.
Or maybe (YEAR*10000)+(MONTH*100)+DAY to build the date? With some way to ensure the month and day end up with leading zeroes for the single digit values... hmmm...
Or maybe (YEAR*10000)+(MONTH*100)+DAY to build the date? With some way to ensure the month and day end up with leading zeroes for the single digit values... hmmm...
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Yes. STP_DT_YY * 10000 + STP_DT_MM * 100 + STP_DT_DD creates a new field (STP_DT) but the query doesn't recognize the new field in the WHERE clause.
SYSUSR.CF_CC_REL.ROLE_STP_DT_YY * 10000 + SYSUSR.CF_CC_REL.ROLE_STP_DT_MM * 100 + SYSUSR.CF_CC_REL.ROLE_STP_DT_DD FROM SYSUSR.CF_CC_REL WHERE (SYSUSR.CF_CC_REL.ROLE_STP_DT > '20080000')
SQLSTATE=S1000, DBMS.CODE=-9
[DataStage][SQL Client][ODBC][CA][ODBC CA-Datacom/DB Driver][CA-DATACOM/DB] ILLEGAL COLUMN NAME: ROLE_STP_DT
SYSUSR.CF_CC_REL.ROLE_STP_DT_YY * 10000 + SYSUSR.CF_CC_REL.ROLE_STP_DT_MM * 100 + SYSUSR.CF_CC_REL.ROLE_STP_DT_DD FROM SYSUSR.CF_CC_REL WHERE (SYSUSR.CF_CC_REL.ROLE_STP_DT > '20080000')
SQLSTATE=S1000, DBMS.CODE=-9
[DataStage][SQL Client][ODBC][CA][ODBC CA-Datacom/DB Driver][CA-DATACOM/DB] ILLEGAL COLUMN NAME: ROLE_STP_DT