Forum Discussion

mike_asplin's avatar
mike_asplin
Helper V
9 months ago
Solved

SQL Query Syntax issue

Hi. In the past I have been handed sql queries to use in Power Query.   The structure of these is to specify the DB at the top of the query and then only reference the table name through out

 

USE

[CURRYS_DW_REPORTING]

GO

WITH CTE_NTT AS

(

SELECT              *

FROM [dbo].[Fact_empower_NotificationTrackingTasks]

 

This errors when I put it in PQ as follows

DataSource.Error: Microsoft SQL: Incorrect syntax near 'GO'.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Incorrect syntax near ','.
Details:
    DataSourceKind=SQL
    DataSourcePath=prd-so-dw2.iia-cloud.com;master
    Message=Incorrect syntax near 'GO'.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

I have found if I make the table reference explicit it stops erroring and previously this seems to have fixed it e.g. this

WITH CTE_NTT AS

(

SELECT              *

FROM [CURRYS_DW_REPORTING].[dbo].[Fact_empower_NotificationTrackingTasks]

 

Today I have some new queries and these changes dont work. it doesnt error, but just sitting waiting of the DB to respond.

 

My question is why cant PQ handle the USE type query or can it, but I needed to do something else for it to work?

 

Any advice appreciated

 

Need Help
  • Zanqueta's avatar
    Zanqueta
    9 months ago

    Yes, your code is quite extensive and seems somewhat complex in terms of many case when statements, but we can leave that for another time. As a suggestion, you could talk to your IT team about generating a table in the database where you can consult these results directly. In the meantime, to validate the connection, I rewrote the code with a few minor adjustments and filtering only one date.

     

    ;WITH CTE_NTT AS (
    -- CTE for selecting notification tracking tasks
    SELECT *
    FROM [CURRYS_DW_REPORTING].[dbo].[Fact_empower_NotificationTrackingTasks]
    WHERE NotificationTypeLookupID IN (
        '25'  -- TaskRespondWorkflowInactiveNotification
        ,'14'  -- TaskRespondToPermanentlyLockedCase
        ,'13'  -- TaskRespondToSilentNotification
        ,'12'  -- Task (Review Doc Service Document)
        ,'9'   -- TaskRespondToCommunication
        ,'8'   -- TaskCompleteParkedCall
        ,'7'   -- WorkflowLockedCase
    )
    AND CONVERT(DATE, TaskCreatedDateTime) = '2023-01-01' -- Filter for a single day
),
CTE_CASES AS (
    -- CTE to gather information related to cases
    SELECT
        CTR.CaseTrackingRecordID,
        T.TopicID,
        T.TopicTitle,
        CTR.RowStartDate,
        CTR.RowEndDate,
        OWN.Forename + ' ' + OWN.Surname AS CaseOwner,
        OWN.EmailAddress AS CaseOwnerEmailAddress,
        ADV.Forename + ' ' + ADV.Surname AS CaseAdviser,
        ISNULL(ADV.EmailAddress, '[email protected]') AS CaseAdviserEmailAddress,
        W_STAGE.WorkflowStageTitle AS WorkflowStage,
        W_STEP.WorkflowStepTitle AS WorkflowStep
    FROM [CURRYS_DW_REPORTING].[dbo].[Dim_empower_CaseTrackingRecord] AS CTR
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_CaseStatus] AS CS
        ON CTR.CaseStatusKey = CS.CaseStatusKey
        AND CS.IsValidCase = 1
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS EMP
        ON CTR.EmployeeUserKey = EMP.empowerUserKey
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS OWN
        ON CTR.OwnerUserKey = OWN.empowerUserKey
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS ADV
        ON CTR.CurrentAdviserKey = ADV.empowerUserKey
        AND ADV.IsTestAccount = 0
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[dim_empower_topic] AS T
        ON CTR.TopicKey = T.TopicKey
        AND T.RowIsCurrent = 'Y'
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_WorkflowStage] AS W_STAGE
        ON CTR.WorkflowStageKey = W_STAGE.WorkflowStageKey
        AND W_STAGE.RowIsCurrent = 'Y'
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_WorkflowStep] AS W_STEP
        ON CTR.WorkflowStepKey = W_STEP.WorkflowStepKey
        AND W_STEP.RowIsCurrent = 'Y'
)
SELECT DISTINCT
    CASE 
        WHEN NTT.NotificationTypeLookupID = 9 AND CTC.CommunicationSubject = 'Request for Help' THEN 'Help Request'
        WHEN NTT.NotificationTypeLookupID = 9 THEN 'Response Required Case Note'
        WHEN NT.NotificationType = 'TaskRespondToPermanentlyLockedCase' THEN 'Permanently Locked Case'
        WHEN NT.NotificationType = 'TaskRespondToSilentNotification' THEN 'Silent Notification'
        WHEN NT.NotificationType = 'Task (Review Doc Service Document)' THEN 'Doc Review'
        WHEN NT.NotificationType IN ('Task (Complete Parked Call)', 'TaskCompleteParkedCall') THEN 'Parked Call'
        WHEN NT.NotificationType = 'WorkflowLockedCase' THEN 'Workflow Locked Case'
        WHEN NT.NotificationType = 'TaskRespondWorkflowInactiveNotification' THEN 'Inactive Notification'
    END AS [Contact Type],
    CONVERT(DATE, NTT.TaskCreatedDateTime) AS [Contact Date],
    CONVERT(VARCHAR(8), NTT.TaskCreatedDateTime, 108) AS [Contact Time],
    NTTDT.CaseTrackingRecordID AS [Case Number],
    CASES.TopicTitle AS [Topic Title],
    CASES.WorkflowStage AS [Workflow Stage],
    CASES.WorkflowStep AS [Workflow Step],
    NTT.NotificationTrackingTaskID AS [Contact ID],
    CASES.CaseAdviser AS [Adviser],
    CASES.CaseAdviserEmailAddress AS [EMAIL]
