Timestamp Comparison

Dee · Post by **Dee** » Sun Dec 05, 2004 8:09 pm

Hi,
I am joining two tables in order to modify the data in one of the tables. For this I need to compare a timestamp field[yyyy-mm-dd 00:00:00.000] in these tables. Can you please let me know how to go about this. I did try using the ICONV expression but it returns a null value.
(ICONV(FIELD(DSLink2b.CONVER_DTTM_INIT,".",1),"D-YMD[4,2,2]")
=ICONV(FIELD(DSLink2a.CONVER_DTTM_INIT,".",1),"D-YMD[4,2,2]")) AND
(ICONV(FIELD(FIELD(DSLink2a.CONVER_DTTM_INIT," ",2),".",1),"MTS") =ICONV(FIELD(FIELD(DSLink2b.CONVER_DTTM_INIT," ",2),".",1),"MTS"))

Also, If a timestamp is a key field in 2 tables is it possible to join these tables with this key field.
Thank you,
Vani

davidnemirovsky · Post by **davidnemirovsky** » Sun Dec 05, 2004 8:30 pm

read below

davidnemirovsky · Post by **davidnemirovsky** » Sun Dec 05, 2004 8:31 pm

In regrads to your first question you should try using something like this for the date part of the timestamp:

Code: Select all

IConv(InputColumn[1,10],"D-YMD[4,2,2]")

And perhaps something like this for the time part:

Code: Select all

IConv(InputColumn[12,8],"MTS")

To answer your second question:
Of course you can join two tables with a timestamp field, but will this return the results you require? You might want to do a join on the date part of the timestamp to return the days that match.

chulett · Post by **chulett** » Sun Dec 05, 2004 8:38 pm

Are both of the timestamps in the format you posted? If so, then you can simply compare them without having to anything special.

It also seems that you've got your field delimiters backwards - you should be using a space for both the date portion and the time portion, but only take the time up to the period for the "MTS" conversion. Add the milliseconds back in when you are done. Something like that, anyways.

Oracle person, and Oracle don't do milliseconds.

As to joining on the two fields, the general answer would be "yes"... if they are in the same format or are the same datatype.

Dee · Post by **Dee** » Mon Dec 06, 2004 11:28 am

Hi,
Thanks for the response. When I join the timestamp field in the two tables, I get an error saying 'Invalid character value for cast specification'. The SQL Type is a timestamp field of length 23 and scale 3.
I am not sure why this is happening since the sqltype of input and output fields are the same. My data for this field is as follows: 2002-03-22 18:02:12.000. Is there any way to get around this.
Thank you

davidnemirovsky · Post by **davidnemirovsky** » Mon Dec 06, 2004 4:51 pm

Which database are you using? What is the exact SQL you are using to perform the join? Are you casting the date fields correctly?
Ie. for Oracle you might need:

Code: Select all

TO_CHAR(DateField, "YYYY-MM-DD HH24:MI:SS")

Dee · Post by **Dee** » Mon Dec 06, 2004 5:23 pm

Its an SQL server database. I am using DRS stages with only generated SQL. In the generated sql, the cast is TO_CHAR(FIELD, "YYYY-MM-DD HH24:MI:SS")I need to take fields from TABLE1 AND TABLE2 and input into TABLE1 based on certain conditions. One of the conditions is that the SETID, CUST_ID, CONVER_DTTM_INIT which are the keys should be equal. Whenever I join the timestamp fields, the output errors out giving 'Invalid cast specification error'. I changed the key structure of the one the tables such that the timestamp is not a key. Then I included
LEFT (DSLink38.CONVER_DTTM_INIT, 19) = Field (DSLink2d.CONVER_DTTM, ".", 1) in the constraint section. The map runs successfully but I get only one row of output as compared to two or more rows of input where the setid and cust_id are the same.
Do you know where I am going wrong.
Thank you

davidnemirovsky · Post by **davidnemirovsky** » Mon Dec 06, 2004 8:01 pm

Please post the 'Generated SQL' that is being generated.

LEFT (DSLink38.CONVER_DTTM_INIT, 19) = Field (DSLink2d.CONVER_DTTM, ".", 1)

I think you should do these comparisons in the join, but if that's giving you trouble I would make the constraint something like this:

Code: Select all

 IConv(DSLink38.CONVER_DTTM_INIT[1,10], "D-YMD[4,2,2]") = IConv(DSLink2d.CONVER_DTTM[1,10], "D-YMD[4,2,2]") AND IConv(DSLink38.CONVER_DTTM_INIT[" ", 2, 1], "MTS") = IConv(DSLink2d.CONVER_DTTM[" ",2,1], "MTS")

See how you go with that.

DEVESHASTHANA · Post by **DEVESHASTHANA** » Mon Dec 06, 2004 11:46 pm

hi,

What i think is that You are facing the problem just because different time format in the source and lookup table ,if that is the case then only use iconv and oconv ,otherwise if the format is same then u can pass the column directly,as datastage does not have any datatypes if the format of column in source and target is same then it will work.

regards,

Devesh

Dee · Post by **Dee** » Wed Dec 08, 2004 10:59 am

Hi,
When I sent one of the tables through a hash file and then did the join, I was able to join the timestamp fields without any errors. The output is also as desired.

