Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    8

    Unanswered: Create view with field combination

    Hi there, my situation is
    I have a table x with 3 filed
    a nvarchar(100), b smalldatetime, c text(16)
    . I want to create a view like this:
    select a + ' ' + b + ' ' + c as all_field from x where all_field like %my_str%

    So, I always get a message error said wrong datatype, how can i do, please help me.

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734

    Lightbulb

    A view is just a select statement basically, meaning if you can't get something to compile in Query Analyzer it's not going to compile as a view. The syntax for a select statement similar to yours (there's nothing like yours that will actually compile) is:

    SELECT column1 + '' + column2 + '' + column3 AS all_fields
    FROM x
    WHERE column1 + '' + column2 + '' + column3 LIKE '%my_str%'

    Notice the single quotes and the fact that the column alias was not used in the where clause.

    It's kind of like the following SELECT statements:

    select name as huh from sysobjects where huh like '%a%'
    select name from sysobjects where name like '%a%'

    Only one of those actually pretends to work.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The alias (all_field) is applied to the result set at the end of execution, so you can't reference in your statement.

    In addition to Derrick;s method, this would normally work:

    select all_field
    from (select a + ' ' + b + ' ' + c as all_field from x) CombinedColumns
    where all_field like %my_str%

    ...but I think you are going to run into problems with the TEXT column type. Notice that it's size is only 16 bytes. That is because the TEXT column is actually just a pointer to the location where the actual column value is stored. So your statement is trying to concatenate a pointer address to the end of your nvarchar and smalldatetime fields. I'm sure that's not what you want.
    You will need to concatenate the actual value of column C, not it's address.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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