CODEWITHSUNDEEP
sql-serversql-server-2008
Dim
Dim

Reputation: 453

Column name doesn't exist in CTE statement

I'm trying to create a CTE statement:

WITH StartCash (StartCash) AS
(
    SELECT StartCash 
    FROM CashierInfo 
    WHERE CashierID = (SELECT MAX( CashierID) 
                       FROM CashierInfo  
                       WHERE UserID = 1 AND EndDate IS NULL)
)
SELECT
    StartCash, a.username AS Username, b.Adress AS Adress,
    (SUM(c.quantity * c.discountprice)) AS SumPrice,
    c.Printed AS Printed, c.CashierUserID AS CashierUserID,
    c.RetailDelivery AS RetailDelivery, c.TrnDocumentID 
FROM
    Users a 
JOIN
    InventoryTransTemp c ON c.CashierUserID = a.UserID 
JOIN
    DeliveryAdress b ON b.DeliveryAdressID = c.DeliveryAdressID 
WHERE
    c.cashieruserid =  1 
GROUP BY
    a.Username, b.Adress, c.Printed, 
    c.CashierUserID, c.RetailDelivery, c.TrnDocumentID

But I am getting an error

Invalid column name 'StartCash'

Upvotes: 2

Views: 1587

Answers (2)

Brien Foss
Brien Foss

Reputation: 3367

StartCash does not exist because your CTE is not included in your FROM clause.

Based solely on what I can see in your question, I assume it would be safe to JOIN CashierInfo.UserID to Users.UserID. So try something like this:

WITH StartCash
AS (
    SELECT StartCash
        ,UserID
    FROM CashierInfo
    WHERE CashierID = (
            SELECT MAX(CashierID)
            FROM CashierInfo
            WHERE UserID = 1
                AND EndDate IS NULL
            )
    )
SELECT sc.StartCash
    ,a.username AS Username
    ,b.Adress AS Adress
    ,(SUM(c.quantity * c.discountprice)) AS SumPrice
    ,c.Printed AS Printed
    ,c.CashierUserID AS CashierUserID
    ,c.RetailDelivery AS RetailDelivery
    ,c.TrnDocumentID
FROM StartCash sc
INNER JOIN Users a ON sc.UserID = a.UserID
INNER JOIN InventoryTransTemp c ON c.CashierUserID = a.UserID
INNER JOIN DeliveryAdress b ON b.DeliveryAdressID = c.DeliveryAdressID
WHERE c.cashieruserid = 1
GROUP BY sc.StartCash
    ,a.Username
    ,b.Adress
    ,c.Printed
    ,c.CashierUserID
    ,c.RetailDelivery
    ,c.TrnDocumentID

Upvotes: 2

Joe
Joe

Reputation: 146

You do not have the CTE in the from clause. You need to join to the CTE but your CTE has only a summary value. https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017

Upvotes: 0

Related Questions