Excel - MS Query - CASE statement 1

[MarcLodge]

Hi All,
I am querying data from a number of spreadsheets into another spreadsheet. One of the columns which I wish to sort on contains the values 'Very Simple', 'Simple', 'Standard', 'Complex', 'Very Complex' and that if the order I wish them to be sorted in.

I thought that the easiest way to do this was to create another column with a CASE statement as follows:

SELECT `R13#0$`.`Component Type`, `R13#0$`.Complexity, `R13#0$`.`Level of Impact`,
CASE `R13#0$`.Complexity
WHEN 'Very Simple' THEN 1
WHEN 'Simple' THEN 2
WHEN 'Standard' THEN 3
WHEN 'Complex' THEN 4
WHEN 'Very Complex' THEN 5
ELSE 0
END
FROM `R13#0$` `R13#0$`

From an standard Ansi SQL point of view, I believe I have the syntax correct but MS Query is returning:

Didn't expect 'WHEN' after the SELECT column list

Anybody got any ideas as I've tried a number of different syntaxes and just can't get it to work.

Marc

 

[SkipVought]

Hi,

MS Query whatever syntax relates to the DATABASE it is querying. If it's querying Excel then use the IIF statement

Select...
, IIF([i]expression[/i],[i]TRUE result[/i],[i]FALSE result[/i])

Skip,

_[red][/red]

 

[xlbo]

I thought the syntax for Case was

CASE WHEN Field = Test1 THEN 1 WHEN Field = Test2 THEN 2 etc etc ... END

rather than

Case Field WHEN Test=1 THEN etc etc ...END

That's the syntax I use for SQL Server anyway...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MarcLodge]

Geoff,
Both versions of the CASE statement are acceptable in ANSI SQL.

Skip,
At times I like Microsoft, other times, I could happily kill them. There's no way on the planet I could have guessed that MS Query would allow IIF statements in SQL! It gets worse as I decided to test it out first in the spreadsheet to make sure I got the format right. IIF gave me errors so I changed it to IF which worked fine, so long as I used double quotes. When I put it back into MS Query I had to fiddle about with it again to get it to work properly as it didn't understand IF or the double quotes. Grrrrr!!!

For reference, if anybody is interested in weeks to come, this is what I coded:

SELECT `R13#0$`.`Component Type`, `R13#0$`.Complexity, `R13#0$`.`Level of Impact`, `R13#0$`.`Build Team`, `R13#0$`.Build, `R13#0$`.Actual, 
IIF(`R13#0$`.Complexity='Very Simple',1, 
IIF(`R13#0$`.Complexity='Simple',2, 
IIF(`R13#0$`.Complexity='Standard',3, 
IIF(`R13#0$`.Complexity='Complex',4, 
IIF(`R13#0$`.Complexity='Very Complex',5,0)))))
FROM `R13#0$` `R13#0$`

Thanks once again for your excellent help Geoff, Skip.

Marc

 

[xlbo]

nice one - didn't know that the other syntax was also acceptable

Star for Skip for that useful info on differing syntax for querying excel

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Yes, I query from Excel to

Excel

Access

Oracle

DB2

and each has their idiosyncrasies.


Skip,

_[red][/red]