Home » SQL & PL/SQL » SQL & PL/SQL » NOT EQUAL QUESTION
NOT EQUAL QUESTION [message #212337] Thu, 04 January 2007 15:16 Go to next message
abeiko
Messages: 29
Registered: March 2005
Junior Member
I have a cursor that works fine in selecting everything that is equal, but I also want to update the rows that are not equal by just reversing the cursor. When I change the = sign to <> it doesn't return what I am expecting it should.

The cursor that works fine is as follows:
DECLARE
CURSOR b2
IS
SELECT from_trading_location_id
,from_party_id
,to_party_id
,gas_day
FROM tmp_match_name_changes
WHERE gas_day BETWEEN d_start_date AND d_end_date
AND link_id IS NULL
AND session_id = v_session_id
GROUP BY from_party_id, to_party_id, from_trading_location_id, gas_day
HAVING SUM (DECODE (from_party_id
,offered_to_party_id, nom_quantity
)) =
SUM (DECODE (to_party_id
,offered_to_party_id, nom_quantity
));

How can I get the same cursor to return everything that is not equal in the having clause. The not equal cursor is as follows:

DECLARE
CURSOR b2
IS
SELECT from_trading_location_id
,from_party_id
,to_party_id
,gas_day
FROM tmp_match_name_changes
WHERE gas_day BETWEEN d_start_date AND d_end_date
AND link_id IS NULL
AND session_id = v_session_id
GROUP BY from_party_id, to_party_id, from_trading_location_id, gas_day
HAVING SUM (DECODE (from_party_id
,offered_to_party_id, nom_quantity
)) <>
SUM (DECODE (to_party_id
,offered_to_party_id, nom_quantity
));
Re: NOT EQUAL QUESTION [message #212434 is a reply to message #212337] Fri, 05 January 2007 05:14 Go to previous messageGo to next message
anilsinare
Messages: 22
Registered: December 2005
Location: ipswich, uk
Junior Member
Try using NOT HAVING instead of using "<>"

DECLARE
CURSOR b2
IS
SELECT from_trading_location_id
,from_party_id
,to_party_id
,gas_day
FROM tmp_match_name_changes
WHERE gas_day BETWEEN d_start_date AND d_end_date
AND link_id IS NULL
AND session_id = v_session_id
GROUP BY from_party_id, to_party_id, from_trading_location_id, gas_day
NOT HAVING SUM (DECODE (from_party_id
,offered_to_party_id, nom_quantity
)) =
SUM (DECODE (to_party_id
,offered_to_party_id, nom_quantity
));
Re: NOT EQUAL QUESTION [message #212466 is a reply to message #212434] Fri, 05 January 2007 08:08 Go to previous messageGo to next message
abeiko
Messages: 29
Registered: March 2005
Junior Member
As soon as I add in the NOT in front of HAVING, I get a SQL error message: ORA-00933: SQL command not properly ended.


SELECT from_trading_location_id
,from_party_id
,to_party_id
,gas_day
FROM tmp_match_name_changes
WHERE gas_day BETWEEN TO_DATE('20061009','YYYYMMDD') AND TO_DATE('20061009','YYYYMMDD')
AND link_id IS NOT NULL
AND session_id = 2696342
GROUP BY from_party_id, to_party_id, from_trading_location_id, gas_day
NOT HAVING SUM (DECODE (from_party_id,offered_to_party_id, nom_quantity)) =
SUM (DECODE (to_party_id,offered_to_party_id, nom_quantity));
Re: NOT EQUAL QUESTION [message #212493 is a reply to message #212337] Fri, 05 January 2007 09:46 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

Quote >>
When I change the = sign to <> it doesn't return what I am expecting it should.

>>

Now give sample data, your expected result and the actual result that you are getting for the SQL of your cursor definition.


Srini

Re: NOT EQUAL QUESTION [message #212498 is a reply to message #212493] Fri, 05 January 2007 09:59 Go to previous message
abeiko
Messages: 29
Registered: March 2005
Junior Member
I actually found the problem.

My decode statement needed the else part of it.
See below:

DECLARE
CURSOR b1
IS
SELECT from_trading_location_id
,from_party_id
,to_party_id
,link_id
,gas_day
FROM tmp_match_name_changes
WHERE gas_day BETWEEN d_start_date AND d_end_date
AND link_id IS NOT NULL
AND session_id = v_session_id
GROUP BY link_id, from_party_id, to_party_id, from_trading_location_id, gas_day
HAVING SUM (DECODE (from_party_id
,offered_to_party_id, nom_quantity, -1
)) <>
SUM (DECODE (to_party_id
,offered_to_party_id, nom_quantity, -1
));

Thanks for your help.
Previous Topic: Display ResultSet
Next Topic: Breaking down a string into parts
Goto Forum:
  


Current Time: Sun Dec 11 08:32:12 CST 2016

Total time taken to generate the page: 0.08794 seconds