Problems with CASE statement within SELECT

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

SQL Server 2000 Forums

Transact-SQL (2000)

Problems with CASE statement within SELECT

Author

Topic

caefer
Starting Member

10 Posts

Posted - 2003-08-19 : 06:10:28

and another hi to you all!

another problem occured.
as you might thought, the former mentioned tables where just examples. this is, what I do.

I have a table 'RunTime.dbo.DiscreteTag' containing the TagNames of some hundrets of bits and a second table 'RunTime.dbo.DiscreteLive' containing those bits' values and datetime they last changed.
they (of course) can have a value of either 0 or 1.
a third table 'RunTime.dbo.Message' stores messages to be thrown on either of those values. there are 2 rows 'Message0' and 'Message1'

Now i want to get a result that shows me datetime, tagname and current value (got that) AND the message refering to the value.

I tried the following:


SELECT DiscreteLive.DateTime, DiscreteTag.TagName, DiscreteTag.InitialValue, DiscreteLive.Value,
	"Message" = CASE
		WHEN DiscreteLive.Value > 0 THEN Message.Message1
		ELSE Message.Message0
	END
	FROM DiscreteLive, DiscreteTag, Message
	WHERE DiscreteTag.TagName    = DiscreteLive.TagName
	  AND DiscreteTag.MessageKey = Message.MessageKey
	  AND DiscreteLive.TagName LIKE "%STM%"

which throws an syntax error near 'WHEN'..

even the following code which is just like in the transaction-sql-helpfile shows that kind of behaviour . . .


SELECT 	"Price Category" = 
	CASE 
		WHEN Value < 1 THEN 'Very Reasonable Title'
		ELSE 'Expensive book!'
	END
FROM DiscreteLive

I am working on MS SQL Server 6.5.

thanks+regards
/christian

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-08-19 : 06:28:18

Hi!

That kind of syntax forks for me on SQL 6.5. Could you post your table DDL and the exact error which is thrown??

caefer
Starting Member

10 Posts

Posted - 2003-08-19 : 07:13:17

hi andraax!

here is all I could manage to get about those two tables. hope that helps!
oh, and the exact error is 'syntax error near WHEN'...


/* Microsoft SQL Server - Scripting			*/
/* Server: SQLSERVER_1					*/
/* Database: Runtime					*/
/* Creation Date 8/19/03 1:03:03 PM 			*/

set quoted_identifier on
GO

/****** Object:  Trigger dbo.tI_DiscreteLive    Script Date: 19.08.03 13:03:05 ******/
if exists (select * from sysobjects where id = object_id('dbo.tI_DiscreteLive') and sysstat & 0xf = 8)
	drop trigger "dbo"."tI_DiscreteLive"
GO

/****** Object:  Trigger dbo.tU_DiscreteLive    Script Date: 19.08.03 13:03:05 ******/
if exists (select * from sysobjects where id = object_id('dbo.tU_DiscreteLive') and sysstat & 0xf = 8)
	drop trigger "dbo"."tU_DiscreteLive"
GO

/****** Object:  Table dbo.DiscreteLive    Script Date: 19.08.03 13:03:06 ******/
if exists (select * from sysobjects where id = object_id('dbo.DiscreteLive') and sysstat & 0xf = 3)
	drop table "dbo"."DiscreteLive"
GO

/****** Object:  Table dbo.DiscreteLive    Script Date: 19.08.03 13:03:06 ******/
CREATE TABLE "dbo"."DiscreteLive" (
	"DateTime" "datetime" NOT NULL ,
	"TagName" varchar (33) NOT NULL ,
	"Value" "tinyint" NULL ,
	"Quality" "tinyint" NOT NULL ,
	"QualityDetail" "int" NULL ,
	"wwTagKey" "int" NOT NULL ,
	CONSTRAINT "PK__DiscreteLive__5693E1F0" PRIMARY KEY  CLUSTERED 
	(
		"DateTime",
		"TagName"
	)
)
GO

 CREATE  INDEX "XIF80DiscreteLive" ON "dbo"."DiscreteLive"("TagName")
GO