FROM CTE_NTT AS NTT WITH (NOLOCK)
INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_NotificationType] AS NT WITH (NOLOCK)
    ON NT.NotificationTypeID = NTT.NotificationTypeLookupID
INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_Date] AS DD WITH (NOLOCK)
    ON NTT.TaskCreatedDateKey = DD.DateKey
LEFT JOIN [CURRYS_DW_REPORTING].[dbo].[Fact_empower_NotificationDistributions] AS NTTDT
    ON NTTDT.NotificationDistributionID = NTT.NotificationDistributionID
LEFT JOIN [CURRYS_DW_REPORTING].[dbo].[Fact_empower_CaseTrackingCommunication] AS CTC WITH (NOLOCK)
    ON CTC.CaseTrackingRecordID = NTTDT.CaseTrackingRecordID
    AND ISNULL(CTC.IsDraft, 0) = 0
LEFT JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS OWN WITH (NOLOCK)
    ON OWN.UserID = CTC.SenderUserID
    AND OWN.RowIsCurrent = 'Y'
INNER JOIN CTE_CASES AS CASES
    ON NTTDT.CaseTrackingRecordID = CASES.CaseTrackingRecordID
    AND NTT.TaskCreatedDateTime BETWEEN CASES.RowStartDate AND CASES.RowEndDate
    AND ISNULL(RIGHT(CASES.CaseAdviserEmailAddress, 15), '') LIKE 'adviserplus.com'
