Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Posts
    328

    Unanswered: Using Case with a view

    I am trying to use the 'case' statement in a view. It looks like this.

    select column1,column2
    case when substring(column3,1,2) like '01' then my_column ='city_name'
    end
    from table1

    I keep getting an incorrect syntax near 'like'. column1 and column2 exist in the table that the view is dependent on, but my_column is a virtual column, meaning I am creating this column on the fly.
    Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The problem is in this part:

    my_column =

    Do you mean something like

    select column1,column2
    case when substring(column3,1,2) like '01' then my_column end as 'city_name'
    from table1

  3. #3
    Join Date
    Aug 2003
    Posts
    328
    Originally posted by MCrowley
    The problem is in this part:

    my_column =

    Do you mean something like

    select column1,column2
    case when substring(column3,1,2) like '01' then my_column end as 'city_name'
    from table1
    I have 15 cities, so if substring(column3,1,2) like '01' then I want my_column to be city1. If substring(column3,1,2) like '02', then I want my_column to be city2 etc.
    Thanks for your help.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Rather than hard-cosing values in a view, it would be better to have a table of cities, but if this is the way you have to go, then:

    select column1, column2,
    case when colum3 = '01' then 'city1'
    when colum3 = '02' then 'city2'
    when ...
    else null end
    from table.....

  5. #5
    Join Date
    Aug 2003
    Posts
    328
    I can make a table of cities. That's no problem.

  6. #6
    Join Date
    Aug 2003
    Posts
    328
    I get the error message:

    Server: Msg 4511, Level 16, State 1, Procedure ProForm1099_1, Line 3
    Create View or Function failed because no column name was specified for column 7.

    Column 7 is of course where my case statement starts.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Of course...it needs a reference

    select column1, column2,
    case when colum3 = '01' then 'city1'
    when colum3 = '02' then 'city2'
    when ...
    else null end AS Column3
    from table.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Aug 2003
    Posts
    328
    Thats what it was! Thanks. I was trying to put the column alias in the wrong place.
    Last edited by exdter; 01-12-04 at 13:44.

Posting Permissions

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