Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    16

    Unanswered: Conditional Expression - i.e., IIF in Access

    I have a query with a conditional expression that I can do just fine in Access but I am having a bear of a time trying to create a similar SQL View. Baiscally I want to say, if column A is null, use value B else use value C.

    In Access the SQL is this:

    SELECT IIf([Categorycode] Is Null,[tblconstituents].[CASNumber],[categorycode]) AS Casnumber, Sum(qryweldingrod3a.CFume) AS CFume, Sum(qryweldingrod3a.cslag) AS cSlag
    FROM qryweldingrod3a INNER JOIN tblconstituents ON qryweldingrod3a.CASNumber = tblconstituents.CASNumber
    GROUP BY IIf([Categorycode] Is Null,[tblconstituents].[CASNumber],[categorycode]);


    But I know you can't use the IIF statement in SQL so I was trying CASE and was still coming up empty handed. Here is what I produced in SQL but it didn't work:

    SELECT SUM(dbo.RecycleWR_qryWeldingRod3a_LBS.CFume) AS CFume, SUM(dbo.RecycleWR_qryWeldingRod3a_LBS.CSlag) AS cSlag,

    CASNumber = CASE Type
    WHEN categoryCode IS NULL THEN dbo.tblConstituents.CASNumber ELSE CategoryCode
    END,
    FROM dbo.tblConstituents INNER JOIN
    dbo.RecycleWR_qryWeldingRod3a_LBS ON dbo.tblConstituents.CASNumber = dbo.RecycleWR_qryWeldingRod3a_LBS.CASNumber
    GROUP BY dbo.RecycleWR_qryWeldingRod3a_LBS.CASNumber

    Any ideas would be greatly appreciated.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT CASE WHEN ColA IS NULL THEN ColB ELSE ColC END
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2003
    Posts
    16
    Originally posted by Brett Kaiser
    SELECT CASE WHEN ColA IS NULL THEN ColB ELSE ColC END
    Well, when I do that, I get
    "The Query Designer does not support the CASE SQL construct."

    Can you even use CASE in a view?

  4. #4
    Join Date
    Oct 2003
    Posts
    16
    Also, I need to assign an alias to that column.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What are you using?

    Aren't you using query analyzer?

    If you're using Access you may need to make it a PASS THRU query

    SELECT CASE WHEN ColA IS NULL THEN ColB ELSE ColC END AS NewCol
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Oct 2003
    Posts
    16
    I was creating the query in VIEW but I got around it using a function. Took me awhile but its working fine now. Thanks for your help

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What do you mean in VIEW?

    Are you doing this in Enterprise Manager?

    I would recommend against that.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Mar 2002
    Location
    Bielefeld, Germany
    Posts
    69
    Wether you use the designer in access or in EM, you'll loose the graphical representation of your query when you use CASE (and a bunch of other constructs). This is what the error message says. The query should run fine, anyway and you should see and be able to modify the sql source in access.
    However, beware of the designer, especially if you have complex where clauses. All sorts of weird things may happen to your sql

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What s/he said...

    Use QA though for SQL Server development....

    You'll have a lot less headaches....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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