Results 1 to 13 of 13

Thread: IIf IsNull using SQL

  1. #1
    FreeriderUK is offline VB.NET Forum Fanatic
    .NET Framework
    .NET 3.5 (VS 2008)
    Join Date
    Jun 2006
    Location
    London
    Posts
    100
    Reputation
    107

    Unhappy IIf IsNull using SQL

    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:

    Code:
    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:
    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, '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.

  2. #2
    cjard's Avatar
    cjard is offline VB.NET Forum All-Mighty
    .NET Framework
    .NET 4.0
    Join Date
    Apr 2006
    Posts
    7,036
    Reputation
    1723
    Quote Originally Posted by FreeriderUK View Post

    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:

    Code:
    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

  3. #3
    cjard's Avatar
    cjard is offline VB.NET Forum All-Mighty
    .NET Framework
    .NET 4.0
    Join Date
    Apr 2006
    Posts
    7,036
    Reputation
    1723
    i.e. in SQLServer and Oracle, a field in the select list cannot refer to another field caluclated in that same select list

  4. #4
    FreeriderUK is offline VB.NET Forum Fanatic
    .NET Framework
    .NET 3.5 (VS 2008)
    Join Date
    Jun 2006
    Location
    London
    Posts
    100
    Reputation
    107
    Thanks cjard,

    worked a treat!

  5. #5
    FreeriderUK is offline VB.NET Forum Fanatic
    .NET Framework
    .NET 3.5 (VS 2008)
    Join Date
    Jun 2006
    Location
    London
    Posts
    100
    Reputation
    107
    One problem though. How to ORDER BY this calculated field?

  6. #6
    cjard's Avatar
    cjard is offline VB.NET Forum All-Mighty
    .NET Framework
    .NET 4.0
    Join Date
    Apr 2006
    Posts
    7,036
    Reputation
    1723
    ORDER BY is processed after the select list has been generated:

    Code:
    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

  7. #7
    FreeriderUK is offline VB.NET Forum Fanatic
    .NET Framework
    .NET 3.5 (VS 2008)
    Join Date
    Jun 2006
    Location
    London
    Posts
    100
    Reputation
    107

    Unhappy

    Quote Originally Posted by cjard View Post
    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:
    Quote Originally Posted by cjard View Post
    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!

  8. #8
    cjard's Avatar
    cjard is offline VB.NET Forum All-Mighty
    .NET Framework
    .NET 4.0
    Join Date
    Apr 2006
    Posts
    7,036
    Reputation
    1723
    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?

  9. #9
    FreeriderUK is offline VB.NET Forum Fanatic
    .NET Framework
    .NET 3.5 (VS 2008)
    Join Date
    Jun 2006
    Location
    London
    Posts
    100
    Reputation
    107
    Quote Originally Posted by cjard View Post
    ... 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.

  10. #10
    FreeriderUK is offline VB.NET Forum Fanatic
    .NET Framework
    .NET 3.5 (VS 2008)
    Join Date
    Jun 2006
    Location
    London
    Posts
    100
    Reputation
    107

    You're right of course.

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

  11. #11
    cjard's Avatar
    cjard is offline VB.NET Forum All-Mighty
    .NET Framework
    .NET 4.0
    Join Date
    Apr 2006
    Posts
    7,036
    Reputation
    1723
    Quote Originally Posted by FreeriderUK View Post
    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?

  12. #12
    FreeriderUK is offline VB.NET Forum Fanatic
    .NET Framework
    .NET 3.5 (VS 2008)
    Join Date
    Jun 2006
    Location
    London
    Posts
    100
    Reputation
    107
    Quote Originally Posted by cjard View Post
    ... 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"

  13. #13
    cjard's Avatar
    cjard is offline VB.NET Forum All-Mighty
    .NET Framework
    .NET 4.0
    Join Date
    Apr 2006
    Posts
    7,036
    Reputation
    1723
    tbh, i'd have done this when the data went IN, not when it comes out..

Bookmarks

Posting Permissions

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