IIf IsNull using SQL

[FreeriderUK]

Previously I have been using MSACCESS databases, but have now changed to SQL.

I was able to use the following to calculate a Due_Date based on the Last_Done date:

SELECT 
Manual_Due, Last_Done, 
Last_Done + Freq * 7 AS Calculated_Due, 
CVDate(IIf(IsNull(Manual_Due), Calculated_Due, Manual_Due)) AS Due_Date 
FROM Customers

How can the same thing be acheived in SQL?
The best I can come up with is:

SELECT Frequency, Period, LastDone, 
CASE Period 
WHEN '1' THEN dateadd(dd, Frequency, LastDone) 
WHEN '2' THEN dateadd(wk, Frequency, LastDone) 
WHEN '3' THEN dateadd(mm, Frequency, LastDone) 
WHEN '4' THEN dateadd(yy, Frequency, LastDone) 
END AS CalculatedDueDate, ManualDueDate,
ISNULL(ManualDueDate, 'CalculatedDueDate') AS ScheduledDueDate
FROM Customers

The problem is, JET allowed the IIF to use a calculated field, but SQL EXPRESS doesn't seem to like it.
I know 'CalculatedDueDate' is a string - if I try using CalculatedDueDate, I get an 'invalid column name' error.

 

[cjard]

The problem is, JET allowed the IIF to use a calculated field, but SQL EXPRESS doesn't seem to like it.
I know 'CalculatedDueDate' is a string - if I try using CalculatedDueDate, I get an 'invalid column name' error.

The field hasnt been calculated yet.. Either wrap the SQL in a nest or repeat the case when:

SELECT
  ISNULL(Manual, Calced)
FROM
(
   SELECT Manual, CASE WHEN ... AS Calced ...
)


or


SELECT Frequency, Period, LastDone, 
CASE Period 
WHEN '1' THEN dateadd(dd, Frequency, LastDone) 
WHEN '2' THEN dateadd(wk, Frequency, LastDone) 
WHEN '3' THEN dateadd(mm, Frequency, LastDone) 
WHEN '4' THEN dateadd(yy, Frequency, LastDone) 
END AS CalculatedDueDate, ManualDueDate,
ISNULL(ManualDueDate, CASE Period 
WHEN '1' THEN dateadd(dd, Frequency, LastDone) 
WHEN '2' THEN dateadd(wk, Frequency, LastDone) 
WHEN '3' THEN dateadd(mm, Frequency, LastDone) 
WHEN '4' THEN dateadd(yy, Frequency, LastDone) 
END) AS ScheduledDueDate
FROM Customers

 

[cjard]

i.e. in SQLServer and Oracle, a field in the select list cannot refer to another field caluclated in that same select list

 

[FreeriderUK]

Thanks cjard,

worked a treat!

 

[FreeriderUK]

One problem though. How to ORDER BY this calculated field?

 

[cjard]

ORDER BY is processed after the select list has been generated:

SELECT
  1 + x as x_plus_one
  count(*)
FROM
  table
GROUP BY
  1 + x
ORDER BY
  x_plus_one

i.e. by the time order by is done, the value has been calculated. Use the name you called the column

 

[FreeriderUK]

ORDER BY is processed after the select list has been generated ... Use the name you called the column

In that case the SELECT isn't working as well as I thought.
I'm using this code:

SELECT Frequency, Period, LastDone,
CASE Period
WHEN '1' THEN dateadd(dd, Frequency, LastDone)
WHEN '2' THEN dateadd(wk, Frequency, LastDone)
WHEN '3' THEN dateadd(mm, Frequency, LastDone)
WHEN '4' THEN dateadd(yy, Frequency, LastDone)
END AS CalculatedDueDate, ManualDueDate,
ISNULL(ManualDueDate, CASE Period
WHEN '1' THEN dateadd(dd, Frequency, LastDone)
WHEN '2' THEN dateadd(wk, Frequency, LastDone)
WHEN '3' THEN dateadd(mm, Frequency, LastDone)
WHEN '4' THEN dateadd(yy, Frequency, LastDone)
END) AS ScheduledDueDate
FROM Customers
[/code]

CalculatedDueDate is created ok as datetime, but ScheduledDueDate gets created as System.String.
I know this is because ManualDueDate is a string and the Alias takes on the datatype of the first value in ISNULL, but this field sometimes needs to be NULL.

Now I'm really stuck!

 

[cjard]

OK, stop coding, pick up pencil and paper instead and think about what you want out of your query; is it actually optimal to produce those results anyway?

 

[FreeriderUK]

... is it actually optimal to produce those results anyway?

Yes, this is exactly what I need. I managed it fine with Access database. I thought SQL was better. Seems not.

 

[FreeriderUK]

You're right of course.

I don't NEED to do this stuff in the SELECT statement...

 

[cjard]

Yes, this is exactly what I need. I managed it fine with Access database. I thought SQL was better. Seems not.

Erm, hitting one bump doesnt mean that Access is better than SQLServer.. I could say that all Microsoft's database systems are s&!% compared to Oracle (especially for dates), because.. well.. they are..

However, trust me; SQLS is a better, more ANSI compliant system than Access, and the fact that it doesnt support some dodgy, quirky query that you managed to hammer togetehr in Access, is probably a good thing

Ends up, for reasons not very clear in your post, you want to have a date that is null sometimes and not others, and is called X when it might be Y, but only if X was null.. umm, it all sounds like a bit of a bodge! Hence me saying - is this really the best way to do this bit of your app?

 

[FreeriderUK]

... it all sounds like a bit of a bodge!

It was indeed.

But wasn't as complicated as you make it out to be!

All I was trying to do was:
If the user entered a ManualDate (hence, sometimes NULL), then use this as the ScheduledDate, otherwise use the CalculatedDate (LastDoneDate + (Freq * Period)).
For some reason I thought it would be best to do it in the SELECT statement.
Far easier to do it in the application.

Thanks for your "help"

 

[cjard]

tbh, i'd have done this when the data went IN, not when it comes out..