code_junkie

SQL nvl equivalent - without if/case statements & isnull & coalesce

Nov 13th, 2011
95
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.77 KB | None | 0 0
  1. select nvl (purge_date,"SODIUFOSDIUFSDOIFUDSF") from id_rec where id=36581;
  2.  
  3.  
  4. (expression)
  5.  
  6. SODIUFOSDIUFSDOIFUDSF
  7.  
  8. 1 row(s) retrieved.
  9.  
  10. select isnull (purge_date,"SODIUFOSDIUFSDOIFUDSF") from id_rec where id=36581;
  11.  
  12. 674: Routine (isnull) can not be resolved.
  13. Error in line 1
  14. Near character position 8
  15.  
  16. select coalesce (purge_date,"SODIUFOSDIUFSDOIFUDSF") from id_rec where id=36581;
  17.  
  18. 674: Routine (coalesce) can not be resolved.
  19. Error in line 1
  20. Near character position 8
  21.  
  22. select decode(purge_date, NULL, "01/01/2009", purge_date) from id_rec where id=74115;
  23.  
  24. 800: Corresponding types must be compatible in CASE expression.
  25. Error in line 1
  26. Near character position 57
  27.  
  28. SELECT DECODE(purge_date, NULL, '01/01/2009'::DATE, purge_date)
  29.  
  30. DATE('01/01/2009')
  31. MDY(1,1,2009)
Add Comment
Please, Sign In to add comment