Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Location
    Oslo
    Posts
    45

    Smile Unanswered: 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
    Mirador

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

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

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

    edit: r937 beat me to it.

  4. #4
    Join Date
    Jan 2004
    Location
    Oslo
    Posts
    45

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

    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.

  5. #5
    Join Date
    Jan 2004
    Location
    Oslo
    Posts
    45
    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 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

  6. #6
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86
    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..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •