question

peter avatar image
peter asked

Incorrect syntax near the keyword 'EXISTS'.

Greetings.

I think i might be getting too adventurous.

can anyone see an issue with the following. The other select works on its own and the inner select works. there are matching values of the OPBEL field but i get this error message.

Incorrect syntax near the keyword 'EXISTS'.

DECLARE @START DATETIME
DECLARE @END DATETIME

SET @START = '20100101'
SET @END = '20100131'

DECLARE @GROSSINVOICED REAL
DECLARE @REVERSED REAL
DECLARE @INVOICED REAL

-------------------------------------------------------------

-- Determine GROSS INVOICED $
SELECT D.* FROM [SAP_Reporting].[dbo].[DFKKOP] as D 
WHERE D.[BUDAT] BETWEEN @START AND @END
AND D.[HVORG] IN ('0008','0009') 

WHERE D.[OPBEL] EXISTS 
(SELECT F.* 
FROM [SAP_Reporting].[dbo].[FKKMAZE] AS F
WHERE EXISTS  
(SELECT F1.[OPBEL],MAX(F1.[AUSDT])
FROM [SAP_Reporting].[dbo].[FKKMAZE] AS F1
GROUP BY F1.[OPBEL])
AND F.[MAHNS] = '1');

many thanks

exists
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered

Your query is not well formatted, although I can see

WHERE D.[OPBEL] EXISTS (SELECT F.* FROM [SAP_Reporting].[dbo].[FKKMAZE]....)

You cannot use WHERE field EXISTS(select)

Maybe you wanted something like

WHERE D.[OPBEL] IN (SELECT field FROM [SAP_Reporting].[dbo].[FKKMAZE]....)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for your guidance. I initially had the IN but SQL came back with an error that indicated that i should use EXISTS. I perservered with that until i sought help. Thanks for pointing out the extra WHERE. Matt has given me a suggestion that I have just tried. It has returned a result but now i need to see if it is the result that I need. Once again many thanks Peter
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered

You are trying to use a correlated sub query - but

a) you don't have the correlation inside the sub query
and
b) you have two WHERE clauses

What you probably want is... Pavel's answer that he's just written! +1 Pavel

edit -> I've just noticed about Pavel not picking up on the two where clauses... so...

-- Determine GROSS INVOICED $
SELECT D.* FROM [SAP_Reporting].[dbo].[DFKKOP] as D 
WHERE D.[BUDAT] BETWEEN @START AND @END
AND D.[HVORG] IN ('0008','0009')     
AND D.[OPBEL] IN 
(SELECT F.[OPBEL] 
FROM [SAP_Reporting].[dbo].[FKKMAZE] AS F
WHERE EXISTS  
(SELECT F1.[OPBEL],MAX(F1.[AUSDT])
FROM [SAP_Reporting].[dbo].[FKKMAZE] AS F1
GROUP BY F1.[OPBEL])
AND F.[MAHNS] = '1');

Also - this bit of the query makes no sense:

WHERE EXISTS  
(SELECT F1.[OPBEL],MAX(F1.[AUSDT])
FROM [SAP_Reporting].[dbo].[FKKMAZE] AS F1
GROUP BY F1.[OPBEL])

Because you're not selecting anything from F1 that is matched to F, you are basically including everything if there are any rows at all included by the inner query - i.e. give me all rows from FKKMAZE where MAHNS = 1 if there are any rows at all in FKKMAZE.

When correlating, you need to reference the outer query in the inner one - for example

SELECT * FROM [sys].[tables] t
WHERE EXISTS 
(SELECT * FROM [sys].[columns] WHERE name = 'ID' and object_id = t.object_id)

This query is saying - give me all the tables where the table has a column called 'ID'

If you don't include the inner reference, then you are asking a different question entirely - for example (note the missing 'and object_id = t.object_id'):

SELECT * FROM [sys].[tables] t
WHERE EXISTS 
(SELECT * FROM [sys].[columns] WHERE name = 'ID')

This query is saying - give me all the tables if there are any columns in the database called 'ID'

As an aside - what is it with SAP and stupidly named columns with 5 letter acronyms?

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Matt. I have run the query as you have amended it and it has returned a result. I'm now just trying to see if it is the "correct" result. At first glance the resultant seems a bit small. DFKKOP & FKKMAZE are linked by OPBEL. There is one instance in DFKKOP and multiple in FKKMAZE each with an incresing date value in AUSDT. I need to select all in DFKKOP and single corresponding FKKMAZE record which has the latest MAX date entry. Yes this is my first tangle with SAP and whilst i'm sure the table names make sense to the little elves in the black forest they take a bit of deciphering
0 Likes 0 ·
johnsoftek avatar image
johnsoftek answered
select *   
  from (SELECT D.*,     
               F.AUSDT,   
               row_number()  
                 over(partition by d.mandt,   
                                   d.opbel,   
                                   d.opupw,   
                                   d.opupk,   
                                   d.opupz  
                          order by f.ausdt desc) as grp_rn  
          FROM [SAP_Reporting].[dbo].DFKKOP  D    
          join [SAP_Reporting].[dbo].FKKMAZE F   
            on d.mandt = f.mandt   
           and d.opbel = f.opbel   
           and d.opupw = f.opupw   
           and d.opupk = f.opupk    
           and d.opupz = f.opupz   
         WHERE D.BUDAT]BETWEEN @START AND @END    
           AND D.HVORG IN ('0008','0009')        
           AND F.MAHNS = '1')  
 where grp_rn = 1;  
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.