• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

COALESCE Function in TIMESTAMP

 
PraveenKumar Kannankutty
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I have the following piece of code...

UPDATE TEST_TBL
SET
A_VALUE = ?,
B_VALUE = ?,
DATE_UPDATED = ?
WHERE ((coalesce(A_VALUE ,0) <> coalesce(?,0))
OR
(coalesce(B_VALUE ,0) <> coalesce(?,0))
OR
(coalesce(DATE_UPDATED,0) <> coalesce(?,0)))

The above query throws an error since DATE_UPDATED which is of type DATE and it cannot be assinged to '0'.
Can you please suggest what could be assigned?

NOTE: I have tried with Single quotes[' '], it also fails.


Thanks in Advance
Praveen.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Moving to the JDBC Forum.
 
Raj Chila
Ranch Hand
Posts: 128
Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
with what ever knowledge I have, I think Coalesce statements are used in the Query part of the statement (select) and not in the where part, even if you can, the LHS should resolve to be a Column Name and you cannot use a literal like like "where 10 = 0".

if you are also using Java then you could do most of the validations in the code itself before making the JDBC call

Originally posted by PraveenKumar Kannankutty:
WHERE ((coalesce(A_VALUE ,0) <> coalesce(?,0))
OR
(coalesce(B_VALUE ,0) <> coalesce(?,0))
OR
(coalesce(DATE_UPDATED,0) <> coalesce(?,0)))

[ October 25, 2005: Message edited by: RajaniKanth Bhargava ]
 
Scott Johnson
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by PraveenKumar Kannankutty:

Can you please suggest what could be assigned?



Instead of 0 try using a real date that wouldn't exist in your data like 1/1/1900, 1/1/2000, or 1/1/2500.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic