Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2002
    Posts
    3

    Unanswered: concatenating fields

    Hi,
    Have a simple question.
    i have a value field and a criteria field would need to find all records for which the value matches the criteria
    Eg:
    table
    name value criteria target
    a 4 <=10
    In the 'table' i need to update target based on whether
    value meets the criteria

    SELECT name FROM table
    WHERE value + criteria

    does not work

    what am i doing wrong?
    thanks
    manju

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I'm not clear on this but if you are saying that

    SELECT name FROM table
    WHERE value + criteria

    does not work, that is because you have left of the condition, or the test. As in "value + criteria" is what?

    Is "value + criteria" > 10
    Is "value + criteria" = 568
    Is "value + criteria" between 26 and 55

    You need to add the test condition, the right hand side of the argument.
    MCDBA

  3. #3
    Join Date
    Apr 2002
    Posts
    3
    im sorry if i wasnt clear.
    actually the test condition is in the criteria field
    so the table has 3 fields
    name:a
    value:4
    criteria:<=10
    thats why i need to concatenate
    value and criteria and evaluate that expr
    So if 4<=10 i would need to do something
    thanks
    manju

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    You'll need to use dynamic SQL with the EXEC command.

    DECLARE @sqlcmd varchar(100)

    SELECT @sqlcmd = 'SELECT * FROM ' + name + ' WHERE ' + value + criteria

    EXEC (@sqlcmd)

    Something like that.
    MCDBA

  5. #5
    Join Date
    Apr 2002
    Posts
    3
    Thx for the reply but this will not do it
    name, value and criteria are fields in sql
    i need to evaluate the expr by combining 2 fields in that
    table and perform an action based on that value
    Eg:
    if the table has
    a|4|<=5
    b|6|<=4
    i need to select records for which
    the value and criteria match, in this case
    only 'a'
    thanks
    manju

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    -- ---------------------------------------------------------------------------------
    -- Run this
    -- ---------------------------------------------------------------------------------
    create table #tmp (tblname varchar(10), value varchar(10), critiria varchar(10), target varchar(10))
    insert into #tmp values ('a','4','<=10',Null)
    insert into #tmp values ('b','11','<=10',Null)
    select * from #tmp
    declare @tblname varchar(10), @sqlcmd varchar(100)
    select @tblname = min(tblname) from #tmp
    while @tblname is not null begin
    select @sqlcmd = 'update #tmp set target = ''~'' where value = ''' + value + ''' and critiria = ''' + critiria + ''' and ' + value + critiria From #tmp where tblname = @tblname
    raiserror(@sqlcmd,0,1) with nowait
    exec (@sqlcmd)
    select @tblname = min(tblname) from #tmp where tblname > @tblname
    end
    select * from #tmp

    -- ---------------------------------------------------------------------------------
    -- Should produce this
    -- ---------------------------------------------------------------------------------
    tblname value critiria target
    ---------- ---------- ---------- ----------
    a 4 <=10 NULL
    b 11 <=10 NULL

    (2 row(s) affected)

    update #tmp set target = '~' where value = '4' and critiria = '<=10' and 4<=10

    (1 row(s) affected)

    update #tmp set target = '~' where value = '11' and critiria = '<=10' and 11<=10

    (0 row(s) affected)

    tblname value critiria target
    ---------- ---------- ---------- ----------
    a 4 <=10 ~
    b 11 <=10 NULL

    (2 row(s) affected)

    That's about as good as I can do with the info provided... Hope it helps!
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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