CASE Statement Error

Question

I have been tasked with migrating my organization's Oracle SQL Views over to SQL Server. I am a novice when it comes to SQL, and have been unable to figure out the error I am getting from the CASE statement.

In the CASE statement, I want to read in the field's value, which will be a street address such as 123 Main St. The plan is to remove the 'St' substring from the field, and return '123 Main'

It already works in Oracle, I am just having difficulty converting the syntax into SQL Server, and the documentation on SUBSTRING and CASE have not helped me solve the problem. I'm probably overlooking something very simple.

The code is:

SELECT c.objectid, p.SHAPE, c.APN_PARCEL_NO AS prc_parcel_no, b.addr_status, b.addr_st_nmbr AS st_number, b.addr_st_frac AS st_fraction, b.addr_st_pfx AS st_prefix
CASE 
  WHEN SUBSTRING (b.addr_st_name,  CHARINDEX( ' ', b.addr_st_name, -1) + 1) = 'ALY' 
    THEN SUBSTRING (b.addr_st_name, 1, CHARINDEX( ' ', b.addr_st_name, -1) -1)
  ELSE b.add_st_name
END
FROM dbo.PARCEL AS p INNER JOIN dbo.TBL_APN_PARCEL_LINK AS c ON p.PRC_PARCEL_NO = c.LAND_APN INNER JOIN dbo.TBL_SITE_ADDRESS_ALL AS b ON c.APN_PARCEL_NO = b.prc_parcel_no

and the error I get reads:

Error Source:.Net SqlClient Data Provider

Error Message: Incorrect syntax near the keyword 'CASE'

I apologize for any formatting problems, still learning how to properly write SQL. For your convenience, the CASE statement code in Oracle is:

CASE
             WHEN SUBSTR (b.addr_st_name,
                          INSTR (b.addr_st_name, ' ', -1) + 1
                         ) = 'ALY'
                THEN SUBSTR (b.addr_st_name,
                             1,
                             INSTR (b.addr_st_name, ' ', -1) - 1
                            )
   ELSE b.addr_st_name
          END st_name,

tl;dr - Looking to convert Oracle view to SQL Server, error is:

Incorrect syntax near the keyword 'CASE'

You need to put a comma before `CASE` (between `st_prefix` and `CASE`) — Serpiton, Jun 18 '14 at 15:51

Alexander · Answer 1 · 2014-06-18T15:56:18.833

It looks like you're missing a comma before your CASE statement.

Also, on a side note, you might be able clarify the CASE statement like this:

SELECT c.objectid, p.SHAPE, c.APN_PARCEL_NO AS prc_parcel_no, b.addr_status, b.addr_st_nmbr AS st_number, b.addr_st_frac AS st_fraction, b.addr_st_pfx AS st_prefix,

       CASE SUBSTR (b.addr_st_name, INSTR(b.addr_st_name, ' ', -1) + 1)
         WHEN 'ALY'
           THEN SUBSTR (b.addr_st_name, 1, INSTR (b.addr_st_name, ' ', -1) - 1)
         ELSE b.addr_st_name
       END st_name,

  FROM dbo.PARCEL AS p INNER JOIN dbo.TBL_APN_PARCEL_LINK AS c ON p.PRC_PARCEL_NO = c.LAND_APN INNER JOIN dbo.TBL_SITE_ADDRESS_ALL AS b ON c.APN_PARCEL_NO = b.prc_parcel_no

MSDN Reference on CASE: http://msdn.microsoft.com/en-us/library/ms181765.aspx

score 1 · Accepted Answer · edited May 23 '17 at 10:25

The error is:

Incorrect syntax near the keyword 'CASE'

In your statement, you have:

SELECT c.objectid, p.SHAPE, c.APN_PARCEL_NO AS prc_parcel_no, b.addr_status, b.addr_st_nmbr AS st_number, b.addr_st_frac AS st_fraction, b.addr_st_pfx AS st_prefix
CASE 
  WHEN SUBSTRING (b.addr_st_name,  CHARINDEX( ' ', b.addr_st_name, -1) + 1) = 'ALY' 
    THEN SUBSTRING (b.addr_st_name, 1, CHARINDEX( ' ', b.addr_st_name, -1) -1)
  ELSE b.add_st_name
END
FROM dbo.PARCEL AS p INNER JOIN dbo.TBL_APN_PARCEL_LINK AS c ON p.PRC_PARCEL_NO = c.LAND_APN INNER JOIN dbo.TBL_SITE_ADDRESS_ALL AS b ON c.APN_PARCEL_NO = b.prc_parcel_no

The CASE statement is a new column being returned, but right before it you do not have a comma separating the new column from the previous column:

