lookup stage

[shilpa79]

I would like to change the date from this format
2007-06-18 01:00:07.000 (from SQL server database)
to
5/4/2007 1:09:28 PM (source xml file)

Target is SQL server table

I am using lookup stage and in the Lkp stage conditions I have given as condition not met : continue
lkp failure: continue
when i doing lkp for this coulmn form the xml and table its loading as 0 in the target table

There is a ID column which should be populated in the second table ........

I tried to use date functions but didnot get exactly how to do that .............

[ArndW]

I am not quite sure of what you are doing, so I cannot answer directly. The lookup stage needs to match the source and target columns in order to work. In you case, what is the data type and format of the main data stream and what is the data type and format for the reference. Is the reference the XML file or SQL table?

[ray.wurlod]

Lookup stage only performs exact matches. You need to effect appropriate transformations upstream or downstream of the Lookup stage.

[shilpa79]

I am not quite sure of what you are doing, so I cannot answer directly. The lookup stage needs to match the source and target columns in order to work. In you case, what is the data type and format of the main data stream and what is the data type and format for the reference. Is the reference the XML file or SQL table?

My reference is sql server table and source is xml stage
I am trying to convert the date but I am not able to ..............

Do the conditions are correct or I need to change them in lkp stage

[ray.wurlod]

You CANNOT change them in Lookup stage. Lookup stage does lookups.

You must change upstream of Lookup stage, probably using Modify stage or Copy stage.

[ArndW]

As Ray has posted twice and I once in this thread, you need to match like-for-like. Your stream and reference lookup columns must be of the same datatype and/or format in order for the stage to work. Hence the questions asked above regarding your definitions.

[shilpa79]

You CANNOT change them in Lookup stage. Lookup stage does lookups.

You must change upstream of Lookup stage, probably using Modify stage or Copy stage. ...

I never used modify stage . I will try to chnage the format from this stage and get back to you if I have any questions

[shilpa79]

You CANNOT change them in Lookup stage. Lookup stage does lookups.

You must change upstream of Lookup stage, probably using Modify stage or Copy stage. ...

I never used modify stage . I will try to change the format from this stage and get back to you if I have any questions
Does this stage convert only the sql types or even the format also
like from
2007-06-18 01:00:07.000 (from SQL server database)
to
5/4/2007 1:09:28 PM (source xml file)

[ray.wurlod]

If you are converting to string it can convert format also.

[shilpa79]

If you are converting to string it can convert format also. ...

I donot have the problem with the SQL type .Both are varchar
Where can I use the functions and change the format
How to change the dates
from
2007-06-18 01:00:07.000 (from SQL server database)
to
5/4/2007 1:09:28 PM (source xml file) using modify stage

[ray.wurlod]

ALL of the date conversion functions allow you to specify the date format string. You can convert one format from a string to a date, and convert that date to a differently formatted string. In a Transformer stage your most likely candidates are TimestampToString() and StringToTimestamp() functions.

[rameshrr3]

Alternatively , if you are using a modify stage and if your input column ( from SQL server) is defined as timestamp; you can define the following modify stage specification( stage-Properties-Options, -> specification) :

Code: Select all

output_col:string[20] = string_from_timestamp["%mm/%dd/%yyyy %hh:%nn:%ss"](input_col)

OR

Code: Select all

output_col:string[20] = string_from_timestamp["%dd/%mm/%yyyy %hh:%nn:%ss"](input_col)

depending on where you want the month field and date field located in your output column data

output_col should be VarChar(20)

Thanks
Ramesh

[shilpa79]

Alternatively , if you are using a modify stage and if your input column ( from SQL server) is defined as timestamp; you can define the following modify stage specification( stage-Properties-Options, -> specification) :

Code: Select all

output_col:string[20] = string_from_timestamp["%mm/%dd/%yyyy %hh:%nn:%ss"](input_col)

OR

Code: Select all

output_col:string[20] = string_from_timestamp["%dd/%mm/%yyyy %hh:%nn:%ss"](input_col)

depending on where you want the month field and date field located in your output column data

output_col should be VarChar(20)

Thanks
Ramesh

I am getting this error:

main_program: Error parsing modify adapter: Expected ';' or ','; got: "CreationTimestamp"; input:
KEEP ReceivingDocumentID CreationTimestamp
;
conv_CreationTimestamp:string[23]=string_from_timestamp["%yyyy/%mm/%dd %hh:%nn:%ss"](CreationTimestamp)
;

I am selecting the two columns from the table and adding one more column conv_creationtimestamp in the modify stage input and putput properties for which I am doing the conversion.
I am not sure I am doing in a right way or not?

[ArndW]

You need to separate the column names with a comma in the KEEP statement in a modify stage. Your line needs to read "KEEP ReceivingDocumentID, CreationTimestamp"

[velayuthan]

DS8 very sensitive on the data types(Sql Types) of the column . before doing any lookup make sure the format of both the source and target
Sql Types are of same time. Even a small mis match would not result in proper lookup.


Solution : Use Modify stage -

functions - handling null values & changing data types .

will solve your problem