Skip to Content
author's profile photo Alex Jermy
0
Alex Jermy

Custom Query 'Must specify table to select from'

Posted on Jul 30, 2020 at 04:15 PM 65 Views

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    author's profile photo Nagarajan K
    Nagarajan K
    Posted on Jul 31, 2020 at 02:52 AM
    1

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Manuel Vasquez
    Manuel Vasquez
    Posted on Jul 30, 2020 at 05:14 PM
    0

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Alex Jermy
    Alex Jermy
    Posted on Aug 03, 2020 at 09:30 AM
    0

    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!

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Nagarajan K
    Nagarajan K
    Posted on Aug 03, 2020 at 01:32 PM
    0

    Close this thread by marking correct answer.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.