cancel
Showing results for 
Search instead for 
Did you mean: 

Where condition not working on PIVOT/CTE

lsauser
Participant
0 Kudos

Hi all,

Probably something simple but im stumped at the moment.

In my following code the WHERE condition is not working when its like this.

declare @column as varchar(max)
declare @Query as varchar(max)
set @column= STUFF((Select '],['+ CAST(DocNum AS VARCHAR) from OPOR WHERE Comments LIKE '%%Comm%%' AND DocStatus = 'O' FOR XML PATH('')),1,2,'') +']'
set @Query='SELECT * FROM
(
SELECT
W.ItemCode,
CAST(q.DocNum AS VARCHAR) AS DocNum,
sum(w.Quantity) Stock
FROM
POR1 W
INNER JOIN OPOR Q ON Q.DocEntry = W.DocEntry
WHERE W.ItemCode LIKE 'CD%%'
GROUP BY W.ItemCode,Q.DocNum
) P
Pivot (
Sum(Stock)
FOR
DocNum in ('+@column+')) AS pvtb'
EXECUTE (@Query)

However if I change it to something like 'WHERE W.Quantity > 1' It works perfectly fine. Looking to filter out the items based on item code.

Any help is appreciated.

Nick

Accepted Solutions (1)

Accepted Solutions (1)

LoHa
Active Contributor
0 Kudos

Hi Nick,

please try

WHERE W.ItemCode LIKE ''' + 'CD%%' + '''

If you want to test your query. Try

SELECT @Query

Instead of

Execute (@Query)

then you get the string and you can have a look what is missing

regards Lothar

lsauser
Participant
0 Kudos

Thanks for the answer Lothar, worked like a charm.

Additionally, is there a simple way to include regular columns into this dynamic query? For example this above query will give me 3 purchase order columns at the moment, but before those I would like to have OnHand and Commited stock for each row item as well.

Happy to move this to a new question if need be.

Answers (1)

Answers (1)

LoHa
Active Contributor
0 Kudos

Hi Nick

you can try it like this

SELECT 
        W.ItemCode,
	O.OnHand,
	O.IsCommited,
        CAST(q.DocNum AS VARCHAR) AS DocNum,
        sum(w.Quantity) Stock
FROM 
POR1 W
INNER JOIN OPOR Q ON Q.DocEntry = W.DocEntry
INNER JOIN OITM O ON O.ItemCode = W.ItemCode
WHERE W.ItemCode LIKE ''' + 'OPT%%' + '''
GROUP BY W.ItemCode,Q.DocNum,O.OnHand,O.IsCommited

regards Lothar

lsauser
Participant
0 Kudos

Awesome thanks heaps Lothar.

Such a simple solution I should have been able to figure this one out!

LoHa
Active Contributor
0 Kudos

You are welcome