/****** Object:  Trigger dbo.tI_DiscreteLive    Script Date: 19.08.03 13:03:06 ******/
create trigger tI_DiscreteLive on DiscreteLive for INSERT as
/* Wonderware INSERT trigger on DiscreteLive */
begin
  declare  @numrows int,
           @nullcnt int,
           @validcnt int,
           @errno   int,
           @errmsg  varchar(255)

  select @numrows = @@rowcount
  /* DiscreteTag is doing DiscreteLive ON CHILD INSERT RESTRICT */
  if
    /* %ChildFK(" or",update) */
    update(TagName)
  begin
    select @nullcnt = 0
    select @validcnt = count(*)
      from inserted,DiscreteTag
        where
          /* %JoinFKPK(inserted,DiscreteTag) */
          inserted.TagName = DiscreteTag.TagName
    /* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted where"," and") */
    
    if @validcnt + @nullcnt != @numrows
    begin
      select @errno  = 30002,
             @errmsg = 'Cannot INSERT "DiscreteLive" because "DiscreteTag" does not exist.'
      goto error
    end
  end


  return
error:
    raiserror @errno @errmsg
    rollback transaction
end

GO

/****** Object:  Trigger dbo.tU_DiscreteLive    Script Date: 19.08.03 13:03:06 ******/
create trigger tU_DiscreteLive on DiscreteLive for UPDATE as
/* Wonderware UPDATE trigger on DiscreteLive */
begin
  declare  @numrows int,
           @nullcnt int,
           @validcnt int,
           @insDateTime datetime, 
           @insTagName varchar(33),
           @errno   int,
           @errmsg  varchar(255)

  select @numrows = @@rowcount
  /* DiscreteTag is doing DiscreteLive ON CHILD UPDATE RESTRICT */
  if
    /* %ChildFK(" or",update) */
    update(TagName)
  begin
    select @nullcnt = 0
    select @validcnt = count(*)
      from inserted,DiscreteTag
        where
          /* %JoinFKPK(inserted,DiscreteTag) */
          inserted.TagName = DiscreteTag.TagName
    /* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted where"," and") */
    
    if @validcnt + @nullcnt != @numrows
    begin
      select @errno  = 30007,
             @errmsg = 'Cannot UPDATE "DiscreteLive" because "DiscreteTag" does not exist.'
      goto error
    end
  end


  return
error:
    raiserror @errno @errmsg
    rollback transaction
end

GO
-------------------------------------------------------------------------------------------------
/* Microsoft SQL Server - Scripting			*/
/* Server: SQLSERVER_1					*/
/* Database: Runtime					*/
/* Creation Date 8/19/03 1:03:27 PM 			*/

set quoted_identifier on
GO

/****** Object:  Trigger dbo.tU_DiscreteTag    Script Date: 19.08.03 13:03:29 ******/
if exists (select * from sysobjects where id = object_id('dbo.tU_DiscreteTag') and sysstat & 0xf = 8)
	drop trigger "dbo"."tU_DiscreteTag"
GO

/****** Object:  Trigger dbo.tD_DiscreteTag    Script Date: 19.08.03 13:03:29 ******/
if exists (select * from sysobjects where id = object_id('dbo.tD_DiscreteTag') and sysstat & 0xf = 8)
	drop trigger "dbo"."tD_DiscreteTag"
GO

/****** Object:  Trigger dbo.tI_DiscreteTag    Script Date: 19.08.03 13:03:29 ******/
if exists (select * from sysobjects where id = object_id('dbo.tI_DiscreteTag') and sysstat & 0xf = 8)
	drop trigger "dbo"."tI_DiscreteTag"
GO

/****** Object:  Table dbo.DiscreteTag    Script Date: 19.08.03 13:03:29 ******/
if exists (select * from sysobjects where id = object_id('dbo.DiscreteTag') and sysstat & 0xf = 3)
	drop table "dbo"."DiscreteTag"
GO

/****** Object:  Table dbo.DiscreteTag    Script Date: 19.08.03 13:03:29 ******/
CREATE TABLE "dbo"."DiscreteTag" (
	"TagName" varchar (33) NOT NULL ,
	"MessageKey" "int" NOT NULL ,
	"InitialValue" "tinyint" NOT NULL CONSTRAINT "DF__DiscreteT__Initi__3473D416" DEFAULT (0),
	CONSTRAINT "PK__DiscreteTag__337FAFDD" PRIMARY KEY  CLUSTERED 
	(
		"TagName"
	)
)
GO

 CREATE  INDEX "XIF40DiscreteTag" ON "dbo"."DiscreteTag"("MessageKey")
GO

/****** Object:  Trigger dbo.tU_DiscreteTag    Script Date: 19.08.03 13:03:29 ******/
create trigger tU_DiscreteTag on DiscreteTag
  for UPDATE
  as
