CODEWITHSUNDEEP
sqlsql-servercase
Mad Scientist
Mad Scientist

Reputation: 917

Microsoft SQL Server: Incorrect syntax near the keyword 'CASE'

I have an oracle database in which I have generated these three SQL statements with CASE:

create unique index MGDEF_UK2 on MAI_GROUP_DEF(case when results ='y'
THEN CONFIG_ID||'-'|| MAI_CLASS_ID||'-'|| LEVEL_1||'-'|| LEVEL_2||'-'|| 
LEVEL_3||'-'|| LEVEL_4 ELSE null END);

CREATE UNIQUE INDEX PPSTAT_S_UK2 on PATTERN_PROC_STATUSES(case when
STATUS_SHORT is null THEN '1' ELSE STATUS_SHORT END);

CREATE UNIQUE INDEX RL_UK on RUN_LOG(CASE WHEN status ='R'
THEN status ELSE NULL END);

Now I have used liquibase to migrate the database to Microsoft SQL Server and the statements were generated like this:

CREATE UNIQUE NONCLUSTERED INDEX MGDEF_UK2 ON [MAI_GROUP_DEF](CASE "RESULTS" WHEN 'y' 
THEN TO_CHAR("CONFIG_ID")||'-'||TO_CHAR("MAI_CLASS_ID")||'-'||TO_CHAR("LEVEL_1")
||'-'||TO_CHAR("LEVEL_2")||'-'||TO_CHAR("LEVEL_3")||'-'||TO_CHAR("LEVEL_4") 
ELSE NULL END)
GO

CREATE UNIQUE NONCLUSTERED INDEX PPSTAT_S_UK2 ON [PATTERN_PROC_STATUSES]
(CASE  WHEN "STATUS_SHORT" IS NULL THEN '1' ELSE "STATUS_SHORT" END)
GO

CREATE UNIQUE NONCLUSTERED INDEX RL_UK ON [RUN_LOG](CASE "STATUS" WHEN 'R' 
THEN "STATUS" ELSE NULL END)
GO

When I run these statements this error occurs:

Incorrect syntax near the keyword 'CASE'.

I have tried the syntax of different tutorials but still this error occurs. I am using Microsoft SQL Server 17. What can I do?

Upvotes: 1

Views: 827

Answers (3)

Dashrath
Dashrath

Reputation: 93

Case syntax is wrong defined. you can try with online query convector will help you with syntax: http://www.sqlines.com/online

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

SQL Server uses + instead of || for string concatenation. Also, it doesn't support indexes on expressions. Instead, you need to use a persisted computed column.

Also, SQL Server supports filtered indexes. So the third example would be:

CREATE UNIQUE INDEX RL_UK on RUN_LOG(status) WHERE status = 'R';

SQL Server doesn't allow duplicate NULL values in an index, so the second is:

CREATE UNIQUE INDEX PPSTAT_S_UK2 ON PATTERN_PROC_STATUSES(STATUS_SHORT);

The first is complicated:

alter table MAI_GROUP_DEF
    add newcol (case when results = 'Y' then . . .  end) persisted;

Remember to use + for the expression.

CREATE UNIQUE INDEX MGDEF_UK2 ON MAI_GROUP_DEF(newcol)

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

try to use + instead of || in CASE WHEN

|| is for Oracle, not for sqlserver

CREATE UNIQUE NONCLUSTERED INDEX MGDEF_UK2 ON [MAI_GROUP_DEF](CASE "RESULTS" WHEN 'y' 
THEN TO_CHAR("CONFIG_ID")+'-'+TO_CHAR("MAI_CLASS_ID")+'-'+TO_CHAR("LEVEL_1")
+'-'+TO_CHAR("LEVEL_2")+'-'+TO_CHAR("LEVEL_3")+'-'+TO_CHAR("LEVEL_4") 
ELSE NULL END)
GO

I would use CONCAT function. to combine string.

CREATE UNIQUE NONCLUSTERED INDEX mgdef_uk2 
  ON [MAI_GROUP_DEF](CASE "results" 
  WHEN 'y' THEN Concat(To_char("config_id"), '-', To_char("mai_class_id"), '-', 
                To_char( 
                                "level_1"), '-', To_char("level_2"), '-' + 
                To_char("level_3"), 
                                '-', To_char("level_4")) 
  ELSE NULL 
END) 

go

Upvotes: 1

Related Questions