SQL: Delete records based on a calculated field

[Liviathan]

SELECT ME.MtgEventID, E.EventDesc, E.EventSDesc, ME.MtgEventQty MtgEventQty,
NVL(SUM(RME.EventQty), 0) TotalDistributed, ME.MtgEventQty - NVL(SUM(RME.EventQty), 0) AvailQty, ME.ModDt
FROM MtgEvent ME, Event E, RegMtgEvent RME
WHERE ME.EventID = E.EventID
AND ME.MtgID = 10 -- 2005 RI Convention
AND ME.MtgEventID = RME.MtgEventID (+)
GROUP BY ME.MtgEventID, E.EventDesc, E.EventSDesc, ME.MtgEventQty, ME.ModDt
order by E.EventDesc


This is my query...I need to delete records where AvailQTY=0.

 

[Beau]

use the equation in the where clause instead of the fieldname.

 

[Liviathan]

SELECT ME.MtgEventID, E.EventDesc, E.EventSDesc, ME.MtgEventQty MtgEventQty,
NVL(SUM(RME.EventQty), 0) TotalDistributed, ME.MtgEventQty - NVL(SUM(RME.EventQty), 0) AvailQty, ME.ModDt
FROM MtgEvent ME, Event E, RegMtgEvent RME
WHERE ME.EventID = E.EventID
AND ME.MtgID = 10 -- 2005 RI Convention
AND ME.MtgEventID = RME.MtgEventID
AND ME.MtgEventQty - NVL(SUM(RME.EventQty), 0) =0
GROUP BY ME.MtgEventID, E.EventDesc, E.EventSDesc, ME.MtgEventQty, ME.ModDt
order by E.EventDesc


And get this:
12:24:30 PM ORA-00934: group function is not allowed here

 

[MrChad]

What table are you deleting from? You have a three-way join in that WHERE clause.

 

[Liviathan]

Let me restart...the top query retuns a list of events, the tickets, how many have been sold and amount left.

There is another table called mtgeventonline that has a wait list, it has the eventID. I need to delete from that table, events that have 0 tickets available, which is what the top query returns.

 

[MrChad]

DELETE FROM mtgeventonline
WHERE EventID IN
(SELECT MtgEventID from
(SELECT ME.MtgEventID, ME.MtgEventQty - NVL(SUM(RME.EventQty), 0) AvailQty
FROM MtgEvent ME, Event E, RegMtgEvent RME
WHERE ME.EventID = E.EventID
AND ME.MtgID = 10 -- 2005 RI Convention
AND ME.MtgEventID = RME.MtgEventID
AND ME.MtgEventQty - NVL(SUM(RME.EventQty), 0) =0
GROUP BY ME.MtgEventID
HAVING ME.MtgEventQty - NVL(SUM(RME.EventQty), 0) = 0)
)

 

[Liviathan]

The fist line is what always throws me off...it returns 1:29:35 PM ORA-00934: group function is not allowed here

And thats where I get stuck. Thanks for your help.


AND ME.MtgEventQty - NVL(SUM(RME.EventQty), 0) =0
GROUP BY ME.MtgEventID
HAVING ME.MtgEventQty - NVL(SUM(RME.EventQty), 0) = 0)) AND paidyn = 'N'

 

[MrChad]

Sorry, I didn't paste the SQL from your original post

DELETE FROM mtgeventonline
WHERE EventID IN
(SELECT MtgEventID from
(SELECT ME.MtgEventID, ME.MtgEventQty - NVL(SUM(RME.EventQty), 0) AvailQty
FROM MtgEvent ME, Event E, RegMtgEvent RME
WHERE ME.EventID = E.EventID
AND ME.MtgID = 10 -- 2005 RI Convention
AND ME.MtgEventID = RME.MtgEventID (+)
GROUP BY ME.MtgEventID
HAVING ME.MtgEventQty - NVL(SUM(RME.EventQty), 0) = 0)
)

 

[MrChad]

Ok, try this:

DELETE FROM mtgonlineevent
WHERE paidyn = 'N' AND mtgeventid IN
(SELECT MtgEventID from
(SELECT ME.MtgEventID, ME.MtgEventQty MtgEventQty, ME.MtgEventQty - NVL(SUM(RME.EventQty), 0) AvailQty
FROM MtgEvent ME, Event E, RegMtgEvent RME
WHERE ME.EventID = E.EventID
AND ME.MtgID = 10 -- 2005 RI Convention
AND ME.MtgEventID = RME.MtgEventID (+)
GROUP BY ME.MtgEventID, ME.MtgEventQty
HAVING ME.MtgEventQty - NVL(SUM(RME.EventQty), 0) = 0))

 

[Liviathan]

Hhaa it ran...but 0 records deleted, when it should delete 323. weird

 

[Liviathan]

NM...It worked.

Thanks

 

[MrChad]

See if

SELECT ME.MtgEventID, ME.MtgEventQty MtgEventQty, ME.MtgEventQty - NVL(SUM(RME.EventQty), 0) AvailQty
FROM MtgEvent ME, Event E, RegMtgEvent RME
WHERE ME.EventID = E.EventID
AND ME.MtgID = 10 -- 2005 RI Convention
AND ME.MtgEventID = RME.MtgEventID (+)
GROUP BY ME.MtgEventID, ME.MtgEventQty
HAVING ME.MtgEventQty - NVL(SUM(RME.EventQty), 0) = 0

returns any records

EDIT: Sweet, glad to see that it worked.

 

[Liviathan]

Thanks for all your help!!!

Got stuck, couldn't find the solution.