Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    8

    Unanswered: Using a timestamp in a case statement

    Hi All

    I'm using a select in a case statement in a trigger where there may not be any results. The problem is I don't what DB2 is expecting the comparison operand (where I have put a ? mark) to be. I have tried quotes, numbers, is null, timestamp(..) etc. to no avail.

    case
    when (select time_stamp
    from table
    where n.D_EMPLOYEE= employee) <> ?
    then (select time_stamp
    from table
    where n.D_EMPLOYEE= employee)
    else current timestamp
    end

    Most times a timestamp value will be found in the transaction file table - if the employee in the trigger after image is found use that timestamp, otherwise use the current timestamp.

    Any response would be appreciated.

    Thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Using a timestamp in a case statement

    Originally posted by johnkippers

    case
    when (select time_stamp
    from table
    where n.D_EMPLOYEE= employee) <> ?
    then (select time_stamp
    from table
    where n.D_EMPLOYEE= employee)
    else current timestamp
    end


    I think you can try using COALESCE instead of CASE, like this:
    Code:
    coalesce((select time_stamp....),current timestamp)
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Re: Using a timestamp in a case statement

    Why don't you use coalesce instead:

    coalsece((select timestamp ...),current timestamp)

    HTH

    Andy

    Originally posted by johnkippers
    Hi All

    I'm using a select in a case statement in a trigger where there may not be any results. The problem is I don't what DB2 is expecting the comparison operand (where I have put a ? mark) to be. I have tried quotes, numbers, is null, timestamp(..) etc. to no avail.

    case
    when (select time_stamp
    from table
    where n.D_EMPLOYEE= employee) <> ?
    then (select time_stamp
    from table
    where n.D_EMPLOYEE= employee)
    else current timestamp
    end

    Most times a timestamp value will be found in the transaction file table - if the employee in the trigger after image is found use that timestamp, otherwise use the current timestamp.

    Any response would be appreciated.

    Thanks.

  4. #4
    Join Date
    Jan 2003
    Posts
    8
    Thanks! I didn't know about coalesce - but now I do!

    Regards.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •