All Forums Database
sophiachan 1 post Joined 12/13
15 Dec 2013
Substring Error?

WHERE ...
AND SUBSTRING(CASE WHEN TABLE_A.COL_1 IS NOT NULL THEN TABLE_A.COL_1 ELSE TABLE_B.COL_1 END, LENGTH(CASE WHEN TABLE_A.COL_1 IS NOT NULL THEN TABLE_A.COL_1 ELSE TABLE_B.COL_1 END)-11,12) IN (SELECT COL_1 FROM TABLE_C)
 It says expect something between 'END' and ',' but what will it be?  I tried to add () to the case statement but still doesn't work...  Anyone?  Thanks!

M.Saeed Khurram 544 posts Joined 09/12
15 Dec 2013

Hi,
There are two syntax available for substring:
SUBSTRING( STRING FROM FOR)
SUBSTR(STRING , START_INDEX , LENGTH)
So if you are using substring you must specify FROM and FOR instead of comma. Other wise you can use SUBSTR with COMMAs.
Try this

SUBSTR(CASE WHEN TABLE_A.COL_1 IS NOT NULL THEN TABLE_A.COL_1 ELSE TABLE_B.COL_1 END, LENGTH(CASE WHEN TABLE_A.COL_1 IS NOT NULL THEN TABLE_A.COL_1 ELSE TABLE_B.COL_1 END)-11,12) IN (SELECT COL_1 FROM TABLE_C)

 

Khurram

You must sign in to leave a comment.