Pls help me with this SQL CASE WHEN (on ASP)

Hi and thanx for reading my post..

Well.. my problem is that i cannot seem to get the CASE syntax correct within my ASP page. I'm not sure where to break the lines and all that.

I have searched the net for quiiite a while before posting this :)

Will be VERY glad if someone can help me to put the puzzle in the right ASP-way :)

-----START-----
strSQL = "SELECT enavn,fnavn,fodselsnr,fangenr,convert(char(10),reg _date,104) " &_

"'status'= CASE when legal = '1' then "beginner" when legal = '2' then "intermediate" when legal = '3' then "professional" when kan_skal = 'kan' then "darlig foto" else 'status ikke angitt' END " &_

"FROM BILDE WHERE accepted LIKE " & "'1'" & " AND marked LIKE " & "'0'" & " AND pkode LIKE " & strDistrikt & " AND kan_skal LIKE " & "'kan'" & " AND arstall LIKE " & strArstall
-----END------

What i want to do is getting the three values from the SQL DB which are in the "legal" field (1, 2 or 3), and based on what it is display "beginner", "intermediate", "professional" or "darlig foto".

Best regards
Miradoryou have single quotes around status -- remove those

i prefer the as alias syntax instead of the alias = syntax

you have doublequotes around some strings ("beginner") which should be singlequotesselect enavn,fnavn,fodselsnr,fangenr
, convert(char(10),reg_date,104)
, case when legal = '1' then 'beginner'
when legal = '2' then 'intermediate'
when legal = '3' then 'professional'
when kan_skal = 'kan' then 'darlig foto'
else 'status ikke angitt'
end as status
from bilde
where ...also, none of your LIKE strings appear to have wildcards in them|||What language are you using here? What server? I suspect you're terminating your string improperly.

edit: r937 beat me to it.|||Programming in ASP, SQL towards MS SQL.
Tnx for the hints ppl...

I'm not very good at SQL... yet.. could anyone correct my code so that it might work ? thinking about where i should use double-qoutes in the start of lines and where i should use " &_

Mirador

Originally posted by Teddy
What language are you using here? What server? I suspect you're terminating your string improperly.

edit: r937 beat me to it.|||Tnx !!...

Hmm.. btw: what do u mean by Wildcards on the "LIKE"'s ?`

Mir..

Originally posted by r937
you have single quotes around status -- remove those

i prefer the as alias syntax instead of the alias = syntax

you have doublequotes around some strings ("beginner") which should be singlequotesselect enavn,fnavn,fodselsnr,fangenr
, convert(char(10),reg_date,104)
, case when legal = '1' then 'beginner'
when legal = '2' then 'intermediate'
when legal = '3' then 'professional'
when kan_skal = 'kan' then 'darlig foto'
else 'status ikke angitt'
end as status
from bilde
where ...also, none of your LIKE strings appear to have wildcards in them|||Wildcards can differ per application, but in SQL Server an example of a wild card in a like statement would be as follows:

Select legal
from bilde
where accepted like '%&%' (in this case the % are the wild cards).

This would find all records of [legal] from the table [bilde] where the amperstand (&) appears somewhere in the field [accepted].

Originally posted by Mirador
Tnx !!...

Hmm.. btw: what do u mean by Wildcards on the "LIKE"'s ?`

Mir..