0

I have to identify a set of lines to be deleted from a table and here is the logic I used to determine the scope to be deleted.

I have 2 tables AAA and BBB using Sybase ASE.

AAA has 2 columns : a date, a label and a price BBB has 3 columns : a date and a label

I use the below query to determine the dates that are in AAA but not in B

SELECT DATE1 FROM AAA WHERE LABEL='toto' GROUP BY DATE1
EXCEPT
SELECT DATE1 FROM BBB WHERE LABEL='toto'

I'd like to reuse the above query to identify the scope a line to be deleted from AAA

I'd like to run something like the below :

SELECT *
FROM AAA
WHERE LABEL='TOTO'
AND DATE1 in (
     (SELECT DATE1 FROM AAA WHERE LABEL='toto' GROUP BY DATE1)
     EXCEPT
     (SELECT DATE1 FROM BBB WHERE LABEL='toto'))

Running this query raised the below error :

Incorrect syntax near the keyword 'EXCEPT'. [SQL State=ZZZZZ, DB Errorcode=156] Next: Incorrect syntax near ')'. [SQL State=42000, DB Errorcode=102]

I have identified what the error exactly is.

Hence my questions are :

  • Can someone help me to identify the error ?
  • Is my idea the right one to perform what I want ?
  • If not, could you please share the best practice ?

Thanks !

Running this query raised the below error :

Incorrect syntax near the keyword 'EXCEPT'. [SQL State=ZZZZZ, DB Errorcode=156] Next: Incorrect syntax near ')'. [SQL State=42000, DB Errorcode=102]

Davo
  • 1
  • does your first query (`SELECT / EXCEPT / SELECT`) run or does it also generate an error? also, please update the question with your ASE version (`select @@version`) – markp-fuso Dec 19 '22 at 19:29
  • I don't have an ASE 16.x instance in front of me at the minute so fwiw, try remving the excess parents from the subquery, ie, try just the one set of parens ... `SELECT... and DATE1 in (SELECT ... EXCEPT ... SELECT ...)` – markp-fuso Dec 19 '22 at 19:31
  • @markp-fuso here are the answer to your question : - the first query (select / except / select) runs properly - select @@version returns : Adaptive Server Enterprise/16.0 SP04 PL02/EBF 29987 SMP/P/x86_64/SLES 12.4/ase160sp04pl02x/3545/64-bit/FBO/Thu Mar 17 21:44:05 2022 – Davo Dec 20 '22 at 08:56

1 Answers1

0

Or better yet:

SELECT *
FROM AAA
WHERE LABEL='TOTO'
AND DATE1 in (
     SELECT DATE1 FROM AAA WHERE LABEL='toto' 
     EXCEPT
     SELECT DATE1 FROM BBB WHERE LABEL='toto')
access_granted
  • 1,807
  • 20
  • 25