Table1-Transformer-Table1
Table2-Transformer-Hash File-Table2

Then Table1 and Table 2 are joined.

I am not sure how it worked. Just wanted to post the solution. Is it got something to do with DRS stages?

Thank you.

Dee · Post by **Dee** » Wed Dec 08, 2004 11:02 am

Hi David,
The generated sql for table1 was
SELECT PS_CUST_CONVER_HDR.SETID,PS_CUST_CONVER_HDR.CUST_ID,TO_CHAR(PS_CUST_CONVER_HDR.CONVER_DTTM_INIT, 'YYYY-MM-DD HH24:MI:SS'),TO_CHAR(PS_CUST_CONVER_HDR.CONVER_DT, 'YYYY-MM-DD HH24:MI:SS'),PS_CUST_CONVER_HDR.CONVER_SUBJECT,PS_CUST_CONVER_HDR.CONVER_SUB_TOPIC,PS_CUST_CONVER_HDR.ROLENAME,PS_CUST_CONVER_HDR.CONVER_STATUS,PS_CUST_CONVER_HDR.DESCR,TO_CHAR(PS_CUST_CONVER_HDR.REVIEW_NEXT_DT, 'YYYY-MM-DD HH24:MI:SS'),PS_CUST_CONVER_HDR.LETTER_CD,PS_CUST_CONVER_HDR.DUN_LETTER_COMP,PS_CUST_CONVER_HDR.FOLLOW_UP_ACTION,PS_CUST_CONVER_HDR.FOLLOW_UP_COMP,TO_CHAR(PS_CUST_CONVER_HDR.FOLLOW_UP_DT, 'YYYY-MM-DD HH24:MI:SS'),PS_CUST_CONVER_HDR.SUP_REVIEW,PS_CUST_CONVER_HDR.CNTCT_SEQ_NUM,PS_CUST_CONVER_HDR.SUP_STATUS,PS_CUST_CONVER_HDR.KEYWORD1,PS_CUST_CONVER_HDR.KEYWORD2,PS_CUST_CONVER_HDR.KEYWORD3,TO_CHAR(PS_CUST_CONVER_HDR.PROMISE_DT, 'YYYY-MM-DD HH24:MI:SS'),PS_CUST_CONVER_HDR.PROMISE_AMT,PS_CUST_CONVER_HDR.PROMISE_CONFIDENCE,PS_CUST_CONVER_HDR.REVIEW_OPRID,PS_CUST_CONVER_HDR.REVIEW_COMP,PS_CUST_CONVER_HDR.FOLLOW_UP_OPRID,PS_CUST_CONVER_HDR.THREAD_COUNT1,PS_CUST_CONVER_HDR.ATTACH_EXIST,PS_CUST_CONVER_HDR.OPRID FROM #$to#.PS_CUST_CONVER_HDR

The generated sql for table2 is
SELECT PS_CUST_CONVER.SETID,PS_CUST_CONVER.CUST_ID,TO_CHAR(PS_CUST_CONVER.CONVER_DTTM_INIT, 'YYYY-MM-DD HH24:MI:SS'),TO_CHAR(PS_CUST_CONVER.CONVER_DT, 'YYYY-MM-DD HH24:MI:SS'),PS_CUST_CONVER.CONVER_SEQ_NUM,TO_CHAR(PS_CUST_CONVER.CONVER_DTTM, 'YYYY-MM-DD HH24:MI:SS'),TO_CHAR(PS_CUST_CONVER.REVIEW_NEXT_DT, 'YYYY-MM-DD HH24:MI:SS'),PS_CUST_CONVER.LETTER_CD,PS_CUST_CONVER.DUN_LETTER_COMP,PS_CUST_CONVER.FOLLOW_UP_ACTION,PS_CUST_CONVER.FOLLOW_UP_COMP,PS_CUST_CONVER.SUP_REVIEW,PS_CUST_CONVER.CNTCT_SEQ_NUM,PS_CUST_CONVER.CONTACT_ID,PS_CUST_CONVER.SUP_STATUS,PS_CUST_CONVER.DESCRLONG,PS_CUST_CONVER.KEYWORD1,PS_CUST_CONVER.KEYWORD2,PS_CUST_CONVER.KEYWORD3,PS_CUST_CONVER.OPRID,TO_CHAR(PS_CUST_CONVER.PROMISE_DT, 'YYYY-MM-DD HH24:MI:SS'),PS_CUST_CONVER.PROMISE_AMT,PS_CUST_CONVER.PROMISE_CONFIDENCE,PS_CUST_CONVER.REVIEW_OPRID,PS_CUST_CONVER.REVIEW_COMP,PS_CUST_CONVER.FOLLOW_UP_OPRID,PS_CUST_CONVER.CONVER_VISIBILITY FROM #$so#.PS_CUST_CONVER WHERE PS_CUST_CONVER.SETID=:1 AND PS_CUST_CONVER.CUST_ID=:2 AND PS_CUST_CONVER.CONVER_DTTM_INIT=TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS') AND PS_CUST_CONVER.CONVER_DT=TO_DATE(:4, 'DD-MON-YY')

Thank you,
Vani