3

I am new to SQL, I tried to create a CTE, but I get a red squiggle under the AS and I am getting an error message in the process of executing the syntax:

Msg 102, Level 15, State 1, Line 117 Incorrect syntax near ')'.

This is the SQL syntax, any help would be great:

WITH CTE_Grand_Harvest_Total AS (
SELECT 
    zardi, COUNT(zardi) AS Count_ZARDI, SUM(Weight) AS Sum_Weight, Total_Harvest, Total_Harvest*COUNT(zardi)*SUM(Weight) AS Grand_Total
FROM 
    S1_PH_CROPS_clean_final_minus_money_matters
WHERE  
    Work.dbo.S1_PH_CROPS_clean_final_minus_money_matters.cropNamePH = 'Maize'
GROUP BY 
    Total_Harvest, zardi
)

1 Answer 1

6

A CTE (common table expression) is actually only part of a SELECT statement. It's something you define before you actually SELECT data. You could look at it as a kind of pre-selection of data before you are actually retrieving data from that subset of data (from the CTE).

You can see this in the Microsoft Learn article explaining the SELECT statement:

<SELECT statement> ::=    
    [ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> [,...n] ] } ]  -- <== The CTE bit
    <query_expression>   
    [ ORDER BY <order_by_expression> ] 
    [ <FOR Clause>]   
    [ OPTION ( <query_hint> [ ,...n ] ) ]   
<query_expression> ::=   
    { <query_specification> | ( <query_expression> ) }   
    [  { UNION [ ALL ] | EXCEPT | INTERSECT }  
        <query_specification> | ( <query_expression> ) [...n ] ]   
<query_specification> ::=   
SELECT [ ALL | DISTINCT ]   -- <======== ACTUAL START OF A STANDARD SELECT
    [TOP ( expression ) [PERCENT] [ WITH TIES ] ]   
    < select_list >   
    [ INTO new_table ]   
    [ FROM { <table_source> } [ ,...n ] ]   
    [ WHERE <search_condition> ]   
    [ <GROUP BY> ]   

Reference:
SELECT (Transact-SQL) (Microsoft Learn)
WITH common_table_expression (Transact-SQL) (Microsoft Learn)

You have to select the data from your CTE, similar to this:

WITH CTE_Grand_Harvest_Total AS (
SELECT 
    zardi, COUNT(zardi) AS Count_ZARDI, SUM(Weight) AS Sum_Weight, Total_Harvest, Total_Harvest*COUNT(zardi)*SUM(Weight) AS Grand_Total
FROM 
    S1_PH_CROPS_clean_final_minus_money_matters
WHERE  
    Work.dbo.S1_PH_CROPS_clean_final_minus_money_matters.cropNamePH = 'Maize'
GROUP BY 
    Total_Harvest, zardi
)
SELECT * FROM CTE_Grand_Harvest_Total;

Then you are compliant with the SELECT and CTE definitions.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.