Originally posted by: jvanboga

Basic question but can't figure out what I'm doing wrong....

Built type tree from Oracle table using DBID. One column is defined as Date. In the type tree the column is defined as Date/Time {CCYY-MM-DD} [ {HH24[:MM:SS]]} .

In the map I'm dbselecting a date from another table to insert in the column above. The selected date does not populate the column even though the trace file indicates the select found the value in question. I've used the todatetime(dbselect(....), {CCYY-MM-DD} [ {HH24[:MM:SS]]} ) as my rule but the output is still null. If I use the same rule in a text/filler the correct value is populated.

What am I missing?
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: paul.brett

Can you break it down a bit?

What is output of map when you change the target to file, and the rule is just your plain DBLOOKUP() without the daet conversion bits?

What version are you using?

Can you paste the DBLOOKUP rule here? (or a sanitised version of it - if it contains business sensitive data)

Paul.
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: jvanboga

Actually, the map in question is reading an external file, mapping it to a flat file with an internal format. That file is loaded to multiple tables by another map. So the date in question is additional data taken from a production table to assist in later processing. Here is the DBSelect with a hard coded value...

DBLOOKUP("SELECT INS_DATE FROM X_TABLE_1 WHERE COL_1 || COL_2 || COL_7 || COL_8 = '123456XYZ789ABC'", "-mdq x_tables.mdq -dn cus -trace trace_file.dbl" ))

Here is an extract from the lookup's trace file....

<7984-4896>: SELECT INS_DATE FROM X_TABLE_1 WHERE COL_1 || COL_2 || COL_7 || COL_8 = '123456XYZ789ABC'
<7984-4896>: Statement execution succeeded.
<7984-4896>: The columns are of the following types:
<7984-4896>: Column 1 (INS_DATE) type is DATE.
<7984-4896>: Number of buffers in fetch array = 3276
<7984-4896>: Writing results to a buffer.
<7984-4896>: Retrieved 1 records (21 bytes).
<7984-4896>: Returned status: (0) Success
The INS_DATE value returned is '2008-12-30 13:11:44'.

The element/field it is written to is defines as Date&Time {CCYY-MM-DD} [ {HH24:MM:SS]}

The problem is that the query returns the date and time as text. So, amongst other ways, the most logical thing for me to do was to wrap the query in rule todatetime(dbselect(....), "{CCYY-MM-DD} [ {HH24:MM:SS]}"). When doing this the output field is populated with null. I also tried defining the date/time as "{CCYY-MM-DD} {HH24:MM:SS}" and "CCYY-MM-DD HH24:MM:SS" and "CCYY-MM-DD HH:MM:SS" but the effect is the same.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: paul.brett

Are you sure abuot that returning format? I would have thought you were likely to get fractions of a second as well.

Try this:

=TODATETIME(DBLOOKUP("...","..."),"{CCYY-MM-DD} {HH24:MM:SS.3-3}")

Paul
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: paul.brett

Let's try that again:

=TODATETIME(DBLOOKUP(
"...",
"..."),
"{CCYY-MM-DD} {HH24:MM:SS.3-3}")

Paul
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: jvanboga

Thanks Paul, it doesn't bring back mil secs. I had thought that too but put the same basic rule writing output to a text field to verify.

Not sure what the dif was but in my test map I replaced my

"{CCYY-MM-DD} {HH24:MM:SS}"

with your

"{CCYY-MM-DD} {HH24:MM:SS.3-3}" change to "{CCYY-MM-DD} {HH24:MM:SS}"

and it worked, wierd!
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: thorstenhirsch

Since DBLOOKUP() is an exit call that might fail, I suggest to split the rule:

dbquery Field
=VALID(DBLOOKUP(...),FAIL("dblookup failed with rc="+LASTERRORCODE()))

date Field
=TODATETIME(dbquery Field,"{CCYY-MM-DD} {HH24:MM:SS.3-3}")
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: jvanboga

You are right, for resolving this issue though I simplified everything.

Thanks, it always good to get the reminder.
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange