CODEWITHSUNDEEP
sqlsql-serverpowerbi
Tygo
Tygo

Reputation: 192

Power BI Microsoft SQL: Incorrect syntax near ';'. Incorrect syntax near ')'

The following runs well in DataGrip. However, in Power BI, I am getting this error and do not know why:

Microsoft SQL: Incorrect syntax near ';'. Incorrect syntax near ')'.

It was giving me another CTE error where I had to add the semicolon at the start, but this I cannot seem to figure out. Syntax looks fine.

  ;WITH pizzaorder AS ( 
        SELECT DISTINCT [order].id 
        FROM [order]
        LEFT JOIN order_line 
        ON order_line.order_id = [order].id 
        WHERE pizza_product_id IS NOT NULL
    ),
    
    otherproductorder AS (
        SELECT DISTINCT [order].id 
        FROM [order]
        LEFT JOIN order_line ON order_line.order_id = [order].id 
        WHERE other_product_id IS NOT NULL
    ),
    
    mostsold AS (
        SELECT TOP 10 product_name,
        SUM(quantity) AS quantity 
        FROM pizzaorder
        INNER JOIN otherproductorder ON otherproductorder.id = pizzaorder.id 
        LEFT JOIN order_line ON order_line.order_id = pizzaorder.id 
        INNER JOIN product ON product.id = order_line.other_product_id 
        GROUP BY product_name
    )
    
    SELECT TOP 10 *
    FROM mostsold 
    ORDER BY quantity DESC

Upvotes: 0

Views: 3600

Answers (2)

Tygo
Tygo

Reputation: 192

I managed to solve the issue by starting over as SMor Suggested.

Its still not perfect and the database has some flaws, but this is the solution that worked for my situation.

SELECT TOP 10 product_name, SUM(quantity) AS quantity 
FROM (SELECT DISTINCT [order].id 
    FROM [order] 
    LEFT JOIN order_line ON order_line.order_id = [order].id 
     WHERE pizza_product_id IS NOT NULL) as pizzaorder 
LEFT JOIN order_line ON order_line.order_id = pizzaorder.id 
INNER JOIN product ON product.id = order_line.other_product_id 
GROUP BY product_name 
ORDER BY quantity DESC

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89406

In many contexts Power BI will compose your provided query with additional queries by wrapping it in a subquery. That doesn't work with CTEs.

So you can create a view using that logic, or transform your CTEs to subqueries in the FROM clause.

But generally you shouldn't be doing that kind of query work before exposing the data to Power BI. In Power BI you typically bring in each table separately into the Power BI Data Set.

Upvotes: 1

Related Questions