CASE in sqlserver - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CASE in sqlserver

Author

Topic

psangeetha
Yak Posting Veteran

95 Posts

Posted - 2008-09-17 : 12:33:23

Hi all, we are trying to change a condition in a 500 lines stored procedure. Below is a bit of the code where the change is to be made:

select distinct cl.table_name, field_name,t.name,inc,
svalue = '(' + cl.table_name + '.' + field_name + 
          CASE cl.hval 
          WHEN cl.lval THEN ' ' + oper + ' ' + 
            CASE t.name        
             WHEN 'char' THEN '''' + cl.lval + ''''
             WHEN 'varchar' THEN '''' + cl.lval + ''''
             WHEN 'datetime' THEN '''' + cl.lval + ''''
             ELSE cl.lval
            END
.
.
.from cdet c1 where..

When the c1.oper value is != and c1.inc is 'E', we need to get the value as '='

Currently, it is just getting the value of the oper as is.

I tried using CASE, like below.

select distinct cl.table_name, field_name, t.name, inc, 
svalue = '(' + cl.table_name + '.' + field_name + 
CASE cl.hval
   WHEN c1.lval THEN ' ' +
      CASE inc
         WHEN 'E' then c1.oper('!=','=')
         Else oper end + ' ' + 
         CASE t.name        
            WHEN 'char' THEN '''' + cl.lval + ''''
            WHEN 'varchar' THEN '''' + cl.lval + ''''
            WHEN 'datetime' THEN '''' + cl.lval + ''''
            ELSE cl.lval
         END
      END
.
.
. from cdet c1 where..

Command completed succesfully. But when I execute the procedure, I am getting this message:

Cannot find either column "c1" or the user-defined function or aggregate "c1.oper",or the name is ambiguous.

If I try removing c1.oper and using oper('!=','=') I get this below message:

'oper' is not a recognized built-in function name.

Please give me your suggestions, Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 12:45:19

what do you mean by c1.oper('!=','=')? are you trying toreturn value of oper along with the strings?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources