JET SQL - Subquery in IIF statement not working?

kez1304 · #1 (**permalink**) 01-13-12, 07:51

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?

r937 · #2 (**permalink**) 01-13-12, 08:02

SELECT IIF(1 = 1, '1', '2') FROM main

Sinndho · #3 (**permalink**) 01-13-12, 08:11

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.

kez1304 · #4 (**permalink**) 01-13-12, 08:42

Thanks for your super quick responses guys!

Quote:

Originally Posted by Sinndho

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.

Sinndho · #5 (**permalink**) 01-13-12, 09:08

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.

kez1304 · #6 (**permalink**) 01-13-12, 09:34

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.

Sinndho · #7 (**permalink**) 01-13-12, 09:48

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.

kez1304 · #8 (**permalink**) 01-13-12, 10:07

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.

kez1304 · #9 (**permalink**) 01-13-12, 10:20

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?