Custom Query 'Must specify table to select from'

[former_member675796]

Hi,

I hope everyone's having a nice day!

I am trying to create a custom query that allows a user to check the number of products sold with specific item configurations. I include the query below. My issue is that I am receiving the error 'Must specify table to select from' when I try and run the query. What I would expect is to see a graphical user interface window with textboxes allowing a user to enter the Item Codes of the item combinations they want to check? What am I doing wrong?

Thanks very much....

Here is the query:

SELECT h.DocNum, h.DocDate
FROM ORDR h
INNER JOIN RDR1 r ON h.DocEntry = r.DocEntry
AND r.DocEntry IN (SELECT DISTINCT DocEntry
FROM RDR1
WHERE ItemCode IN ('[%1]', '[%2]'
, '[%3]', '[%4]'
, '[%5]', '[%6]'))
WHERE r.ItemCode = '[%0]'
GROUP BY h.DocNum, h.DocDate ORDER BY DocDate DESC

[kothandaraman_nagarajan · Accepted solution]

Hi,

Small changes from above query,

DECLARE @Item0 nvarchar(50), @Item1 nvarchar(50), @Item2 nvarchar(50), @Item3 nvarchar(50), @Item4 nvarchar(50), @Item5 nvarchar(50), @Item6 nvarchar(50)

SET @Item0 = (SELECT T0.[ItemCode] FROM OITM T0 WHERE T0.[ItemCode] ='[%0]')

SET @Item1 = (SELECT T0.[ItemCode] FROM OITM T0 WHERE T0.[ItemCode] ='[%1]')

SET @Item2 = (SELECT T0.[ItemCode] FROM OITM T0 WHERE T0.[ItemCode] ='[%2]')

SET @Item3 = (SELECT T0.[ItemCode] FROM OITM T0 WHERE T0.[ItemCode] ='[%3]')

SET @Item4 = (SELECT T0.[ItemCode] FROM OITM T0 WHERE T0.[ItemCode] ='[%4]')

SET @Item5 = (SELECT T0.[ItemCode] FROM OITM T0 WHERE T0.[ItemCode] ='[%5]')

SET @Item6 = (SELECT T0.[ItemCode] FROM OITM T0 WHERE T0.[ItemCode] ='[%6]')

SELECT h.DocNum, h.DocDate

FROM ORDR h INNER JOIN RDR1 r ON h.DocEntry = r.DocEntry AND r.DocEntry IN (SELECT DISTINCT DocEntry FROM RDR1 WHERE ItemCode IN (@Item1, @Item2, @Item3, @Item4, @item5, @item6)) WHERE r.ItemCode = @Item0

GROUP BY h.DocNum, h.DocDate

ORDER BY DocDate DESC

Regards,

Nagarajan

[kothandaraman_nagarajan]

Close this thread by marking correct answer.

[former_member675796]

This works brilliantly.

Thanks loads for this guys.

Really was banging my head against the wall trying to understand why I was getting that error!

[alejandrovasqzgt]

In my experience, SAP won't let you use subquerys with parameters on it.

Try this:

DECLARE @Item0 nvarchar(50), @Item1 nvarchar(50), @Item2 nvarchar(50), @Item3 nvarchar(50), @Item4 nvarchar(50), @Item5 nvarchar(50), @Item6 nvarchar(50)

SELECT @Item0 = T0.[ItemCode] FROM OITM T0 WHERE T0.[ItemCode] =[%0]

SELECT @Item1 = T0.[ItemCode] FROM OITM T0 WHERE T0.[ItemCode] =[%1]

SELECT @Item2 = T0.[ItemCode] FROM OITM T0 WHERE T0.[ItemCode] =[%2]

SELECT @Item3 = T0.[ItemCode] FROM OITM T0 WHERE T0.[ItemCode] =[%3]

SELECT @Item4 = T0.[ItemCode] FROM OITM T0 WHERE T0.[ItemCode] =[%4]

SELECT @Item5 = T0.[ItemCode] FROM OITM T0 WHERE T0.[ItemCode] =[%5]

SELECT @Item6 = T0.[ItemCode] FROM OITM T0 WHERE T0.[ItemCode] =[%6]

SET @Item0 = '[%0]'

SET @Item1 = '[%1]'

SET @Item2 = '[%2]'

SET @Item3 = '[%3]'

SET @Item4 = '[%4]'

SET @Item5 = '[%5]'

SET @Item6 = '[%6]'

SELECT h.DocNum, h.DocDate FROM ORDR h INNER JOIN RDR1 r ON h.DocEntry = r.DocEntry AND r.DocEntry IN (SELECT DISTINCT DocEntry FROM RDR1 WHERE ItemCode IN (@Item1, @Item2, @Item3, @Item4, @item5, @item6)) WHERE r.ItemCode = @Item0 GROUP BY h.DocNum, h.DocDate ORDER BY DocDate DESC