MS database query: Statement CASE WHEN THEN ELSE END
 

Hi all,

I want to create in Excel (via data/import new external data/new
database query) a query from MS Access database using statement case.
MS query still complains. I tried something like this example (using
Northwind sample database):

SELECT employees.EmployeeID, employees.LastName,
case
when employees.country = 'USA' then 1
else 0
end
FROM Nwind.employees employees


What is wrong? The Microsoft query still complains that it didn't
expect 'employees' after the select column list.

I have also tried several variant like "... code = case ...." or ....
end as 'code'..." but no improvement.


thank you very much for any help.

riso

MS database query: Statement CASE WHEN THEN ELSE END
 

It fails because your code is not a valid SQL statement.
These are valid SQL statements:

SELECT employees.EmployeeID, employees.LastName, employees.country
FROM employees WHERE employees.country = 'USA';

SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName,
IIf([Employees]![Country]='USA',1,0) AS Country
FROM Employees;

The latter works in Access and as an SQL statement using ADO or DAO,
but I don't know if it will from Excel in the way you are trying.

Hth,
Merjet

MS database query: Statement CASE WHEN THEN ELSE END
 

Thank you very much.

I have found that the IIF() work fine.

One thing, I am not able to name columns as I need. Without iif I can
use arbitrary names, but once I use function iif the part of the sql
command "... AS country ..." dissapeared and excel use funny names
expr1001, expr1002 and so on. How to fix it?

I am going to ask this question in separate post.

anyway thank you very much.

riso

On Mar 5, 10:12 pm, "merjet" wrote:
It fails because your code is not a valid SQL statement.
These are valid SQL statements:

SELECT employees.EmployeeID, employees.LastName, employees.country
FROM employees WHERE employees.country = 'USA';

SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName,
IIf([Employees]![Country]='USA',1,0) AS Country
FROM Employees;

The latter works in Access and as an SQL statement using ADO or DAO,
but I don't know if it will from Excel in the way you are trying.

Hth,
Merjet