Reference a CTE From Another CTE

Other SQL Server Topics
1

Can I use a CTE inside another CTE?

I created CTE_1, tested, works
I created another CTE Using CTE_1 as the source table, but I get error 'unexpected CTE_2'

Any ideas?
Thanks

2

Yes.


;WITH cte1 AS (
    SELECT ...
    FROM dbo.sometable
),
cte2 AS (
    SELECT ...
    FROM cte1
),
cte3 AS (
    SELECT ...
   FROM cte2
) ...
1 Like
3

Thanks - what I thought
Any clues what might be wrong I keep getting error message "unexpected 'cte_product'"
The 1st CTE works as expected.

Thanks

// Get Order Nbr
with cte_order
    (Market_Name
    ,Cost_Center
    ,Region
    ,Profit_Center
    ,GM
    ,Product_Category
    ,Product_Sub_Category
    ,XCode_3
    ,XCode_2
    ,XCode_1
    ,Status_Code
    ,Trx_Date
    ,Gross_Revenue
    ,Net_Revenue
    ,Is_Cash
    ,Is_Rejected
    ,Was_Credit
    ,Is_Credited
    ,Customer_Id
    ,Customer
    ,Order_Created_Date
    ,Order_Nbr
    ,Sales_Associate
    ,Row_Nbr
    )
    as
    (
    select
        lr.market_name
        , lr.cost_center
        , lr.region
        , lr.profit_center
        , lr.gm
        , lr.product_category
        , lr.product_sub_category
        , lr.xcode_3
        , lr.xcode_2
        , lr.xcode_1
        , lr.status_code
        , lr.trx_date
        , lr.gross_revenue
        , lr.net_revenue
        , lr.is_cash
        , lr.is_rejected
        , lr.was_credit
        , lr.is_credited
        , lr.customer_id
        , lr.customer
        , lr.order_created_date
        , lr.order_nbr
        , lr.sales_associate
        , row_number()
            over (partition by lr.order__ order by lr.order__ )"Row_Nbr"
        from
            table.public.revenue lr
 
    )
 
//Get Product
    cte_product (Order_Nbr,Product)
    as
    (
    select
        o.order_nbr
        ,case
            when (
                    contains(o.xcode_2, '(NS)')
                    or contains(o.xcode_3, '(NS)')
                    or o.xcode_1 = 'NON-SLS'
                )
            then 'Non-Spot'
            when o.cost_center = 'Alpha' then 'Alpha'
            when o.xcode_2 = '(Dig) OTT' then 'Bravo'
            when contains(o.status_code, 'Charlie') then 'Charlie'
                                                when contains(o.xcode_2, '(Dig)') then 'Delta'
            else 'Other'
        end
        "Product"
from
    cte_order o
)
select *
from cte_product
4

Missing comma between CTEs.

image

That big, red abomination is supposed to be a comma.

1 Like
5

@SqlHippo

Thanks! that resolved it.

1 Like