Link to home
Start Free TrialLog in
Avatar of Brent Guttmann
Brent GuttmannFlag for United States of America

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..#tempInfFacts') IS NOT NULL
    DROP TABLE #tempInfFacts

IF object_id('tempdb..#FactPivot') IS NOT NULL
    DROP TABLE #FactPivot

IF object_id('tempdb..#MasterFacts') IS NOT NULL
    DROP TABLE #MasterFacts

IF object_id('tempdb..#FactPivotScore') IS NOT NULL
    DROP TABLE #FactPivotScore

IF object_id('tempdb..#tempScore') 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_BSInventory]
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
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Hi,

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.
Avatar of Brent Guttmann

ASKER

Yes, it works in management studio.
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Brent Guttmann
Brent Guttmann

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
I have got to go to bed now - but here is some more  details regarding the error -- this  is what i got when trying to add a new table to a different worksheet using the same connection
msg2.txt
msg1.txt
Is this fixed ?

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

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.
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.
Not really an answer, but apparently he's a complainer.