Incorrect syntax near the keyword 'EXISTS'.

Question

question

peter asked Mar 17, '10

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

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

Answer 1 · 2010-03-17T04:13:05Z

Pavel Pawlowski answered Mar 17, '10

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

10 |1200

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

peter commented · Mar 17, 2010 at 07:07 PM

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 ·

Answer 2 · 2010-03-17T04:14:15Z

Matt Whitfield answered Mar 17, '10

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

10 |1200

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

peter commented · Mar 17, 2010 at 07:25 PM

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 ·

Answer 3 · 2010-04-09T01:40:51Z

johnsoftek answered Apr 9, '10

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

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

question