on 11-05-2021 3:49 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.