Databases
StackOverflow2013
Postsdbo
POChanging WHERE clause in CTE stops it working
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POChanging WHERE clause in CTE stops it working
    primarykey
    data
    text
    <p>This works:</p> <pre><code>;WITH CTE AS ( SELECT PT.[description] AS [Transaction Type], Sum (P.original_amount) AS [AMOUNT (£) CREDIT], PR4.[description] AS [Product Type] FROM [X].[dbo].[PAYMENT] P join [X].[dbo].[T1] PT on P.PAYMENT_ID=PT.PAYMENT_TYPE_ID join [X].[dbo].[T2] CN on CN.PAYMENT_TYPE_ID=PT.PAYMENT_TYPE_ID join [X].[dbo].[T3] C on C.[Customer_ID]=CN.[Customer_ID] join [X].[dbo].[T4] SO on SO.PAYMENT_TYPE_ID=P.PAYMENT_TYPE_ID join [X].[dbo].[T5] OI on OI.ORDER_ID=SO.SITE_ORDER_ID join [X].[dbo].[T6] PR4 on PR4.Product_id=OI.PRODUCT_ID join [X].[dbo].[T7]PC4 on PC4.[PRODUCT_CATEGORY_ID]=PR4.[PRODUCT_CATEGORY_ID] Where PC4.DESCRIPTION is not null group by PT.DESCRIPTION, PR4.DESCRIPTION ) select * from CTE </code></pre> <p>This does NOT work:</p> <pre><code>;WITH CTE AS ( SELECT PT.[description] AS [Transaction Type], Sum (P.original_amount) AS [AMOUNT (£) CREDIT], PR4.[description] AS [Product Type] FROM [X].[dbo].[PAYMENT] P join [X].[dbo].[T1] PT on P.PAYMENT_ID=PT.PAYMENT_TYPE_ID join [X].[dbo].[T2] CN on CN.PAYMENT_TYPE_ID=PT.PAYMENT_TYPE_ID join [X].[dbo].[T3] C on C.[Customer_ID]=CN.[Customer_ID] join [X].[dbo].[T4] SO on SO.PAYMENT_TYPE_ID=P.PAYMENT_TYPE_ID join [X].[dbo].[T5] OI on OI.ORDER_ID=SO.SITE_ORDER_ID join [X].[dbo].[T6] PR4 on PR4.Product_id=OI.PRODUCT_ID join [X].[dbo].[T7]PC4 on PC4.[PRODUCT_CATEGORY_ID]=PR4.[PRODUCT_CATEGORY_ID] Where PC4.DESCRIPTION ='VALUEXXXXX' group by PT.DESCRIPTION, PR4.DESCRIPTION ) select * from CTE </code></pre> <p>Only difference is </p> <pre><code>Where PC4.DESCRIPTION ='VALUEXXXXX' </code></pre> <p>The error is</p> <pre><code>Msg 207, Level 16, State 1, Line 25 Invalid column name 'VALUEXXXXX'. </code></pre> <p>What is the syntax problem ?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USQuassnoi
    1. USJames Khan
    plurals
    1. PL
      singulars
      1. LTDuplicate
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POChanging WHERE clause in CTE stops it working
      1. This table or related slice is empty.
      1. VTClose
    2. VO
      singulars
      1. POChanging WHERE clause in CTE stops it working
      1. This table or related slice is empty.
      1. VTClose
    3. VO
      singulars
      1. POChanging WHERE clause in CTE stops it working
      1. This table or related slice is empty.
      1. VTDownMod
    1. COI believe it's not quoted in your original query. Since your batch is shorter than 25 lines this seems to be lost in copypaste.
      singulars
      1. POChanging WHERE clause in CTE stops it working
      1. USQuassnoi
    2. CODon't post the same question twice. Rather edit it and flag for re-opening. There is a good comment by bluefeet on your last question. You haven't answered it yet.
      singulars
      1. POChanging WHERE clause in CTE stops it working
      1. USDaniel Hilgarth
    3. COWhat is the DataType of the [Description] column on [X].[dbo].[T7]? Also, put a space between the table name and alias of PC4. I can't imagine this has anything to do with it, but it's dirty looking. :)
      singulars
      1. POChanging WHERE clause in CTE stops it working
      1. USEric J. Price
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload