Microsoft Query and CASE statement

Jan Lorenz · External Since: Mar 07, 2006 Posts: 2

Hi,

it's possible to use a "CASE WHEN" - statement with Microsoft Query to
import data into Excel?

I'm getting an error with for example:

Select CASE Kurs WHEN 1 THEN 2 ELSE 3 END From Table

In the SQL Enterprise Manager it works.

Jan

Linn Kubler · External Since: Sep 07, 2005 Posts: 97

Jan, I just figured this out yesterday in fact. The syntax is:

select 'Field Header' = case when field = 'whatever' then 'response' when
field = 'something else' then 'another response' else 'something else' end
from myTable

Hope that helps,
Linn

"Jan Lorenz" <Knarzer77.TakeThisOut@gmx.de> wrote in message
news:OfGFlMgQGHA.5924@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> it's possible to use a "CASE WHEN" - statement with Microsoft Query to
> import data into Excel?
>
> I'm getting an error with for example:
>
> Select CASE Kurs WHEN 1 THEN 2 ELSE 3 END From Table
>
> In the SQL Enterprise Manager it works.
>
>
> Jan
>

Jan Lorenz · External Since: Mar 07, 2006 Posts: 2

OK, it works. It was a problem with the GROUP statement.

"Linn Kubler" <lkubler DeleteThis @chartwellwisc2.com> schrieb im Newsbeitrag
news:%23l$aLjhQGHA.1688@TK2MSFTNGP11.phx.gbl...
> Jan, I just figured this out yesterday in fact. The syntax is:
>
> select 'Field Header' = case when field = 'whatever' then 'response' when
> field = 'something else' then 'another response' else 'something else' end
> from myTable
>
> Hope that helps,
> Linn
>
> "Jan Lorenz" <Knarzer77 DeleteThis @gmx.de> wrote in message
> news:OfGFlMgQGHA.5924@TK2MSFTNGP09.phx.gbl...
>> Hi,
>>
>> it's possible to use a "CASE WHEN" - statement with Microsoft Query to
>> import data into Excel?
>>
>> I'm getting an error with for example:
>>
>> Select CASE Kurs WHEN 1 THEN 2 ELSE 3 END From Table
>>
>> In the SQL Enterprise Manager it works.
>>
>>
>> Jan
>>
>
>

User: inactive Posts:

I was absolutely thrilled when I saw this post. It was exactly what I was looking for to convert my IIF Access queries to CASE statements in MS Query. But, I cannot get it to work. When I use the syntax (as detailed in this post):
"SELECT 'FieldHeader' = CASE WHEN field = 'A' THEN 'B' ELSE 'C' END FROM table", I receive the following error message: "Didn't expect 'field' after the SELECT column list".
I've tried numerous other variations of the SELECT CASE statement that work in other programs but cannot get any of them to work. I'm guessing I'm missing punctuation somewhere??? I'm beyond frustrated ... any ideas?

pbbellri · Joined: Sep 24, 2008 Posts: 4 Location: Chicago, IL

The trick to use CASE statements in Excel is to include is both in the SELECT part as well as the GROUP BY part. Where as in the SELECT part you may assign it a field name (i.e. "Field Header"), for the GROUP BY you may not.

In the example below, the query did not work because it was missing the GROUP BY.

----- INCORRECT ----
SELECT 'FieldHeader' = CASE WHEN field = 'A' THEN 'B' ELSE 'C' END
FROM table

----- CORRECT -----
SELECT 'FieldHeader' = CASE WHEN field = 'A' THEN 'B' ELSE 'C' END
FROM table
GROUP BY CASE WHEN field = 'A' THEN 'B' ELSE 'C' END

---- ALTERNATIVE ----
SELECT CASE WHEN field = 'A' THEN 'B' ELSE 'C' END as 'FieldHeader'
FROM table
GROUP BY CASE WHEN field = 'A' THEN 'B' ELSE 'C' END

--Paul

junbug178 · Joined: Nov 13, 2008 Posts: 2

Correct way to do 'CASE WHEN' in 'MS Query':
select IIf(Column = 1, 0, 1)
from Table

Just tested it and it works...

pbbellri · Joined: Sep 24, 2008 Posts: 4 Location: Chicago, IL

IIF() works well for a small number of cases, but when you have multiple possible cases, then the CASE WHEN is the better option.

----- EXAMPLE with Multiple Cases-----
SELECT 'FieldHeader' = CASE WHEN field = 'A1' THEN 'B2' WHEN field = 'A2' THEN 'B2' WHEN field = 'A3' THEN 'B3' WHEN field = 'A4' THEN 'B4' ELSE 'C' END
FROM table
GROUP BY CASE WHEN field = 'A1' THEN 'B2' WHEN field = 'A2' THEN 'B2' WHEN field = 'A3' THEN 'B3' WHEN field = 'A4' THEN 'B4' ELSE 'C' END

Nesting IIF() statements may become cumbersome and may not work if you nest too many of them.

junbug178 · Joined: Nov 13, 2008 Posts: 2

I was actually answering the original question below on 'how to' do a 'case when' in 'MS Query' in Excel. 'CASE WHEN' doesn't seem to work in 'MS Query'.

pbbellri · Joined: Sep 24, 2008 Posts: 4 Location: Chicago, IL

CASE statements can be used in MS Query (Excel's data query application), but not always (it depends on the database table you are connecting to).

If you are connecting to a SQL Server database, then CASE statements work.

If you are connecting to a MS Access database, then CASE will not work. MS Access does not recognize CASE statements. MS Access uses a function called SWITCH() instead.

Things to look out for when using CASE or SWITCH in MS Query.
Using CASE in MS Query on a connection to a SQL Database Table:
When connecting to a SQL database through MS Query, you may find difficulty when applying a CASE statement for a field that you need to GROUP BY. In normal SQL, you can assign a name to the CASE in the SELECT, and then call that name in the GROUP BY... but in MS Query, they don't let you just call that new name, instead, you have to copy that whole CASE statement (excluding the naming part). If you are summing the CASE (or counting, etc.), then you don't include it in the GROUP BY.

Example 1:
SELECT 'Test'=CASE WHEN table.certainField='A' THEN 'B' ELSE 'C' END
FROM table
GROUP BY CASE WHEN table.certainField='A' THEN 'B' ELSE 'C' END

Or, you may also see it this way...

SELECT CASE WHEN table.certainField='A' THEN 'B' ELSE 'C' END As 'Test'
FROM table
GROUP BY CASE WHEN table.certainField='A' THEN 'B' ELSE 'C' END

Notice that everything except the newly assigned field name is included in the GROUP BY.

Example 2 (not grouping):
SELECT 'Test'=SUM(CASE WHEN table.certainField='A' THEN 1 ELSE 0 END
FROM table
GROUP BY CASE WHEN table.certainField='A' THEN 1 ELSE 0 END

Or, you may also see it this way...

SELECT CASE WHEN table.certainField='A' THEN 1 ELSE 0 END As 'Test'
FROM table
GROUP BY CASE WHEN table.certainField='A' THEN 1 ELSE 0 END

Note: Instead of "1", maybe you chose to use a field like "sales" or "quantity", because you want to sum up the sales when year = 2006... and maybe your field name would be "2006 Sales" instead of "Test".

Using SWITCH() in MS Query on a connection to a MS Access Database Table:
SWITCH() is very similar to IIF(), but it doesn't bother with all of that messy nesting. SWITCH() requires an even number of arguments and tests each logical statement linearly.

Example 1:
SELECT 'Test'=SWITCH(table.certainField='A', 'B')
GROUP BY SWITCH(table.certainField='A', 'B')

This example would result in 'B' whenever table.certainField = 'A', and it would return Null for everything else.

Example 2:
SELECT 'Test'=SWITCH(table.certainField='A', 'B', table.certainField<>'A', table.certainField)
GROUP BY SWITCH(table.certainField='A', 'B', table.certainField<>'A', table.certainField)

This example would result in 'B' whenever table.certainField = 'A', and it would return table.certainField for everything else. If you have more than one logical statement, you'd probably prefer to achieve your "ELSE" effect using table.certainField=table.certainField.

SWITCH(table.certainField='A', 'B', table.certainField=table.certainField, table.certainField)

So, the moral of the story, MS Query's language is dependant on the database it is connecting to. Very Happy