Lookup

laxmi_etl · Post by **laxmi_etl** » Tue Mar 06, 2007 5:48 pm

Hi-

I am having a problem when I try to have a lookup with a field to get a key.
The source field I have is 'A' with a CHAR datatype and the lookup table
lookup field 'B' with a TIMESTAMP datatype When I try to join these two fields to get a new field it's giving lot of errors.

I tried to change the source table field name to 'B' and datatype to timestamp in the Transformer but still it's not working

All the records are coming upto lookup and after that the data is not moving Looks like something is going on in Lookup Stage.

Giving fallowing errors.

Lookup,1: Field "B" has keyprep export format error; value: *******************
Failed keylookup on table (/u01/Ascential/DataStage/tldscratch/dynLUT129846247cfbd77)

Is there any suggestion

Thanks

kumar_s · Post by **kumar_s** » Tue Mar 06, 2007 6:24 pm

The datatype can be directly changed in Transformer stage. You will need an explicit conversion function. You need to make sure the key field of main stream as well as the lookup stream are of same datatype and same length. Atlest compatible datatype.
The warning that been shown is due to the reason, Null value is assigned in Date field due to improper conversion of datatype.

us1aslam1us · Post by **us1aslam1us** » Tue Mar 06, 2007 7:21 pm

In Enterprise Edition, you need to be bit careful with the conversions and format unlike server edition where direct conversions are possible. Try doing an explicit conversion like StringtoTimestamp on field 'A' in the transformer and do not forget to handle the Null values existing in that field.

laxmi_etl · Post by **laxmi_etl** » Tue Mar 06, 2007 9:18 pm

I did the same way that you explained but when I try to pass value for the nulls it's giving the fallowing error.

Error in output column derivation expression for column 'B' in link DSLink83. Invalid conversion requested from a int8 to a timestamp

kumar_s · Post by **kumar_s** » Tue Mar 06, 2007 9:27 pm

Could you explain you job design? What is DSLink83 connecting to?
Was your conversion successful?
What is the source value and what is the conversion logic you used?
If you write the data to a sequential file before lookup, were you able to the value of timestamp getting converted correctly?

laxmi_etl · Post by **laxmi_etl** » Tue Mar 06, 2007 9:42 pm

DSLINK88 is connected to lookup stage.

Ok I tried to write the output data to a file.
by applying string to timestamp conversion function in the transformer for a field with char datatype to timestamp datatype but when I see the data of the field that I want to see as timestamp it was ****** in the output file
the input data of that field is 20061205.

The whole thing is happening before it goes to lookup stage.

The conversion logic that I used in the transformer for the 'B' is
StringToTimestamp(DSLink77.B)

Thanks

kumar_s · Post by **kumar_s** » Tue Mar 06, 2007 9:45 pm

The function you used to convert is incomplete.
There is no time portion in the given value.
Before let you know that, could you give the value of the lookup data (key)?

laxmi_etl · Post by **laxmi_etl** » Tue Mar 06, 2007 9:48 pm

Lookup key values is 1970-01-01 00:00:00

kumar_s · Post by **kumar_s** » Tue Mar 06, 2007 9:54 pm

Try StringToTimestamp(DSLink77.B,"%YYYY%MM%DD") and let me know the output of the sequential file.

laxmi_etl · Post by **laxmi_etl** » Tue Mar 06, 2007 10:09 pm

When I use this function the data(only two records) is going upto transformer and after that nothing is going to output file.

laxmi_etl · Post by **laxmi_etl** » Tue Mar 06, 2007 10:12 pm

APT_CombinedOperatorController,0: Caught exception from runLocally(): APT_ParseError: Parsing parameters "%yyyy%mm%dd%" for conversion "timestamp=timestamp_from_ustring[%yyyy-%mm-%dd %hh:%nn:%ss](ustring)": APT_Conversion_String_TimeStamp: Invalid Format [%yyyy%mm%dd%] used for string_from_time type conversion.

The above errors are getting when we I use the function that was told by you.

Thanks

kumar_s · Post by **kumar_s** » Tue Mar 06, 2007 10:21 pm

Due to some reason, you were not able to give the full details, or perhaps you were not able to find related to what the is data type and value of data from Lookup as well as from main stream.
Let me do that work for you. Let me repharse the structre of your job as,
Input is sequential file, with the key as a char field with the value in the pattern of '20061205'. You are using a Transformer stage to convert this key to Timestamp field before reaching the Lookup stage.
And lookup is also sequential file, and the data avaialble is in '1970-01-01 00:00:00'. And it is read as Timestamp datatype.
You are doning lookup via a lookup stage using the key.

Pls correct if there is any wrong in the above assumption.

If ther is no, you can go with a way that the two data can be convert to a common datatype.

Since the lookup data's time portion is not populated, you can trunc that to Date as well.

laxmi_etl · Post by **laxmi_etl** » Wed Mar 07, 2007 9:20 am

Nevermind.

It is working I used the fallowing
StringToTimestamp(Trim(DSLink77.B):" 00:00:00","%yyyy%mm%dd %hh:%nn:%ss")

That is working good.

Anyhow thankyou somuch for your help

Thanks.

DSguru2B · Post by **DSguru2B** » Wed Mar 07, 2007 9:23 am

Great. Time to mark your post as "Resolved" now.