ODBC Derivation?

[danddmrs]

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?

[chulett]

So... a Datacom sql question rather than a DataStage question. Afraid I don't know squat about Datacom, is there anyone you work with that can help with that?

Or you can just assemble the date inside your job.

[danddmrs]

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.

[chulett]

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...

[danddmrs]

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

[chulett]

You'd have to reference the expression (again) in your where clause rather than the alias.

[danddmrs]

Perfect. Thanks Craig.

Removed the individual date components from the SELECT
Created a new field with the expression
Referenced the expression in the WHERE clause

[chulett]

Cool.