begin
  declare  @numrows int,
           @nullcnt int,
           @validcnt int,
           @insTagName varchar(33),
           @errno   int,
           @errmsg  varchar(255)

  select @numrows = @@rowcount
/* Message used by DiscreteTag ON CHILD UPDATE RESTRICT */
  if
    /* %ChildFK(" or",update) */
    update(MessageKey)
  begin
    select @nullcnt = 0
    select @validcnt = count(*)
      from inserted,Message
        where
          /* %JoinFKPK(inserted,Message) */
          inserted.MessageKey = Message.MessageKey
    /* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted where"," and") */
    
    if @validcnt + @nullcnt != @numrows
    begin
      select @errno  = 30007,
             @errmsg = 'Cannot UPDATE "DiscreteTag" because "Message" does not exist.'
      goto error
    end
  end

/* Tag  DiscreteTag ON CHILD UPDATE RESTRICT */
  if
    /* %ChildFK(" or",update) */
    update(TagName)
  begin
    select @nullcnt = 0
    select @validcnt = count(*)
      from inserted,Tag
        where
          /* %JoinFKPK(inserted,Tag) */
          inserted.TagName = Tag.TagName
    /* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted where"," and") */
    
    if @validcnt + @nullcnt != @numrows
    begin
      select @errno  = 30007,
             @errmsg = 'Cannot UPDATE "DiscreteTag" because "Tag" does not exist.'
      goto error
    end
  end


/* DiscreteTag did DiscreteHistory ON PARENT UPDATE RESTRICT */
  if
    /* %ParentPK(" or",update) */
    update(TagName)
  begin
    if exists (
      select * from deleted,DiscreteHistory
      where
        /*  %JoinFKPK(DiscreteHistory,deleted," = "," and") */
        DiscreteHistory.TagName = deleted.TagName
    )
    begin
      select @errno  = 30005,
             @errmsg = 'Cannot UPDATE "DiscreteTag" because "DiscreteHistory" exists.'
      goto error
    end
  end

/* DiscreteTag is doing DiscreteLive ON PARENT UPDATE RESTRICT */
  if
    /* %ParentPK(" or",update) */
    update(TagName)
  begin
    if exists (
      select * from deleted,DiscreteLive
      where
        /*  %JoinFKPK(DiscreteLive,deleted," = "," and") */
        DiscreteLive.TagName = deleted.TagName
    )
    begin
      select @errno  = 30005,
             @errmsg = 'Cannot UPDATE "DiscreteTag" because "DiscreteLive" exists.'
      goto error
    end
  end


/* start of mod tracking */ 
declare @res int
exec @res = ww_ModLogStatus
if (@res = 1)
begin 
  if @numrows = 1
  begin
    IF update(TagName) 
      BEGIN
        INSERT ModLog(id, ColumnName, ColumnType, ModType, UserKey, DateTime)
        SELECT id,'TagName',3,'U',user_id(),getdate()
         FROM InSQLSysObjects WHERE Name = 'DiscreteTag'
        INSERT ModChar(ModKey, OldValue, NewValue)
         SELECT @@IDENTITY, deleted.TagName, inserted.TagName
          FROM deleted,inserted
      END
   IF update(MessageKey) 
      BEGIN
        INSERT ModLog(id, ColumnName, ColumnType, ModType, UserKey, DateTime)
        SELECT id,'MessageKey',1,'U',user_id(),getdate()
         FROM InSQLSysObjects WHERE Name = 'DiscreteTag'
        INSERT ModInteger(ModKey, OldValue, NewValue)
         SELECT @@IDENTITY, deleted.MessageKey, inserted.MessageKey
          FROM deleted,inserted
      END
   IF update(InitialValue) 
      BEGIN
        INSERT ModLog(id, ColumnName, ColumnType, ModType, UserKey, DateTime)
        SELECT id,'InitialValue',1,'U',user_id(),getdate()
         FROM InSQLSysObjects WHERE Name = 'DiscreteTag'
        INSERT ModInteger(ModKey, OldValue, NewValue)
         SELECT @@IDENTITY, deleted.InitialValue, inserted.InitialValue
          FROM deleted,inserted
      END
   
  end
end
/* end of mod tracking */ 

  return
error:
    raiserror @errno @errmsg
    rollback transaction
end

GO

