Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    2

    Unanswered: Updating Records Using Case

    I am writing this little piece to update collTMP table if the case is satified otherwise move onto the next case. I working for Visual Fox Pro Code that originally stated:
    Please not that UFC =alltrim(colltmp.activity)+alltrim(colltmp.sub_act iv)+ alltrim(colltmp.ss_activ)+alltrim(colltmp.function )

    case ufc = '147604' or ufc = '147605'
    replace colltmp.program with 'TO'

    case ufc = '146110'
    replace colltmp.program with 'TO'

    case ufc = '110722'
    replace colltmp.program with 'TO'

    case ufc = '110900'
    replace colltmp.program with 'TO'

    case ufc = '211310'
    replace colltmp.program with 'TO'

    case ufc = '211320'
    replace colltmp.program with 'TO'

    case ufc = '212309'
    replace colltmp.program with 'TO'
    ============================
    The code I am re-writtign in SQL Server is as follows:

    UPDATE [mun3wtemp].[dbo].[CollTMP]
    SET
    Program = case WHEN
    rtrim([CollTMP].[activity])+rtrim([CollTMP].[sub_activ])+rtrim([CollTMP].[ss_activ)]+rtrim([CollTMP].[function])
    IN ('147604','147605','142300','142301','142303','142 304','142305','142306','142308','142309','111310', '111311','111320','146110','110722','110900','2113 10','211320','212309','410925','410936','410940',' 410945','410950','410960') then 'TO' ELSE 'OT'

    **************************************
    The error message I get is
    Incorrect syntax near the keyword 'IN'.

    I have tried taking 'IN' out and using '=' for one value, no such luck. If anyone can help with this, much appreciated. Thanks

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Check syntax more carefully - draft below is working well (if select is working - update will work):

    create table test(id int identity,f1 varchar(10),f2 varchar(10))
    go
    insert test(f1,f2) values('1','1')
    insert test(f1,f2) values('2','2')
    go
    create function myfunction(@param int)
    returns varchar
    as begin
    return cast(@param as varchar)
    end
    go
    select case when f1+f2+dbo.myfunction(1) in('111')
    then 'OK'
    else 'ok'
    end
    from test

  3. #3
    Join Date
    Nov 2003
    Posts
    2

    Red face THANKS

    i was able to get it working.

    I used the follwoing code ( i was really only missing 'End' in my code:

    UPDATE [mun3wtemp].[dbo].[colltmp]
    SET program =
    CASE
    WHEN rtrim(activity)+rtrim(sub_activ)+rtrim(ss_activ)+r trim([function])in ('112800','112801','112802', '112803', '112804')THEN 'IB'
    WHEN rtrim(activity)+rtrim(sub_activ)+rtrim(ss_activ)+r trim([function]) in('147604','147605','142300','142301','142303','1 42304','142305','142306','142308','142309','111310 ','111311','111320','146110','110722','110900', '211310','211320','212309','410925','410936','4109 40','410945','410950','410960') THEN 'TO'
    END




    Originally posted by snail
    Check syntax more carefully - draft below is working well (if select is working - update will work):

    create table test(id int identity,f1 varchar(10),f2 varchar(10))
    go
    insert test(f1,f2) values('1','1')
    insert test(f1,f2) values('2','2')
    go
    create function myfunction(@param int)
    returns varchar
    as begin
    return cast(@param as varchar)
    end
    go
    select case when f1+f2+dbo.myfunction(1) in('111')
    then 'OK'
    else 'ok'
    end
    from test

Posting Permissions

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