...b.addr_st_pfx AS st_prefix /*Insert comma here*/ CASE ...

Granted, the error message isn't helpful, but at least it's specific: the error is "near CASE" in the sense that it's right before it. There may be other issues, but that's the one referenced in the error message.

Another issue I can see is that the column name in the ELSE clause is misspelled: you have add_st_name, when it should be addr_st_name.

The CASE statement itself also looks like it has a problem with your conversion of INSTR to CHARINDEX. CHARINDEX's last parameter is the start position, but negative values are equivalent to 0: it starts at the beginning of the string. Oracle's INSTR uses negative positions to search backwards. See https://stackoverflow.com/a/9479899 for a technique to find the last occurrence of a character (in this case a space) in TSQL.

I think what you're trying to do is something like:

SELECT c.objectid, p.SHAPE, c.APN_PARCEL_NO AS prc_parcel_no, b.addr_status,
  b.addr_st_nmbr AS st_number, b.addr_st_frac AS st_fraction,
  b.addr_st_pfx AS st_prefix,
  CASE
    WHEN RIGHT(b.addr_st_name, NULLIF(CHARINDEX(' ', REVERSE(b.addr_st_name)), 0) - 1) = 'ALY'
      THEN SUBSTRING(b.addr_st_name, 1, LEN(b.addr_st_name) - CHARINDEX(' ', REVERSE(b.addr_st_name)))
    ELSE b.addr_st_name
  END
FROM dbo.PARCEL AS p
INNER JOIN dbo.TBL_APN_PARCEL_LINK AS c ON p.PRC_PARCEL_NO = c.LAND_APN
INNER JOIN dbo.TBL_SITE_ADDRESS_ALL AS b ON c.APN_PARCEL_NO = b.prc_parcel_no

This strips off an ending of "(space)ALY" from b.addr_st_name if it exists. Not too familiar with Oracle, but I think that's what your original statement was doing.

I attempted your suggested answer and get the error "Invalid column name 'add_st_name' Is this because I do not specify the column in the SELECT statement? — csterling, Jun 18 '14 at 16:36
@csterling Must have seen it before my edit. I saw the other part of your comment on sz1's answer and noticed you had a misspelled column name in your post that all of us have been copy/pasting. I've fixed it in my answer. — Tadmas, Jun 18 '14 at 16:39
Thanks for your help, Tadmas! I'm getting a new error now - Invalid Length parameter passed to the RIGHT function. Looking into that function now... — csterling, Jun 18 '14 at 16:42
@csterling Oh, yeah, um, I guess if there's no space in the value that will happen since the length ends up being -1. I think you can wrap that in a `NULLIF` to fix it. I'll update my answer. — Tadmas, Jun 18 '14 at 16:45

score 1 · Answer 3 · answered Jun 18 '14 at 15:55

1

You are missing a comma and a SUBSTRING character, I added the 1 (you will need to check what this should be) to the first SUBSTRING and the comma before the CASE statement.

SELECT c.objectid, p.SHAPE,
 c.APN_PARCEL_NO AS prc_parcel_no, b.addr_status, b.addr_st_nmbr AS st_number,
  b.addr_st_frac AS st_fraction, b.addr_st_pfx AS st_prefix,
CASE 
  WHEN SUBSTRING (b.addr_st_name, 1,  CHARINDEX( ' ', b.addr_st_name, -1) + 1) = 'ALY' 
    THEN SUBSTRING (b.addr_st_name, 1, CHARINDEX( ' ', b.addr_st_name, -1) -1)
  ELSE b.add_st_name
END
FROM dbo.PARCEL AS p INNER JOIN dbo.TBL_APN_PARCEL_LINK AS c ON p.PRC_PARCEL_NO = c.LAN

answered Jun 18 '14 at 15:55

sz1

72
1
8

Thanks. This seems to be closer. The second argument SUBSTRING takes is START, that specifies where the returned characters start. If I am trying to get the last part of the string would I want to use -1? I am also getting a new error after incorporating your code: Invalid column name 'add_st_name' Am I getting this because I do not specify the column in the SELECT statement? – csterling Jun 18 '14 at 16:17
@csterling I think you're running into a difference between Oracle and SQL Server: in Oracle, negative position values index the end of a string; in SQL Server, negative position values index before the beginning of a string, so they usually end up equivalent to an index of 0. – Tadmas Jun 18 '14 at 16:21
@Tadmas So how would I specify the last index of a string in SQL Server? – csterling Jun 18 '14 at 16:23
@csterling See my answer - I linked to an answer on another question asking about that, and I also wrote a replacement that I think will work for you. – Tadmas Jun 18 '14 at 16:26

CASE Statement Error

3 Answers3