/****** Object:  Trigger dbo.tD_DiscreteTag    Script Date: 19.08.03 13:03:29 ******/
create trigger tD_DiscreteTag on DiscreteTag for DELETE as
/* Wonderware DELETE trigger on DiscreteTag */
begin
  declare  @errno   int,
           @errmsg  varchar(255)
    /* DiscreteTag did DiscreteHistory ON PARENT DELETE RESTRICT */
    if exists (
      select * from deleted,DiscreteHistory
      where
        /*  %JoinFKPK(DiscreteHistory,deleted," = "," and") */
        DiscreteHistory.TagName = deleted.TagName
    )
    begin
      select @errno  = 30001,
             @errmsg = 'Cannot DELETE "DiscreteTag" because "DiscreteHistory" exists.'
      goto error
    end

    /* DiscreteTag is doing DiscreteLive ON PARENT DELETE RESTRICT */
    if exists (
      select * from deleted,DiscreteLive
      where
        /*  %JoinFKPK(DiscreteLive,deleted," = "," and") */
        DiscreteLive.TagName = deleted.TagName
    )
    begin
      select @errno  = 30001,
             @errmsg = 'Cannot DELETE "DiscreteTag" because "DiscreteLive" exists.'
      goto error
    end


    return
error:
    raiserror @errno @errmsg
    rollback transaction
end

GO

/****** Object:  Trigger dbo.tI_DiscreteTag    Script Date: 19.08.03 13:03:29 ******/
create trigger tI_DiscreteTag on DiscreteTag for INSERT as
/* Wonderware INSERT trigger on DiscreteTag */
begin
  declare  @numrows int,
           @nullcnt int,
           @validcnt int,
           @errno   int,
           @errmsg  varchar(255)


  select @numrows = @@rowcount
  /* Message used by DiscreteTag ON CHILD INSERT RESTRICT */
  if
    /* %ChildFK(" or",update) */
    update(MessageKey)
  begin
    select @nullcnt = 0
    select @validcnt = count(*)
      from inserted,Message
        where
          /* %JoinFKPK(inserted,Message) */
          inserted.MessageKey = Message.MessageKey
    /* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted where"," and") */
    
    if @validcnt + @nullcnt != @numrows
    begin
      select @errno  = 30002,
             @errmsg = 'Cannot INSERT "DiscreteTag" because "Message" does not exist.'
      goto error
    end
  end

  /* Tag  DiscreteTag ON CHILD INSERT RESTRICT */
  if
    /* %ChildFK(" or",update) */
    update(TagName)
  begin
    select @nullcnt = 0
    select @validcnt = count(*)
      from inserted,Tag
        where
          /* %JoinFKPK(inserted,Tag) */
          inserted.TagName = Tag.TagName
    /* %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted where"," and") */
    
    if @validcnt + @nullcnt != @numrows
    begin
      select @errno  = 30002,
             @errmsg = 'Cannot INSERT "DiscreteTag" because "Tag" does not exist.'
      goto error
    end
  end


  return
error:
    raiserror @errno @errmsg
    rollback transaction
end

GO

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-19 : 09:16:25

Have you tried retyping the query to check if there isn't an unprintable character.

Are you running this in query analyser?

try
select s = case when 1=0 then 'a' else 'b' end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

caefer
Starting Member

10 Posts

Posted - 2003-08-20 : 02:29:35

Hi!

I'm running ISQL_w over odbc (but local on the sql-server makes no difference so odbc is ok.)
This drives me crazy. really.
Well, you see, we have this application running called InTouch (Process Control Engineering) which set up the sql-server with industrialsql on top.

now we want to access several tables on that server, just the queries seem to totally misbehave..
even your proposed simple select statement returns the following:

/*-----------------------------
SET QUOTED_IDENTIFIER ON

SELECT s = CASE WHEN 1=0 THEN 'a' ELSE 'b'
-----------------------------*/
Msg 170, Level 15, State 1
Line 3: Incorrect syntax near 'b'.

I don't get it. . .

regards
/christian

mr_mist
Grunnio

1870 Posts

Posted - 2003-08-20 : 03:05:32

SELECT s = CASE WHEN 1=0 THEN 'a' ELSE 'b' END

-------
Moo. :)

caefer
Starting Member

10 Posts

Posted - 2003-08-20 : 04:22:31

oh, yessir..
that works, thx.
but when I add a FROM.. statement I got the error again.

SELECT s = CASE WHEN 1=0 THEN 'a' ELSE 'b' END FROM MyTable
Incorrect syntax near 'WHEN'.

any syntax mistype I made?

regards
/christian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources