Moving a Switch statement from Access to SQL 1

[scliffe]

Hi,

I've been trying to transfer the following statement in an Access query to a view in SQL and cannot figure out the best approach. I've investigated CASE but this fails whenever I use symbols like > but would be fine if I substituted in the CASE statement for a text entry. I'm sure its obvious to a more experienced programmer than I!

MS Access SQL Code
Switch([GSCore]>=7,"A",[GSCore]>=6.5,"A/B",[GSCore]>=6,"B/C",[GSCore]>=5.5,"C/D",[GSCore]>=5,"D",[GSCore]<5,"D/E",True,"N/A") AS TMG INTO tblTMG

SQL Server code that I could get to work
SELECT admission_no, SPoints, SUnits, GScore, CASE GScore WHEN '7' THEN GScore * 2 WHEN '6' THEN GScore * 50 ELSE GScore END AS TMG
FROM dbo.vw_EntryCalcs

Hope this is clear enough, if not I'll try to clarify.

Thanks

Simon

 

[Crowley16]

with the case statement in t-sql, if you move the column criteria test into the when clause, you can have more complicated processing, e.g.

CASE
WHEN ColumnName >= 7 THEN something
...
ELSE
END AS ...

--------------------
Procrastinate Now!

 

[scliffe]

Hi,

I've tried that (see below) already but that's why I've been having problems unless I'm overlooking something...

SELECT admission_no, SPoints, SUnits, GScore, CASE GScore WHEN >= 7 THEN 'A' WHEN < 7 THEN 'B' ELSE GScore END AS TMG
FROM dbo.vw_EntryCalcs2

Thanks

Simon

 

[Crowley16]

erm, not quite what I specified...

... CASE WHEN GScore >= 7 THEN 'A' ...

--------------------
Procrastinate Now!

 

[scliffe]

Ooops, I think I must be word dyslexic today! It works in terms of liking the SQL but when trying to view results, I'm getting an error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to float

 

[scliffe]

Ignore that last message... I've fixed my problem!

Thanks for the help!

 

[Crowley16]

you're comparing varchars?

be careful, because using the default collation, '17' < '7'

--------------------
Procrastinate Now!

 

[scliffe]

It was my "Else" statement that was causing the problem rather than the rest of the code (I was leaving the GScore as a number rather than converting it to "N/A" when I tested it). The process basically converts a number within a range to an equivalent letter (e.g. >7 maps to A, 6-7 maps to B ... <5 maps to E)

 

[Crowley16]

as long as the numbers doesn't get above 9 then it'll be fine

--------------------
Procrastinate Now!

 

[scliffe]

I'll be ok so long as I make sure the college don't change their "points per unit" system from the current one of 8 per unit so 8 is the highest.

 

[Crowley16]

why not just store it as a tinyint/smallint then you don't have to worry if your collegues change the system

--------------------
Procrastinate Now!

 

[scliffe]

Finally got back to putting all the options in... Feeling very beginner-like today!

SELECT admission_no, SPoints, SUnits, GScore, CASE WHEN GScore >= 7 THEN 'A' WHEN GScore < 7 AND >= 6.5 THEN 'A/B' ELSE 'N/A' WHEN GScore < 6.5 AND >= 6 THEN 'B/C' WHEN GScore < 6 AND >= 5.5 THEN 'C/D' WHEN GScore < 5.5 AND >= 5 THEN 'D' WHEN GScore < 5 THEN 'D/E' END AS TMG
FROM dbo.vw_EntryCalcs2

Unable to parse query text.

 

[gmmastros]

Your ELSE should go at the bottom and you need to specify the column for each conditional.

Wrong...
WHEN GScore < 7 AND >= 6.5 THEN ...

Right

WHEN GScore < 7 AND [!]GScore[/!] >= 6.5 THEN ...

Like this...

[COLOR=blue]SELECT[/color] admission_no, 
       SPoints, 
       SUnits, 
       GScore, 
       [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] GScore >= 7 [COLOR=blue]THEN[/color] [COLOR=red]'A'[/color] 
            [COLOR=blue]WHEN[/color] GScore < 7 AND GScore >= 6.5 [COLOR=blue]THEN[/color] [COLOR=red]'A/B'[/color] 
            [COLOR=blue]WHEN[/color] GScore < 6.5 AND GScore >= 6 [COLOR=blue]THEN[/color] [COLOR=red]'B/C'[/color] 
            [COLOR=blue]WHEN[/color] GScore < 6 AND GScore >= 5.5 [COLOR=blue]THEN[/color] [COLOR=red]'C/D'[/color] 
            [COLOR=blue]WHEN[/color] GScore < 5.5 AND GScore >= 5 [COLOR=blue]THEN[/color] [COLOR=red]'D'[/color] 
            [COLOR=blue]WHEN[/color] GScore < 5 [COLOR=blue]THEN[/color] [COLOR=red]'D/E'[/color] 
            [COLOR=blue]ELSE[/color] [COLOR=red]'N/A'[/color] 
            [COLOR=blue]END[/color] [COLOR=blue]AS[/color] TMG
[COLOR=blue]FROM[/color]   dbo.vw_EntryCalcs2

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[FlaBusInt]

Couldn't you simplify it a bit by just including the upper bound in each WHEN clause?

SELECT admission_no, 
       SPoints, 
       SUnits, 
       GScore, 
       CASE WHEN GScore >= 7 THEN 'A' 
            WHEN GScore >= 6.5 THEN 'A/B' 
            WHEN GScore >= 6 THEN 'B/C' 
            WHEN GScore >= 5.5 THEN 'C/D' 
            WHEN GScore >= 5 THEN 'D' 
            WHEN GScore < 5 THEN 'D/E' 
            ELSE 'N/A' 
            END AS TMG
FROM   dbo.vw_EntryCalcs2

 

[gmmastros]

Sure. It can be simplified. But where's the learning experience in that? Seriously, CASE is used a lot, so knowing how to properly deal with it is important.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[scliffe]

Cheers for all that. In my defence, I didn't originally have the ELSE statement in the middle (a senior moment) but I didn't spot the simplification (always one to simplify where possible but I didn't think it would follow the same simplification process as the Access Switch command.

Thanks again - I'm off to batter my head with a feather to minimize the senior moments ;-)