lookup stage
Moderators: chulett, rschirm, roy
lookup stage
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 .............
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 .............
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?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
My reference is sql server table and source is xml stageArndW wrote: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?
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
I never used modify stage . I will try to change the format from this stage and get back to you if I have any questionsray.wurlod wrote: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. ...
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)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I donot have the problem with the SQL type .Both are varcharray.wurlod wrote:If you are converting to string it can convert format also. ...
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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) :
OR
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
Code: Select all
output_col:string[20] = string_from_timestamp["%mm/%dd/%yyyy %hh:%nn:%ss"](input_col)
Code: Select all
output_col:string[20] = string_from_timestamp["%dd/%mm/%yyyy %hh:%nn:%ss"](input_col)
output_col should be VarChar(20)
Thanks
Ramesh
I am getting this error:rameshrr3 wrote: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) :
ORCode: Select all
output_col:string[20] = string_from_timestamp["%mm/%dd/%yyyy %hh:%nn:%ss"](input_col)
depending on where you want the month field and date field located in your output column dataCode: Select all
output_col:string[20] = string_from_timestamp["%dd/%mm/%yyyy %hh:%nn:%ss"](input_col)
output_col should be VarChar(20)
Thanks
Ramesh
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?
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"
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 10
- Joined: Fri Jul 13, 2007 4:01 am
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
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