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

Mirador · #1 (**permalink**) 01-09-04, 11:18

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
Mirador

r937 · #2 (**permalink**) 01-09-04, 12:32

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 singlequotes

Code:

select 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

Teddy · #3 (**permalink**) 01-09-04, 12:41

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

edit: r937 beat me to it.

Mirador · #4 (**permalink**) 01-09-04, 13:32

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

Quote:

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.

Mirador · #5 (**permalink**) 01-09-04, 13:34

Tnx !!...

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

Mir..

Quote:

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 singlequotes

Code:

select 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

acg_ray · #6 (**permalink**) 01-09-04, 14:40

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].

Quote:

Originally posted by Mirador
Tnx !!...

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

Mir..

Internet Services

Web Development

Digital Marketing

Consumer Tech