| Author |
Topic |
|
xcas08
Starting Member
13 Posts |
 Posted - 2008-03-17 : 21:58:57
|
| Within the following query, i have three date fields. Status Active date/time, Status Complete Date/Time and Commit Date/Time. Both status times usually have valid dates, however sometimes the commit time is null. when that happens i'd like to fill that field with the value from the Status Active Date/Time. How can i get this accomplished within the query? Thank you for your help.SELECT "Create Date/Time", "Account Affected", "Request Task ID", Status, "Status Active Date/Time", "Status Complete Date/Time", "Commit Date/Time", Location, "Request Type", "Task Name", Requester, "Exclude From Reporting", "Exclusion Reason", "Request Short Description" FROM OpasDatamart_DoED.dbo."Request Task" "Request Task" WHERE ("Account Affected"='Department of Education') AND (Status='Complete') AND ("Status Complete Date/Time">={ts '2008-01-01 00:00:00'}) |
|
|
tprupsis
Yak Posting Veteran
88 Posts |
Posted - 2008-03-17 : 22:18:18
|
| You can use the ISNULL function to do this. |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-17 : 23:11:33
|
| Something like this:ISNULL(Commit Date/Time,Status Active Date/Time) |
|
|
|
raky
Aged Yak Warrior
767 Posts |
Posted - 2008-03-18 : 01:39:51
|
| You can use COALESCE(Commit Date/Time,Status Active Date/Time) also. COALESCE is better to use than ISNULL |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-03-18 : 06:59:18
|
| use square braces across field names tooCOALESCE([Commit Date/Time],[Status Active Date/Time]) |
|
|
|
tprupsis
Yak Posting Veteran
88 Posts |
Posted - 2008-03-18 : 10:31:51
|
quote:
You can use COALESCE(Commit Date/Time,Status Active Date/Time) also. COALESCE is better to use than ISNULL
Why is COALESCE "better"? As I understand it, COALESCE can be considered to be "better" when you need to evaluate multiple arguments, or there are possible differences in datatypes. Neither of those is applicable in this scenario. Are there other conditions when COALESCE would be more appropriate than ISNULL? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-03-18 : 14:08:46
|
| COALESCE is ANSI compliant, if that matters. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-18 : 14:15:39
|
| "Why is COALESCE "better"? As I understand it, COALESCE can be considered to be "better" when you need to evaluate multiple arguments, or there are possible differences in datatypes. Neither of those is applicable in this scenario. Are there other conditions when COALESCE would be more appropriate than ISNULL?"You are 100% right. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-03-18 : 20:38:30
|
quote:
Originally posted by sodeep
"Why is COALESCE "better"? As I understand it, COALESCE can be considered to be "better" when you need to evaluate multiple arguments, or there are possible differences in datatypes. Neither of those is applicable in this scenario. Are there other conditions when COALESCE would be more appropriate than ISNULL?"You are 100% right.
COALESCE is ANSI SQL standard so any cross platform related work would benefit. |
|
|
|
|
Query question.