Forum Discussion

gregarican's avatar
gregarican
Copper Contributor
Nov 11, 2021

SQL Server 2008 R2 Nested Query with a Function

I am helping another SQL Server DBA with an issue they have with just a particular SQL Server 2008 R2 instance. I have run this query successfully on my own 2008 R2 instances, 2012 instances, etc. I'll list the query below. The sub-selects are pulling a single specific value from a table-valued function. We have verified that the referenced view column exists, we have verified that the function can be successfully run as a standalone outside of the sub-select, we have verified the customer ID is valid, etc.

 

DECLARE @CustId varchar(12)
SET @CustId = 'AA100168'   

SELECT       
   CASE (select WORD from dbo.GETALLWORDS([Customers].[Anniversary],'-') where WORDNUM = 2)
    WHEN 'Jan' THEN 1
    WHEN 'Feb' THEN 2
    WHEN 'Mar' THEN 3
    WHEN 'Apr' THEN 4
    WHEN 'May' THEN 5
    WHEN 'Jun' THEN 6
    WHEN 'Jul' THEN 7
    WHEN 'Aug' THEN 8
    WHEN 'Sep' THEN 9
    WHEN 'Oct' THEN 10
    WHEN 'Nov' THEN 11
    WHEN 'Dec' THEN 12
    END AS AnnMonth, 
    (select WORD from dbo.GETALLWORDS([Customers].[Anniversary],'-') where WORDNUM = 1) AS AnnDay,
    (select WORD from dbo.GETALLWORDS([Customers].[Anniversary],'-') where WORDNUM = 3) AS AnnYear
FROM dbo.viwUser_Customers_Custom Customers 
   WHERE [Customers].[Id] = @CustId

 

Here are the errors that come back for each sub-select.

 

Msg 102, Level 15, State 1     Incorrect syntax near 'Customers'.

 

Any suggestions as to what is causing this to fail? It fails to execute in SSMS, but trying to parse the query it comes back clean. I checked that the 2008 R2 deployment is at the same service pack level as other instances where this same query works.

 

No RepliesBe the first to reply

Resources