SQL Server 2008 R2 Nested Query with a Function

%3CLINGO-SUB%20id%3D%22lingo-sub-2955435%22%20slang%3D%22en-US%22%3ESQL%20Server%202008%20R2%20Nested%20Query%20with%20a%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2955435%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20am%20helping%20another%20SQL%20Server%20DBA%20with%20an%20issue%20they%20have%20with%20just%20a%20particular%20SQL%20Server%202008%20R2%20instance.%20I%20have%20run%20this%20query%20successfully%20on%20my%20own%202008%20R2%20instances%2C%202012%20instances%2C%20etc.%20I'll%20list%20the%20query%20below.%20The%20sub-selects%20are%20pulling%20a%20single%20specific%20value%20from%20a%20table-valued%20function.%20We%20have%20verified%20that%20the%20referenced%20view%20column%20exists%2C%20we%20have%20verified%20that%20the%20function%20can%20be%20successfully%20run%20as%20a%20standalone%20outside%20of%20the%20sub-select%2C%20we%20have%20verified%20the%20customer%20ID%20is%20valid%2C%20etc.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3EDECLARE%20%40CustId%20varchar(12)%0ASET%20%40CustId%20%3D%20'AA100168'%20%20%20%0A%0ASELECT%20%20%20%20%20%20%20%0A%20%20%20CASE%20(select%20WORD%20from%20dbo.GETALLWORDS(%5BCustomers%5D.%5BAnniversary%5D%2C'-')%20where%20WORDNUM%20%3D%202)%0A%20%20%20%20WHEN%20'Jan'%20THEN%201%0A%20%20%20%20WHEN%20'Feb'%20THEN%202%0A%20%20%20%20WHEN%20'Mar'%20THEN%203%0A%20%20%20%20WHEN%20'Apr'%20THEN%204%0A%20%20%20%20WHEN%20'May'%20THEN%205%0A%20%20%20%20WHEN%20'Jun'%20THEN%206%0A%20%20%20%20WHEN%20'Jul'%20THEN%207%0A%20%20%20%20WHEN%20'Aug'%20THEN%208%0A%20%20%20%20WHEN%20'Sep'%20THEN%209%0A%20%20%20%20WHEN%20'Oct'%20THEN%2010%0A%20%20%20%20WHEN%20'Nov'%20THEN%2011%0A%20%20%20%20WHEN%20'Dec'%20THEN%2012%0A%20%20%20%20END%20AS%20AnnMonth%2C%20%0A%20%20%20%20(select%20WORD%20from%20dbo.GETALLWORDS(%5BCustomers%5D.%5BAnniversary%5D%2C'-')%20where%20WORDNUM%20%3D%201)%20AS%20AnnDay%2C%0A%20%20%20%20(select%20WORD%20from%20dbo.GETALLWORDS(%5BCustomers%5D.%5BAnniversary%5D%2C'-')%20where%20WORDNUM%20%3D%203)%20AS%20AnnYear%0AFROM%20dbo.viwUser_Customers_Custom%20Customers%20%0A%20%20%20WHERE%20%5BCustomers%5D.%5BId%5D%20%3D%20%40CustId%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EHere%20are%20the%20errors%20that%20come%20back%20for%20each%20sub-select.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3EMsg%20102%2C%20Level%2015%2C%20State%201%20%20%20%20%20Incorrect%20syntax%20near%20'Customers'.%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAny%20suggestions%20as%20to%20what%20is%20causing%20this%20to%20fail%3F%20It%20fails%20to%20execute%20in%20SSMS%2C%20but%20trying%20to%20parse%20the%20query%20it%20comes%20back%20clean.%20I%20checked%20that%20the%202008%20R2%20deployment%20is%20at%20the%20same%20service%20pack%20level%20as%20other%20instances%20where%20this%20same%20query%20works.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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.

 

0 Replies