Brent Guttmann
asked on
SQL Error - SQL Statement is not valid. There are no columns detected in the statement.
Hi, I was using the below query for a refreshable workbook and it suddenly stopped working.. the error when validating it in powerpivot is : "The SQL statement is not valid. There are no columns detected in the statement."
Can someone please tell me why this happening / tell me how I can fix it?
Set NOCOUNT on
Set FMTONLY off
----Drop #temp tables if they exist
IF object_id('tempdb..#tempIn fFacts') IS NOT NULL
DROP TABLE #tempInfFacts
IF object_id('tempdb..#FactPi vot') IS NOT NULL
DROP TABLE #FactPivot
IF object_id('tempdb..#Master Facts') IS NOT NULL
DROP TABLE #MasterFacts
IF object_id('tempdb..#FactPi votScore') IS NOT NULL
DROP TABLE #FactPivotScore
IF object_id('tempdb..#tempSc ore') IS NOT NULL
DROP TABLE #tempScore
---Select desired 'date-oriented' codes from Infinity
SELECT FILENO,
CODE,
CAST (FIELD AS DATE) AS TRANS_DATE
INTO #tempInfFacts
FROM [CLS-SYNC].[dbo].Infinity
WHERE Folder_Number = 1
AND CODE IN ('*MedRcvd', '*D1DmG1', '*001AtDm', '*DmdApp', '*DmdRej', '*001DmS1', '*DMDRTN', '*CAFFFrq', '*ClAfRq', '*CAFFRvd', '*FDCPARV', '*ValReq',
'*L1ValGn', '*L1ValRv', '*VALRJR', '*VALRJD', '*FDCPASN', '*L1CmpGn', '*L1CmpRw', '*CAppD', '*EscDt', '*L2SnDt')
AND FILENO NOT LIKE 'TEST%'
ORDER BY FILENO
---Select from the TEMP table & Pivot; Pay strict attention to order of codes in statements & Group By
SELECT *
INTO #FactPivot
FROM #tempInfFacts PIVOT (Max (Trans_Date) FOR CODE IN ([*MedRcvd], [*D1DmG1], [*001AtDm], [*DmdApp], [*DmdRej], [*001DmS1], [*DMDRTN], [*CAFFFrq], [*ClAfRq], [*CAFFRvd], [*FDCPARV], [*ValReq],
[*L1ValGn], [*L1ValRv], [*VALRJR], [*VALRJD], [*FDCPASN], [*L1CmpGn], [*L1CmpRw], [*CAppD], [*EscDt], [*L2SnDt])) AS PivotCodes
GROUP BY FileNo, [*MedRcvd], [*D1DmG1], [*001AtDm], [*DmdApp], [*DmdRej], [*001DmS1], [*DMDRTN], [*CAFFFrq], [*ClAfRq], [*CAFFRvd], [*FDCPARV], [*ValReq],
[*L1ValGn], [*L1ValRv], [*VALRJR], [*VALRJD], [*FDCPASN], [*L1CmpGn], [*L1CmpRw], [*CAppD], [*EscDt], [*L2SnDt]
CREATE INDEX fileno
ON #FactPivot(fileno)
---Create Temp Table with Master Facts
SELECT *
INTO #MasterFacts
FROM [BASCLS-SYNC].[dbo].[VW_BS Inventory]
WHERE [Suit Date] IS NULL
AND [Judgment Date] IS NULL
AND [Suit Amount] = 0
AND [JMT_AMT] = 0
AND [Status Code] <> '938'
ORDER BY FILENO
CREATE INDEX fileno
ON #MasterFacts(fileno)
---Get scores
SELECT FILENO,
CODE,
FIELD,
MAX([Date]) AS [Date]
INTO #tempScore
FROM [CLS-SYNC].[dbo].Infinity
WHERE Folder_Number = 1
AND CODE IN ('*TU_Scr2', '*TURSCal')
AND FILENO NOT LIKE 'TEST%'
GROUP BY FILENO, CODE, FIELD
SELECT *
INTO #FactPivotScore
FROM #tempScore PIVOT (Max ([FIELD]) FOR CODE IN ([*TU_Scr2], [*TURSCal])) AS PivotCodes
GROUP BY FileNo, [*TU_Scr2], [*TURSCal], [DATE];
CREATE INDEX fileno
ON #FactPivotScore(fileno);
---Join all of the information on FileNo
SELECT #MasterFacts.[FileNo],
[Client FileNo],
[Forwarder Name],
[Clerk Name],
[County],
[Debtor 1 Name],
[Debtor State],
[Last Status Change],
[Status Age],
[Status],
[Status Code],
[Date Received],
[Original Claim],
[Balance],
[Last Payment Date],
[Last Payment Amount],
#FactPivotScore.[*TU_Scr2] AS [Score],
#FactPivotScore.[*TURSCal] AS [TU Score],
#FactPivot.[*MedRcvd] AS [Media Received],
#FactPivot.[*D1DmG1] AS [Demand Generated],
#FactPivot.[*001AtDm] AS [Demand Atty Review],
#FactPivot.[*DmdApp] AS [Demand Atty Approved],
#FactPivot.[*DmdRej] AS [Demand Rejected],
#FactPivot.[*001DmS1] AS [Demand Sent],
#FactPivot.[*DMDRTN] AS [Demand Returned Date],
#FactPivot.[*CAFFFrq] AS [Client Affidavit First Request],
#FactPivot.[*ClAfRq] AS [Client Affidavit Requested Date],
#FactPivot.[*CAFFRvd] AS [Client Affidavit Received],
#FactPivot.[*FDCPARV] AS [FDCPA Val Req Rec],
#FactPivot.[*ValReq] AS [Validation Requested],
#FactPivot.[*L1ValGn] AS [Validation Letter Generated],
#FactPivot.[*L1ValRv] AS [Validation Reviewed By Atty],
#FactPivot.[*VALRJR] AS [Validation Rejected],
#FactPivot.[*VALRJD] AS [Validation Approved],
#FactPivot.[*FDCPASN] AS [Validation Sent],
#FactPivot.[*L1CmpGn] AS [Complaint Generated Date],
#FactPivot.[*L1CmpRw] AS [Complaint Reviewed By Attorney],
#FactPivot.[*CAppD] AS [Complaint Approved],
#FactPivot.[*EscDt] AS [Escalation Date],
GetDate() AS 'Update'
FROM #MasterFacts
INNER JOIN
#FactPivot
ON #FactPivot.FileNo = #MasterFacts.FileNo
LEFT OUTER JOIN
#FactPivotScore
ON #FactPivotScore.FileNo = #MasterFacts.[FileNo]
WHERE #FactPivot.[*CAppD] is NULL
Can someone please tell me why this happening / tell me how I can fix it?
Set NOCOUNT on
Set FMTONLY off
----Drop #temp tables if they exist
IF object_id('tempdb..#tempIn
DROP TABLE #tempInfFacts
IF object_id('tempdb..#FactPi
DROP TABLE #FactPivot
IF object_id('tempdb..#Master
DROP TABLE #MasterFacts
IF object_id('tempdb..#FactPi
DROP TABLE #FactPivotScore
IF object_id('tempdb..#tempSc
DROP TABLE #tempScore
---Select desired 'date-oriented' codes from Infinity
SELECT FILENO,
CODE,
CAST (FIELD AS DATE) AS TRANS_DATE
INTO #tempInfFacts
FROM [CLS-SYNC].[dbo].Infinity
WHERE Folder_Number = 1
AND CODE IN ('*MedRcvd', '*D1DmG1', '*001AtDm', '*DmdApp', '*DmdRej', '*001DmS1', '*DMDRTN', '*CAFFFrq', '*ClAfRq', '*CAFFRvd', '*FDCPARV', '*ValReq',
'*L1ValGn', '*L1ValRv', '*VALRJR', '*VALRJD', '*FDCPASN', '*L1CmpGn', '*L1CmpRw', '*CAppD', '*EscDt', '*L2SnDt')
AND FILENO NOT LIKE 'TEST%'
ORDER BY FILENO
---Select from the TEMP table & Pivot; Pay strict attention to order of codes in statements & Group By
SELECT *
INTO #FactPivot
FROM #tempInfFacts PIVOT (Max (Trans_Date) FOR CODE IN ([*MedRcvd], [*D1DmG1], [*001AtDm], [*DmdApp], [*DmdRej], [*001DmS1], [*DMDRTN], [*CAFFFrq], [*ClAfRq], [*CAFFRvd], [*FDCPARV], [*ValReq],
[*L1ValGn], [*L1ValRv], [*VALRJR], [*VALRJD], [*FDCPASN], [*L1CmpGn], [*L1CmpRw], [*CAppD], [*EscDt], [*L2SnDt])) AS PivotCodes
GROUP BY FileNo, [*MedRcvd], [*D1DmG1], [*001AtDm], [*DmdApp], [*DmdRej], [*001DmS1], [*DMDRTN], [*CAFFFrq], [*ClAfRq], [*CAFFRvd], [*FDCPARV], [*ValReq],
[*L1ValGn], [*L1ValRv], [*VALRJR], [*VALRJD], [*FDCPASN], [*L1CmpGn], [*L1CmpRw], [*CAppD], [*EscDt], [*L2SnDt]
CREATE INDEX fileno
ON #FactPivot(fileno)
---Create Temp Table with Master Facts
SELECT *
INTO #MasterFacts
FROM [BASCLS-SYNC].[dbo].[VW_BS
WHERE [Suit Date] IS NULL
AND [Judgment Date] IS NULL
AND [Suit Amount] = 0
AND [JMT_AMT] = 0
AND [Status Code] <> '938'
ORDER BY FILENO
CREATE INDEX fileno
ON #MasterFacts(fileno)
---Get scores
SELECT FILENO,
CODE,
FIELD,
MAX([Date]) AS [Date]
INTO #tempScore
FROM [CLS-SYNC].[dbo].Infinity
WHERE Folder_Number = 1
AND CODE IN ('*TU_Scr2', '*TURSCal')
AND FILENO NOT LIKE 'TEST%'
GROUP BY FILENO, CODE, FIELD
SELECT *
INTO #FactPivotScore
FROM #tempScore PIVOT (Max ([FIELD]) FOR CODE IN ([*TU_Scr2], [*TURSCal])) AS PivotCodes
GROUP BY FileNo, [*TU_Scr2], [*TURSCal], [DATE];
CREATE INDEX fileno
ON #FactPivotScore(fileno);
---Join all of the information on FileNo
SELECT #MasterFacts.[FileNo],
[Client FileNo],
[Forwarder Name],
[Clerk Name],
[County],
[Debtor 1 Name],
[Debtor State],
[Last Status Change],
[Status Age],
[Status],
[Status Code],
[Date Received],
[Original Claim],
[Balance],
[Last Payment Date],
[Last Payment Amount],
#FactPivotScore.[*TU_Scr2]
#FactPivotScore.[*TURSCal]
#FactPivot.[*MedRcvd] AS [Media Received],
#FactPivot.[*D1DmG1] AS [Demand Generated],
#FactPivot.[*001AtDm] AS [Demand Atty Review],
#FactPivot.[*DmdApp] AS [Demand Atty Approved],
#FactPivot.[*DmdRej] AS [Demand Rejected],
#FactPivot.[*001DmS1] AS [Demand Sent],
#FactPivot.[*DMDRTN] AS [Demand Returned Date],
#FactPivot.[*CAFFFrq] AS [Client Affidavit First Request],
#FactPivot.[*ClAfRq] AS [Client Affidavit Requested Date],
#FactPivot.[*CAFFRvd] AS [Client Affidavit Received],
#FactPivot.[*FDCPARV] AS [FDCPA Val Req Rec],
#FactPivot.[*ValReq] AS [Validation Requested],
#FactPivot.[*L1ValGn] AS [Validation Letter Generated],
#FactPivot.[*L1ValRv] AS [Validation Reviewed By Atty],
#FactPivot.[*VALRJR] AS [Validation Rejected],
#FactPivot.[*VALRJD] AS [Validation Approved],
#FactPivot.[*FDCPASN] AS [Validation Sent],
#FactPivot.[*L1CmpGn] AS [Complaint Generated Date],
#FactPivot.[*L1CmpRw] AS [Complaint Reviewed By Attorney],
#FactPivot.[*CAppD] AS [Complaint Approved],
#FactPivot.[*EscDt] AS [Escalation Date],
GetDate() AS 'Update'
FROM #MasterFacts
INNER JOIN
#FactPivot
ON #FactPivot.FileNo = #MasterFacts.FileNo
LEFT OUTER JOIN
#FactPivotScore
ON #FactPivotScore.FileNo = #MasterFacts.[FileNo]
WHERE #FactPivot.[*CAppD] is NULL
ASKER
Yes, it works in management studio.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Im trying to avoid making a new connection because i have around 10 slicers connected to it... i had previosuly added blank columns in the pivot view with =blank() as the formula but the connection was refreshing after i did that. I am not sure if the connection worked after i added the slicers or not, but those took quite a while to format so like i said, trying to avoid a new connection, if possible
Actually, come to think of it, i tested the original version of the workbook and it has the same message, so i dont think its due to any of my recent edits/additons
ASKER
Is this fixed ?
Regards,
Pawan
Regards,
Pawan
I just created a new connection in a new workbook
Well in the below comment --
https://www.experts-exchange.com/questions/28986816/SQL-Error-SQL-Statement-is-not-valid-There-are-no-columns-detected-in-the-statement.html?anchorAnswerId=41909752#a41909752
I have also gave you the same suggestion.
https://www.experts-exchange.com/questions/28986816/SQL-Error-SQL-Statement-is-not-valid-There-are-no-columns-detected-in-the-statement.html?anchorAnswerId=41909752#a41909752
Have you changed recently in this ? try refreshing the connection or may be recreate the connection is possible?
I have also gave you the same suggestion.
ASKER
Sure - I will do that, although I didn't need to ask a question on here to know that creating a new workbook and new connection was always an option.. that's like saying "Reset it". I wanted the cause of the connection not working so that I could prevent it happening in the future. But, if people are going to complain, makes no difference to me where the points go.
Yes, I meant disregard - Didn't realize I needed to spellcheck everything.
Yes, I meant disregard - Didn't realize I needed to spellcheck everything.
ASKER
Not really an answer, but apparently he's a complainer.
Have you tried running the code in SQL - SSMS ? Check out if you are getting rows from there ?
Also when you are inserting data in the temp table dont do order by. It will hamper the performance.