Score:0

SQL Server 2008 R2 Nested Query with a Function

cn flag

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.

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.