Home » SQL & PL/SQL » SQL & PL/SQL » Not Exists or Not in
Not Exists or Not in [message #251437] Fri, 13 July 2007 10:33 Go to next message
onsite2
Messages: 1
Registered: July 2007
Junior Member
I am trying to add on the bottom of this table insert a where clause that would check to make sure the records don't already exist in the table before inserting. I have a unique key constraint that I need to check for. The unique key is made up of nom_id, gas_day, template_type, daily_detail_row_number, template_row_in_nom

Here's the insert:
INSERT INTO hold_cdd
( daily_detail_id,
gas_day,
nom_id,
link_id,
nom_status,
duplicate_flag,
late_receipt_flag,
contract_id,
service_type,
rate_class,
service_class,
offered_by_party_id,
offered_by_party,
service_sub_type,
service_usage,
overrun_indicator,
offered_to_party_id,
offered_to_party,
from_party_id,
from_party,
from_trading_location_id,
from_trading_location,
from_internal_external,
to_party_id,
to_party,
to_trading_location_id,
to_trading_location,
to_internal_external,
commodity_fuel_link_id,
energy_volume_uom,
nom_rate,
nom_quantity,
gst_flag,
template_type,
daily_detail_row_number,
template_row_in_nom,
from_operator_party_Id,
from_external_party_id,
from_external_account,
to_operator_party_Id,
to_external_party_id,
to_external_account,
from_nom_group_number,
from_nom_group_stream,
to_nom_group_number,
to_nom_group_stream,
supplier_nom_group_number,
used_flag,
service_nom_rule_id,
cdd_type,
summarized_cdd_id,
remark,
create_date_time,
create_user,
confirm_flag,
interruptible_flag,
from_transfer_eba_id,
to_transfer_eba_id,
modified_flag
)
SELECT
current_daily_detail_seq.nextval,
trunc(d_effective_date_hour),
seq_nom_id,
link_id,
'NOT COMPLETED',
'N', -- duplicate_flag,
late_receipt_flag,
contract_id,
service_type,
rate_class,
service_class,
offered_by_party_id,
offered_by_party,
service_sub_type,
service_usage,
overrun_indicator,
offered_to_party_id,
offered_to_party,
from_party_id,
from_party,
from_trading_location_id,
from_trading_location,
from_internal_external,
to_party_id,
to_party,
to_trading_location_id,
to_trading_location,
to_internal_external,
commodity_fuel_link_id,
energy_volume_uom,
nom_rate,
nom_quantity,
gst_flag,
template_type,
daily_detail_row_number,
template_row_in_nom,
from_operator_party_Id,
from_external_party_id,
from_external_account,
to_operator_party_Id,
to_external_party_id,
to_external_account,
from_nom_group_number,
from_nom_group_stream,
to_nom_group_number,
to_nom_group_stream,
supplier_nom_group_number,
used_flag,
service_nom_rule_id,
cdd_type,
null, -- summarized_cdd_id,
remark,
sysdate + pa_gisb.offset,
user,
confirm_flag,
interruptible_flag,
from_transfer_eba_id,
to_transfer_eba_id,
'N'
FROM ZERO_CDD
WHERE nom_id=in_nom_id
AND gas_day=
( SELECT n.start_gas_day
FROM nom n
WHERE n.nom_id = in_nom_id
)
AND NOT( service_type = 'TRANS' AND rate_class = 'NA')

I want to insert from the zero_cdd table only if the records (based on the unique key) do not exist in the hold_cdd table.
Can I use a not exists statement or do I need to create a cursor of the data, loop through the data set and create a nested if statement to check for all the fields in the unique key?

Any help would be appreciated. Thanks.
Re: Not Exists or Not in [message #251441 is a reply to message #251437] Fri, 13 July 2007 11:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Not Exists or Not in [message #251506 is a reply to message #251437] Fri, 13 July 2007 19:41 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If it can be done simply in SQL, it is typically preferable over PL/SQL cursor loops.

If you are using 10g, you can also use the MERGE statement without an UPDATE clause - the effect is to insert only new rows.

Ross Leishman
Previous Topic: Table Design....
Next Topic: Miss Match Check (9i Rel 2)
Goto Forum:
  


Current Time: Wed Dec 07 14:21:40 CST 2016

Total time taken to generate the page: 0.07289 seconds