Hi Im getting the error
Msg 8156, Level 16, State 1, Line 15 The column 'ACTIONTYPE' was specified multiple times for 'paged'.
when running this query
WITH paged AS
SELECT *, ROW_NUMBER() OVER (ORDER BY DESCRIPTION) AS 'rowNumber'
FULL JOIN IQ_PROMOTIONSITEMS ON IQ_ITEM.NO = IQ_PROMOTIONSITEMS.ITEMNO
WHERE IQ_POMOTIONSITEMS.ITEMNO = IQ_ITEM.NO
AND DONOTSHOW = '0'
AND NO+DESCRIPTION LIKE'%'
SELECT * FROM paged WHERE rowNumber BETWEEN 1 and 10;
is there anyway to exclude columns from the joined table so i dont get this error? or any ideas on how to alter this query
Thanks in advance
asked May 05 '10 at 11:29 AM in Default
You need to specify the column from each table that you want in the CTE definition and not do a "select *", see below. I have aliased the table named to make it a little clearer which columns com from which table. If the column exists in both tables you need to put a column list in place so the query processor knows which one you want to display.
answered May 05 '10 at 04:38 PM
You have ACTIONTYPE column in both tables that you join in the CTE. Modify the query to list all columns that you need explicitly, selecting * is not the best idea.
answered May 05 '10 at 04:35 PM