Topic
  • 6 replies
  • Latest Post - ‏2005-09-27T15:41:39Z by SystemAdmin
SystemAdmin
SystemAdmin
2105 Posts

Pinned topic Timestamp and null value

‏2005-09-26T14:11:55Z |
Hi,
I am trying to migrate data from one database to another from same server(Sybase). Both the source and destinations in the DS are declared as Timestamp. In the database both the columns are declared as datetime.

I am stuck on the timestamp column. If the colum values are null it is being transformed to '*******************' !! I have tried IsNull in the transformation but it does not work. I MUST accept a null value (not a hypothetical date such as 01-01-0001). If I accept the hypothetical value it works but that will break logics in many application where we check for null (as oppose to a fake date).

Does work:
If IsNull(source.column) Then "01-01-0001 00:00:00.000" Else source.column

Does not work but I expect:
If IsNull(source.column) or source.column ="" Then "NULL" Else source.column

I hope IBM made this null transformation much easier. Interestingly, null transformation works fine on string column type.

Thanks,
Prodip
Updated on 2005-09-27T15:41:39Z at 2005-09-27T15:41:39Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    2105 Posts

    Re: Timestamp and null value

    ‏2005-09-26T15:34:34Z  
    Does the target DB allow nulls in a TimeStamp? Most of the time (pun not intended) Timestamps are not nulls.
  • SystemAdmin
    SystemAdmin
    2105 Posts

    Re: Timestamp and null value

    ‏2005-09-26T17:51:35Z  
    Does the target DB allow nulls in a TimeStamp? Most of the time (pun not intended) Timestamps are not nulls.[/quote:ed7e3be92d]

    Datetime columns in Sybase should allow Nulls. Are you sure that a database NULL value is being passed and not the LITERAL null? That would definately explain why it works on char/varchar columns.

    Brett
  • SystemAdmin
    SystemAdmin
    2105 Posts

    Re: Timestamp and null value

    ‏2005-09-27T10:36:47Z  
    First, thanks for the reply.

    To answer jsmoley's question- Yes, the target column does allow null. Currently a field called UPDATE_TIMESTAMP is populated with null (it's database null and it's not a string null) values.

    Brett:
    Let me take a step back. I misinformed you all. I was never been able to catch null (BDNull) values in the transformation stage. In the transformation stage the timestamp column value appeared as ******(all bright stars)!

    Explaining the Does work:
    For the sake of testing, I changed my input SQL to force the null values to a default (again, not my acceptable solution) like SELECT ISNULL(UPDATE_TIMESTAMP, '1900-01-01 12:00:00.001') UPDATE_TIMESTAMP FROM.....This worked. The problem lies in the input stage. If I did not force the null to a default, the null values appear as asterisks when I view the data on this stage. That's why I can never intercept nulls in the transformation stage.

    I don't want to force the nulls to a default in the input sql (that would be unmanageable). The input stage should be able to pull the null values (on the timestamp column) as NULL and I should not have to do any additional check in the transformation stage because my target allows null.

    I will appreciate your help.

    Thanks,
    Prodip
  • SystemAdmin
    SystemAdmin
    2105 Posts

    Re: Timestamp and null value

    ‏2005-09-27T12:31:11Z  
    Sorry, I just looked closer at your original post.

    Have you tried this:

    If IsNull(source.column) or source.column ="" Then @null Else source.column

    It still looks like, from your example, you're passing the literal word NULL and not an actual null.
    Brett
  • SystemAdmin
    SystemAdmin
    2105 Posts

    Re: Timestamp and null value

    ‏2005-09-27T15:24:59Z  
    Brett,
    I got it to work BUT it will slow down the performance. I could not use @Null in the transformation stage because it's probably not recognized in the transformation stage. I used the following to transform UPDATE_TIMESTAMP-

    If IsValid('Timestamp',source.UPDATE_TIMESTAMP) Then source.UPDATE_TIMESTAMP Else If Len(Trim(source.UPDATE_TIMESTAMP,"*","A")) = 0 Then SetNull() Else source.UPDATE_TIMESTAMP

    The control flow assumes:
    Most of the records will have valid timestamp value.
    Invalid value contains only asterisk character in it.
    If none satisfied, accept the original source value. This can raise error but would like to know about them.

    I still think there has to be a better alternative than my workaround above. It may be the database driver that is forcing null value on timestamp column to look like asterisks (*).

    I will wait for the right answers because the performance matters.

    Thanks,
    Prodip
  • SystemAdmin
    SystemAdmin
    2105 Posts

    Re: Timestamp and null value

    ‏2005-09-27T15:41:39Z  
    Brett,
    I got it to work BUT it will slow down the performance. I could not use @Null in the transformation stage because it's probably not recognized in the transformation stage. I used the following to transform UPDATE_TIMESTAMP-

    If IsValid('Timestamp',source.UPDATE_TIMESTAMP) Then source.UPDATE_TIMESTAMP Else If Len(Trim(source.UPDATE_TIMESTAMP,"*","A")) = 0 Then SetNull() Else source.UPDATE_TIMESTAMP

    The control flow assumes:
    Most of the records will have valid timestamp value.
    Invalid value contains only asterisk character in it.
    If none satisfied, accept the original source value. This can raise error but would like to know about them.

    I still think there has to be a better alternative than my workaround above. It may be the database driver that is forcing null value on timestamp column to look like asterisks (*).

    I will wait for the right answers because the performance matters.

    Thanks,
    Prodip[/quote:22da44b878]

    So you're assuming and you didn't test it? @NULL certainly does work in a transform--its a system variable.