Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Unanswered: JET SQL - Subquery in IIF statement not working?

    Just a quickie (hopefully)...

    Can't understand why this doesn't work:

    Code:
    SELECT IIF(1 = 1, (SELECT '1' FROM main), '2') FROM main
    It just returns nothing...

    Obviously the purpose of using this setup is a little more involved, but as a simple demo, I can't understand why this won't work.

    Is this just the JET engine not being able to handle a relatively simple (and useful) feature of the SQL language?
    Looking for the perfect beer...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT IIF(1 = 1, '1', '2') FROM main
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It works on the condition that there is one (and only one) row in the table "Main".

    These will work whatever the number of rows Main can have, except zero (no rows):
    Code:
    SELECT IIf(1=1,(SELECT MAX('1') FROM main),'2') FROM main;
    or:
    Code:
    SELECT IIf(1=1,(SELECT TOP 1 '1' FROM main),'2') FROM main;
    etc.
    Have a nice day!

  4. #4
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Thanks for your super quick responses guys!

    Quote Originally Posted by Sinndho View Post
    It works on the condition that there is one (and only one) row in the table "Main".
    Sinndho, thanks for explaining and giving working examples... I managed to get my code working as follows:

    Code:
    strSQL = "SELECT main.[Product Code], " & _
                "IIF(ref_products.[Product vName] = 'BESPOKE', " & _
                    "(SELECT MAX(ref_products_BSPK.[BSPK Product vName]) " & _
                     "FROM ref_products_BSPK INNER JOIN main " & _
                         "ON main.[Product Code] = ref_products_BSPK.[Product Code] " & _
                     "WHERE ref_products_BSPK.[Product Code] = main.[Product Code]" & _
                    ")" & _
                ", ref_products.[Product vName]) " & _
             ", COUNT(1) AS [Quantity] " & _
             "FROM main LEFT JOIN ref_products " & _
                 "ON ref_products.[Product Code] = main.[Product Code] " & _
             "WHERE [Order Number] = " & Me.orderNum.Value & " " & _
             "AND main.[Delete] = FALSE " & _
             "GROUP BY main.[Product Code], ref_products.[Product vName] " & _
             "ORDER BY main.[Product Code]"
    I can't believe I spent hours trying to get this to work, and all that was missing was a MAX() statement.


    To improve my understanding of SQL (which is pretty lacking at the moment), can you explain why it's necessary to have a MAX statement?

    From my original simplified query of:

    Code:
    SELECT IIF(1 = 1, (SELECT '1' FROM main), '2') FROM main
    I'm unsure as to why it the JET engine cares how many records there are... If both:

    Code:
    SELECT '1' FROM main
    and

    Code:
    SELECT IIF(1 = 1, '1', '2') FROM main
    Work, why is MAX needed for the two of them to be put together?


    Thanks for your help in getting it working, but I would really appreciate a bit of explanation as to why this is an issue.
    Looking for the perfect beer...

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In the expression:
    Code:
    IIF(<Condition>, <ReturnValueWhenTrue>, <ReturnValueWhenFalse>)
    every parameter (all 3 ) must be either a scalar value or an expression that produces a scalar result.

    When passed to the Jet interpreter, the subquery:
    Code:
    SELECT '1' FROM main
    will return a set of values (each equal to '1'), one for every line in the Main table, which enters in conflict with the exigence of using scalar parameters with the IIF function.

    When you use a predicate such as TOP 1 or an aggregate function such as MAX or MIN, the set returned by the subquery can only have one row, which makes it compliant with the "scalar-only" parameters rule of the IIF function.

    On the contrary, any expression or subquery used with an empty table (zero row) will always return an empty set.
    Have a nice day!

  6. #6
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Okay, I follow what's going on now...

    However, after a little more testing I've discovered that the actual code I'm using:

    Code:
    strSQL = "SELECT main.[Product Code], " & _
                "IIF(ref_products.[Product vName] = 'BESPOKE', " & _
                   "(SELECT MAX(ref_products_BSPK.[BSPK Product vName]) " & _
                    "FROM ref_products_BSPK INNER JOIN main " & _
                       "ON main.[Product Code] = ref_products_BSPK.[Product Code] " & _
                    "WHERE ref_products_BSPK.[Product Code] = main.[Product Code]" & _
                   ")" & _
                ", ref_products.[Product vName]) AS [Product Description] " & _
             ", COUNT(1) AS [Quantity] " & _
             "FROM main LEFT JOIN ref_products " & _
                "ON ref_products.[Product Code] = main.[Product Code] " & _
             "WHERE [Order Number] = " & Me.orderNum.Value & " " & _
             "AND main.[Delete] = FALSE " & _
             "GROUP BY main.[Product Code], ref_products.[Product vName] " & _
             "ORDER BY main.[Product Code]"
    Is simply giving the same Product Description for every single record being displayed... This is because it's just finding the same match everytime...

    What I really need it to do, is in the subqueries WHERE statement; find the ref_products_BSPK.[Product Code] for the main.[Product Code] currently being evaluated in the outer query.

    Have you got any ideas on how to do that? I've tried giving it an alias, and referencing that in the subquery, but it just asks me for input for the alias when the code is run.

    Thanks again for your help.
    Looking for the perfect beer...

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Can you supply a sample of the tables (a csv in plain text inside a post is OK), or at least their definition (column names + data type)? This will spare time for both of us.
    Have a nice day!

  8. #8
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Boiling it down, as simply as possible...

    Table main
    Product Code [String]

    Table ref_products
    Product Code [String]
    Product vName [String]

    Table ref_products_BSPK
    Product Code [String]
    BSPK Product vName [String]


    I want to populate a 3 column listbox, with the following:

    Col 1 - Product Code, from the main table (when a particular Order Number is chosen, although this isn't important)

    Col 2 - Product vName from the ref_products table whose Product Code is equal to the Product Code from the main table, or, if on inspection the value found for the Product vName is the [String] 'BESPOKE', then the same check should be done on the ref_products_BSPK table, and the BSPK Product vName value that matches the given Product Code should be returned instead.

    Col 3 - Count the number of records that contain the same Product Code in the main table.

    Returned results should be grouped together by Product Code.


    I think I need to use a correlated subquery in a select statement, but I can't quite formulate it correctly.
    Looking for the perfect beer...

  9. #9
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Typical, I solved it minutes after posting all those details...

    I was using a join in the subquery, when I should've simply referenced the main table (without joining) in the WHERE clause...

    Below is the correct code:

    Code:
    strSQL = "SELECT main.[Product Code] AS test, " & _
                "IIF(ref_products.[Product vName] = 'BESPOKE', " & _
                   "(SELECT MAX(ref_products_BSPK.[BSPK Product vName]) " & _
                    "FROM ref_products_BSPK " & _
                    "WHERE ref_products_BSPK.[Product Code] = main.[Product Code]" & _
                   ")" & _
                ", ref_products.[Product vName]) AS [Product Description] " & _
             ", COUNT(1) AS [Quantity] " & _
             "FROM main LEFT JOIN ref_products " & _
                "ON ref_products.[Product Code] = main.[Product Code] " & _
             "WHERE [Order Number] = " & Me.orderNum.Value & " " & _
             "AND main.[Delete] = FALSE " & _
             "GROUP BY main.[Product Code], ref_products.[Product vName] " & _
             "ORDER BY main.[Product Code]"

    One thing while I have your attention though; I tried doing the following:

    Code:
    strSQL = "SELECT main.[Product Code] AS test, " & _
                "IIF(ref_products.[Product vName] = 'BESPOKE', " & _
                   "(SELECT MAX(ref_products_BSPK.[BSPK Product vName]) " & _
                    "FROM ref_products_BSPK AS a " & _
                    "WHERE a.[Product Code] = b.[Product Code]" & _
                   ")" & _
                ", ref_products.[Product vName]) AS [Product Description] " & _
             ", COUNT(1) AS [Quantity] " & _
             "FROM main AS b LEFT JOIN ref_products " & _
                "ON ref_products.[Product Code] = main.[Product Code] " & _
             "WHERE [Order Number] = " & Me.orderNum.Value & " " & _
             "AND main.[Delete] = FALSE " & _
             "GROUP BY main.[Product Code], ref_products.[Product vName] " & _
             "ORDER BY main.[Product Code]"
    But this doesn't work at all... Why am I unable to alias things in a subquery like that?
    Looking for the perfect beer...

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I came to the same conclusion just a few seconds ago
    Have a nice day!

  11. #11
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Ahh well, live and learn, thanks a lot for all your help.
    Looking for the perfect beer...

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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