Need help putting this Query in the right ASP-way

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

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

rnealejr · #2 (**permalink**) 01-09-04, 12:58

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

Mirador · #3 (**permalink**) 01-09-04, 13:37

W00w !!

.. that was a quick and gooood reply !

owe u a beer

hehe.. tnx a bunch..

Mirador..

Quote:

Originally posted by rnealejr
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

r937 · #4 (**permalink**) 01-09-04, 16:10

rnealejr, your query still has singlequotes around status

i know, it's tough spotting every little thing, eh

see http://www.dbforums.com/t977350.html

Mirador, please do a favour to those who might wish to help you, please do not post the same question in more than one forum

and where's my beer?

rnealejr · #5 (**permalink**) 01-09-04, 17:19

Rudy,

The 'status' is just another way to alias a column - just like "select price as 'Total Cost' from ...". However, it is available for backward compatibility, which may create problems with yukon -so beware. I have seen some interesting changes in yukon that might create major headaches for all of us. You can download from ms their best practices analysis tool that will help for migrating.

rnealejr · #6 (**permalink**) 01-09-04, 17:31

mirador - What country are you programming for - It looks like German or another germanic language ... Ooohhhhh, how I remember (or better yet don't) the beer from Germany/Austria ... sacralicious ...