SQL Syntax in ASP 1

[ragepro]

I am having some trouble applying extended queries in an ASP environment. I'd like to use the following:

SELECT Name, alphaOrder = 
  CASE 
    WHEN (LEFT(Name,2) LIKE '[a-d]') THEN 1
    WHEN (LEFT(Name,2) LIKE '[b-c]') THEN 2
    ELSE 0
  END
FROM table
ORDER BY AlphaOrder, Name

I've tried using the following but can't figure out how to do the case statement:

stDQ = "SELECT * FROM table

...heres where i get completely stuck!!

With my limited knowledge of ASP and SQL I'm really not sure how to go about incorporating this kind of query into ASP. THanks in advance!

 

[snowboardr]

I believe you could do it like this:

I have not yet tested this multiple LIKES, but I am sure it works for one LIKE because I use that all the time.

strSQL = "SELECT colum_name,column_name2 FROM table_name WHERE (colum_name LIKE '" & sLetter1 & "%') AND "_
& "(table_name LIKE '" & sLetter2 & "%') AND "_
& "(table_name LIKE '" & sLetter3 & "%')"

let me know how that works for you.

- Jason





www.vzio.com
ASP WEB DEVELOPMENT

 

[clarkin]

All those newlines are just for formatting, it will work equally well piled into one big long line (ie, a single SQL="" line).

So:

stDQ = "SELECT Name, alphaOrder = CASE WHEN (LEFT(Name,2) LIKE '[a-d]') THEN 1 WHEN (LEFT(Name,2) LIKE '[b-c]') THEN 2 ELSE 0 END FROM table ORDER BY AlphaOrder, Name"

then run it like any other select
If you want to keep the formatting for readability (probably a good idea) you can:

stDQ = "SELECT Name, alphaOrder = "_
  & "CASE WHEN (LEFT(Name,2) LIKE '[a-d]') THEN 1 "_
  & "WHEN (LEFT(Name,2) LIKE '[b-c]') THEN 2 "_
  & "ELSE 0 END "_
  & "FROM table ORDER BY AlphaOrder, Name"

(the resulting string is the same)

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[ragepro]

Jason, more than one LIKE seemed to confuse it and it would return nothing. One LIKE worked of course. Thanks for the idea though!

clarkin, your's worked GREAT! Thanks a lot man!! I had actually thought doing it that way and didn't think it would work... I should have tried it anyway, huh? Thanks again.

-adam