SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Thread: SQL IF's

Hybrid View

  1. #1
    SitePoint Addict
    Join Date
    Jan 2002
    Location
    Omaha, NE
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL IF's

    I use sql server 2000 and I'm hoping someone can help me.

    I inherited a system that controls some layouts of webpages through a database. One table has a NumberOfCols field that contains the information for the number of columns in the layout. What I'm looking to do is return the value "A" if the number is 1, "A,B" if the the number is 2, and "A,B,C" if the number is 3.

    A simple query to return the data would be

    SELECT NumberOfCols
    FROM template
    WHERE cid = 100 and sid = 2

    I assume you would use some IF's but I'm unsure how.

    Thanks
    "Oh, you hate your job? Why didn't you say so? There's a support group for that.
    It's called EVERYBODY, and they meet at the bar."

    --Drew Carey

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT case when NumberOfCols = 3 then 'A,B,C'
                when NumberOfCols = 2 then 'A,B'
                when NumberOfCols = 1 then 'A'
                else null
              end as translated_value
      FROM template
     WHERE cid = 100 and sid = 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jan 2002
    Location
    Omaha, NE
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Now escape the quotes

    That works perfectly accept I need to use the results in a subquery meaning I do an

    WHERE Col IN ('A','B','C')

    So I need the returned results to be in the above format. I tried escaping the quotes with two single quotes ('') but that didn't work.

    Any other ideas?
    "Oh, you hate your job? Why didn't you say so? There's a support group for that.
    It's called EVERYBODY, and they meet at the bar."

    --Drew Carey

  4. #4
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you are using the number of columns information in a subqurery why not just use the integer value?

    Code:
     ....
     Where col ANY (select NumberOfCols from Template where cid = Outer.Cid and sid = Outer.Sid)
    Or rather, why are you using

    results
    -------
    'A'
    'B'
    'C'

    3 rows returned

    when you just want to know the number of columns?

  5. #5
    SitePoint Addict
    Join Date
    Jan 2002
    Location
    Omaha, NE
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Because of what i have to work with. The system stores the the number of Cols in a template and then uses COLDFUSION to translate into how many columns it needs to make with each column designated A,B, OR C depending on how many you need. The information going into each column is then associated with either A, B, OR C. Not the number.

    So what I have to do is somehow translate the NumOfCols field into a string like 'A','B','C' to use in the IN statement.


    It isn't designed the best, but that is what I have to work with
    "Oh, you hate your job? Why didn't you say so? There's a support group for that.
    It's called EVERYBODY, and they meet at the bar."

    --Drew Carey

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you totally lost me

    how does this --
    Code:
    case when NumberOfCols = 3 then 'A,B,C'
         when NumberOfCols = 2 then 'A,B'
         when NumberOfCols = 1 then 'A'
         else null
       end
    relate to this --
    Code:
    WHERE Col IN ('A','B','C')
    ??
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Jan 2002
    Location
    Omaha, NE
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It relates because I'm attempting to do this:

    Code:
     ...
    Code:
    WHERE Col IN (
    SELECT case when NumberOfCols = 3 then 'A,B,C'
    when NumberOfCols = 2 then 'A,B'
    when NumberOfCols = 1 then 'A'
    else null
    end
    FROM T
    WHERE cid = 100 and sid = 8)
    "Oh, you hate your job? Why didn't you say so? There's a support group for that.
    It's called EVERYBODY, and they meet at the bar."

    --Drew Carey

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    still not sure i understand why, but try this --
    Code:
    select ...
      from ...
     where 1 =
         ( case when NumberOfCols = 3 
                 and col in ('A','B','C')
                then 1  
                when NumberOfCols = 2 
                 and col in ('A','B')
                then 1
                when NumberOfCols = 1
                 and col = 'A'
                then 1 
                else 0 end )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Jan 2002
    Location
    Omaha, NE
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Got It!

    OK...well I went at it another way. Instead of the IN statement I use a less then or equal to (<=) and then return the highest letter it might be. Here is the code. What I have been trying to do is return the customer/site pair that contains a certain component. I didn't know you could do a less then on a character or string.

    Code:
     SELECT distinct cc.custid,cc.siteid
    FROM custcomponents cc, custsite cs
    where cc.componentid = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#Getcomp.componentID#">
    AND cs.custid > 100
    AND cs.status = 'A'
    AND cs.custid = cc.custid
    AND cs.siteid = cc.siteid
    AND cc.ColumnCode <= ( SELECT case when NumOfCols = 3 then 'C'
    					 when NumOfCols = 2 then 'B'
    					 when NumOfCols = 1 then 'A'
    					 when NumOfCols = 4 then 'D'
    			 else 'B'
    	  end as ColList
    		 FROM templates t
    		 WHERE t.TemplateID = (Select TemplateID from custTemplate ct
    			 WHERE ct.custid = cc.custid 
    			 and ct.siteid = cc.siteid ))
    Thanks for all your help!
    "Oh, you hate your job? Why didn't you say so? There's a support group for that.
    It's called EVERYBODY, and they meet at the bar."

    --Drew Carey

  10. #10
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I believe I already suggested using the ANY operator, which would have given you the same result.
    Last edited by asterix; Jan 13, 2005 at 03:03. Reason: Then again maybe not...


Bookmarks

Posting Permissions

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