WHERE ISNULL(OWN.EmailAddress, '') NOT LIKE '%adviserplus%'

     

    See if that works.

     

    Your original query:

    ;WITH CTE_NTT AS (
    -- CTE for selecting notification tracking tasks
    SELECT *
    FROM [CURRYS_DW_REPORTING].[dbo].[Fact_empower_NotificationTrackingTasks]
    WHERE NotificationTypeLookupID IN (
        '25'  -- TaskRespondWorkflowInactiveNotification
        ,'14'  -- TaskRespondToPermanentlyLockedCase
        ,'13'  -- TaskRespondToSilentNotification
        ,'12'  -- Task (Review Doc Service Document)
        ,'9'   -- TaskRespondToCommunication
        ,'8'   -- TaskCompleteParkedCall
        ,'7'   -- WorkflowLockedCase
    )
    AND CONVERT(DATE, TaskCreatedDateTime) BETWEEN 
        DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, -25, CONVERT(DATE, GETDATE() - 1))), 0) 
        AND CONVERT(DATE, GETDATE() - 1)
),
CTE_CASES AS (
    -- CTE to gather information related to cases
    SELECT
        CTR.CaseTrackingRecordID,
        T.TopicID,
        T.TopicTitle,
        CTR.RowStartDate,
        CTR.RowEndDate,
        OWN.Forename + ' ' + OWN.Surname AS CaseOwner,
        OWN.EmailAddress AS CaseOwnerEmailAddress,
        ADV.Forename + ' ' + ADV.Surname AS CaseAdviser,
        ISNULL(ADV.EmailAddress, '[email protected]') AS CaseAdviserEmailAddress,
        W_STAGE.WorkflowStageTitle AS WorkflowStage,
        W_STEP.WorkflowStepTitle AS WorkflowStep
    FROM [CURRYS_DW_REPORTING].[dbo].[Dim_empower_CaseTrackingRecord] AS CTR
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_CaseStatus] AS CS
        ON CTR.CaseStatusKey = CS.CaseStatusKey
        AND CS.IsValidCase = 1
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS EMP
        ON CTR.EmployeeUserKey = EMP.empowerUserKey
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS OWN
        ON CTR.OwnerUserKey = OWN.empowerUserKey
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS ADV
        ON CTR.CurrentAdviserKey = ADV.empowerUserKey
        AND ADV.IsTestAccount = 0
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[dim_empower_topic] AS T
        ON CTR.TopicKey = T.TopicKey
        AND T.RowIsCurrent = 'Y'
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_WorkflowStage] AS W_STAGE
        ON CTR.WorkflowStageKey = W_STAGE.WorkflowStageKey
        AND W_STAGE.RowIsCurrent = 'Y'
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_WorkflowStep] AS W_STEP
        ON CTR.WorkflowStepKey = W_STEP.WorkflowStepKey
        AND W_STEP.RowIsCurrent = 'Y'
)
SELECT DISTINCT
    CASE 
        WHEN NTT.NotificationTypeLookupID = 9 AND CTC.CommunicationSubject = 'Request for Help' THEN 'Help Request'
        WHEN NTT.NotificationTypeLookupID = 9 THEN 'Response Required Case Note'
        WHEN NT.NotificationType = 'TaskRespondToPermanentlyLockedCase' THEN 'Permanently Locked Case'
        WHEN NT.NotificationType = 'TaskRespondToSilentNotification' THEN 'Silent Notification'
        WHEN NT.NotificationType = 'Task (Review Doc Service Document)' THEN 'Doc Review'
        WHEN NT.NotificationType IN ('Task (Complete Parked Call)', 'TaskCompleteParkedCall') THEN 'Parked Call'
        WHEN NT.NotificationType = 'WorkflowLockedCase' THEN 'Workflow Locked Case'
        WHEN NT.NotificationType = 'TaskRespondWorkflowInactiveNotification' THEN 'Inactive Notification'
    END AS [Contact Type],
    CONVERT(DATE, NTT.TaskCreatedDateTime) AS [Contact Date],
    CONVERT(VARCHAR(8), NTT.TaskCreatedDateTime, 108) AS [Contact Time],
    NTTDT.CaseTrackingRecordID AS [Case Number],
    CASES.TopicTitle AS [Topic Title],
    CASES.WorkflowStage AS [Workflow Stage],
    CASES.WorkflowStep AS [Workflow Step],
    NTT.NotificationTrackingTaskID AS [Contact ID],
    CASES.CaseAdviser AS [Adviser],
    CASES.CaseAdviserEmailAddress AS [EMAIL]
    --,'Test McTestface' AS [Client]
FROM CTE_NTT AS NTT WITH (NOLOCK)
INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_NotificationType] AS NT WITH (NOLOCK)
    ON NT.NotificationTypeID = NTT.NotificationTypeLookupID
INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_Date] AS DD WITH (NOLOCK)
    ON NTT.TaskCreatedDateKey = DD.DateKey
LEFT JOIN [CURRYS_DW_REPORTING].[dbo].[Fact_empower_NotificationDistributions] AS NTTDT
    ON NTTDT.NotificationDistributionID = NTT.NotificationDistributionID
LEFT JOIN [CURRYS_DW_REPORTING].[dbo].[Fact_empower_CaseTrackingCommunication] AS CTC WITH (NOLOCK)
    ON CTC.CaseTrackingRecordID = NTTDT.CaseTrackingRecordID
    AND ISNULL(CTC.IsDraft, 0) = 0
LEFT JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS OWN WITH (NOLOCK)
    ON OWN.UserID = CTC.SenderUserID
    AND OWN.RowIsCurrent = 'Y'
INNER JOIN CTE_CASES AS CASES
    ON NTTDT.CaseTrackingRecordID = CASES.CaseTrackingRecordID
    AND NTT.TaskCreatedDateTime BETWEEN CASES.RowStartDate AND CASES.RowEndDate
    AND ISNULL(RIGHT(CASES.CaseAdviserEmailAddress, 15), '') LIKE 'adviserplus.com'
    -- This case wasn't assigned to a Network Rail_Empower adviser when note/request was sent
WHERE ISNULL(OWN.EmailAddress, '') NOT LIKE '%adviserplus%'

     

15 Replies

Replies have been turned off for this discussion
  • Zanqueta's avatar
    Zanqueta
    Super User
    9 months ago
    Power Query does not support SQL Server commands such as USE and GO. These are intended for SQL Server Management Studio (SSMS) and are not valid within the Power BI query engine. Additionally, when using Common Table Expressions (CTEs), Power Query requires a semicolon (;) before the WITH clause to ensure proper syntax.

    Recommended Approach

    To make your SQL query compatible with Power Query:
    1. Remove USE and GO
      These are not recognised by Power Query and will result in syntax errors.
    2. Add a semicolon before WITH
      This is required to correctly initiate a CTE.
    3. Use fully qualified table names
      Even if the database is defined in the connection, explicitly referencing it can help avoid ambiguity.
    ;WITH CTE_NTT AS (
    SELECT *
    FROM [CURRYS_DW_REPORTING].[dbo].[Fact_empower_NotificationTrackingTasks]
)
SELECT *
FROM CTE_NTT
     
    f you would like assistance adapting one of your new queries for Power Query, feel free to share it here.
    ✅ If this answer resolved your issue, please mark it as correct to help other members of the community.
     
  • mike_asplin's avatar
    mike_asplin
    Helper V
    9 months ago

    Not sure i've got this quite right, but thnaks ofr help

     

    Original query

    USE
[CURRYS_DW_REPORTING]	

GO

WITH CTE_NTT AS
(
SELECT	*

FROM [dbo].[Fact_empower_NotificationTrackingTasks]
WHERE NotificationTypeLookupID IN	('25'	-- TaskRespondWorkflowinactiveNotifiaction
									,'14'	-- TaskRespondToPermanentlyLockedCase
									,'13'	-- TaskRespondToSilentNotification
									,'12'	-- Task (Review Doc Service Document)
									,'9'	-- TaskRespondToCommunication
									,'8'	-- TaskCompleteParkedCall
									,'7'	-- WorkflowLockedCase
									)
	AND CONVERT(DATE, TaskCreatedDateTime)
		BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M, -25, CONVERT(DATE, GETDATE()-1))), 0) AND CONVERT(DATE, GETDATE()-1)
),

     

    editted query

    ;WITH CTE_NTT AS

(
SELECT	*

FROM [CURRYS_DW_REPORTING].[dbo].[Fact_empower_NotificationTrackingTasks]

WHERE NotificationTypeLookupID IN	('25'	-- TaskRespondWorkflowinactiveNotifiaction
									,'14'	-- TaskRespondToPermanentlyLockedCase
									,'13'	-- TaskRespondToSilentNotification
									,'12'	-- Task (Review Doc Service Document)
									,'9'	-- TaskRespondToCommunication
									,'8'	-- TaskCompleteParkedCall
									,'7'	-- WorkflowLockedCase
									)
	AND CONVERT(DATE, TaskCreatedDateTime)
		BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M, -25, CONVERT(DATE, GETDATE()-1))), 0) AND CONVERT(DATE, GETDATE()-1)
),

     

    Just sitting spinning its wheels 

  • Zanqueta's avatar
    Zanqueta
    Super User
    9 months ago

    Hi mike_asplin , thanks for let me know.

     

     If this answer resolved your issue, please, give a kudo and mark it as correct to help other members of the community.

    • mike_asplin's avatar
      mike_asplin
      Helper V
      9 months ago

      sorry it isnt loading. it doesnt show an error but never loads. Is my code below correct?

       

      Thanks

      • tayloramy's avatar
        tayloramy
        Super User
        9 months ago

        Hi mike_asplin, 

         

        You can't use SQL in the Power Query editor - only M code will work. 

         

        Use the Get Data menu to select whatever type of database you are using, and then when prompted for the details you can use teh SQL query there. 

         

        If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.