Home » SQL & PL/SQL » SQL & PL/SQL » HELP! Insert Script
HELP! Insert Script [message #219387] Wed, 14 February 2007 03:57 Go to next message
kipp@plancknet.com
Messages: 25
Registered: September 2006
Junior Member
I have 2 INSERT scripts, they are in the same sql file and run one after the other, ie,
INSERT INTO tableA
...
COMMIT;

INSERT INTO tableA
...
COMMIT;

What I need to do and just figure out is the second insert is almost the same as the first, but I only want to run it if tableA is empty. I need to check it in the WHERE statement as I am not using PL/SQL and cant use IF's.

Can anyone help with this?

Thanks!

Report message to a moderator

Re: HELP! Insert Script [message #219392 is a reply to message #219387] Wed, 14 February 2007 04:11 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
If you are using a select statement while inserting the data you can add the condition on count in the where clause.
Ex:
insert into vamsi_tab (select 1,2 from dual where 0 = (select count(0) from vamsi_tab));
By
Vamsi

Report message to a moderator

Re: HELP! Insert Script [message #219396 is a reply to message #219392] Wed, 14 February 2007 04:26 Go to previous messageGo to next message
kipp@plancknet.com
Messages: 25
Registered: September 2006
Junior Member
Many Thanks! I will give that a shot.
Quick question on your code...
select 1,2 from dual where 0
What does the 1,2 do? and what does the "where 0" do?
Thanks

Report message to a moderator

Re: HELP! Insert Script [message #219398 is a reply to message #219396] Wed, 14 February 2007 04:33 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
HUH!!! That was an example
desc vamsi_tab
Name                           Null     Type                                           
------------------------------ -------- ------
M                                       NUMBER                                               
N                                       NUMBER
In my example I want to insert 1,2 into m,n of vamsi_tab, if there are already no rows. Hence the condition "where 0 = (select count(0) ......)"

If you want us to help, please post your insert statement completely.
By
Vamsi

Report message to a moderator

Re: HELP! Insert Script [message #219400 is a reply to message #219398] Wed, 14 February 2007 04:38 Go to previous messageGo to next message
kipp@plancknet.com
Messages: 25
Registered: September 2006
Junior Member
thanks so much for the super fast reply, that will help me out tremendously.

Report message to a moderator

Re: HELP! Insert Script [message #219490 is a reply to message #219392] Wed, 14 February 2007 11:25 Go to previous messageGo to next message
kipp@plancknet.com
Messages: 25
Registered: September 2006
Junior Member
Ok, what I am tryin to do below, create a blank temp table,
then insert the data into the temp where sysdate-1,
next INSERT the data into the temp table if there was no data inserted using sysdate-1, then insert into the temp tab le using sysdate-2.

CREATE TABLE ODS_BSM_ISSUES_TEMP AS
SELECT * FROM ODS_ISSUE_TRANS WHERE row_num=1;
COMMIT;
DELETE ODS_BSM_ISSUES_TEMP;
COMMIT;
--
INSERT INTO ODS_BSM_ISSUES_TEMP
(seq_num,
doc_num,
nsn,
dashesnsn,
sfx_cd,
qty,
fsc,
stat_dt,
stat_cd,
dodaac,
addrs,
stat_dt_999,
supplemental_address,
multi,
prod_cd,
lbsshipped,
own_purp_cd,
cond_cd,
sig_cd,
supp_addr,
serv_type,
qty_oh,
cd_ind,
display_prod_cd,
display_doc_num,
display_service,
display_stat_dt_999,
display_qty,
display_stat_cd,
display_sfx_cd,
shipto_address,
report_date
)
(SELECT
seq_ods_issue.nextval,
RQN.zzorig_doc_NBR,
RQN.nsn,
substr(RQN.nsn,1,4)||'-'||substr(RQN.nsn,5,2)||'-'||substr(RQN.nsn,7,3)||'-'||substr(RQN.nsn,10,4),
RQN.suffix,
RQN.quantity,
RQN.fsc,
RQN.dob,
RQN.status_cd,
SUBSTR(RQN.zzorig_doc_NBR,1,6),
NVL(DOD.user_address,'NO ADDRESS ON FILE'),
to_char(RQN.dob,'YDDD'),
RQN.ZZSUPP_ADRS,
LK.wt_cmp_number*LK.wt_factor,
LK.display_prod_cd,
LK.wt_cmp_number*RQN.quantity*LK.wt_factor,
NULL,
NULL,
NVL(RQN.zzsig_cd,NULL),
NULL, --- SUPP_
'X', --- LK.SERV_TYPE Pre-populate with dummy
NULL, --- LK.QTY_OH,
LK.cd_ind,
LK.display_prod_cd,
RQN.zzorig_doc_NBR,
'XXXXXXXXXXX', --- display_service Pre-populate with dummy
to_char(RQN.dob,'YDDD'),
RQN.quantity,
RQN.status_cd,
RQN.suffix,
NULL,
to_char(dob,'MONTH YYYY')
FROM ZDOR_RQH RQN,
ODS_ADDRESS DOD,
ODS_NSN_LOOKUP LK
WHERE SUBSTR(RQN.zzorig_doc_NBR,1,6) NOT IN (SELECT ods.dodaac
FROM ODS_EX_DODAAC_LOOKUP ods
WHERE ods.dodaac=substr(RQN.zzorig_doc_NBR,1,6))
AND RQN.nsn = LK.nsn
AND RQN.DATA_DATE = to_date(sysdate-1,'DD-MON-YY')
AND SUBSTR(RQN.zzorig_doc_NBR,1,6) = DOD.dodaac (+));
COMMIT;
--
PROMPT ** Now check the Data_date-2 if there are no records for Data_date-1
--
INSERT INTO ODS_BSM_ISSUES_TEMP
(seq_num,
doc_num,
nsn,
dashesnsn,
sfx_cd,
qty,
fsc,
stat_dt,
stat_cd,
dodaac,
addrs,
stat_dt_999,
supplemental_address,
multi,
prod_cd,
lbsshipped,
own_purp_cd,
cond_cd,
sig_cd,
supp_addr,
serv_type,
qty_oh,
cd_ind,
display_prod_cd,
display_doc_num,
display_service,
display_stat_dt_999,
display_qty,
display_stat_cd,
display_sfx_cd,
shipto_address,
report_date
)
(SELECT
seq_ods_issue.nextval,
RQN.zzorig_doc_NBR,
RQN.nsn,
substr(RQN.nsn,1,4)||'-'||substr(RQN.nsn,5,2)||'-'||substr(RQN.nsn,7,3)||'-'||substr(RQN.nsn,10,4),
RQN.suffix,
RQN.quantity,
RQN.fsc,
RQN.dob,
RQN.status_cd,
SUBSTR(RQN.zzorig_doc_NBR,1,6),
NVL(DOD.user_address,'NO ADDRESS ON FILE'),
to_char(RQN.dob,'YDDD'),
RQN.ZZSUPP_ADRS,
LK.wt_cmp_number*LK.wt_factor,
LK.display_prod_cd,
LK.wt_cmp_number*RQN.quantity*LK.wt_factor,
NULL,
NULL,
NVL(RQN.zzsig_cd,NULL),
NULL, --- SUPP_
'X', --- LK.SERV_TYPE Pre-populate with dummy
NULL, --- LK.QTY_OH,
LK.cd_ind,
LK.display_prod_cd,
RQN.zzorig_doc_NBR,
'XXXXXXXXXXX', --- display_service Pre-populate with dummy
to_char(RQN.dob,'YDDD'),
RQN.quantity,
RQN.status_cd,
RQN.suffix,
NULL,
to_char(dob,'MONTH YYYY')
FROM ZDOR_RQH RQN,
ODS_ADDRESS DOD,
ODS_NSN_LOOKUP LK
WHERE (SELECT * FROM dual
WHERE 0=(SELECT COUNT(0) FROM tableA))
AND SUBSTR(RQN.zzorig_doc_NBR,1,6) NOT IN (SELECT ods.dodaac
FROM ODS_EX_DODAAC_LOOKUP ods
WHERE ods.dodaac=substr(RQN.zzorig_doc_NBR,1,6))
AND RQN.nsn = LK.nsn
AND RQN.DATA_DATE = to_date(sysdate-2,'DD-MON-YY')
AND SUBSTR(RQN.zzorig_doc_NBR,1,6) = DOD.dodaac (+));
COMMIT;

Report message to a moderator

Re: HELP! Insert Script [message #219511 is a reply to message #219490] Wed, 14 February 2007 12:47 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
kipp@plancknet.com wrote on Wed, 14 February 2007 12:25
...
AND RQN.DATA_DATE = to_date(sysdate-1,'DD-MON-YY')
...
AND RQN.DATA_DATE = to_date(sysdate-2,'DD-MON-YY')



As I have told you before, but I guess you do not listen.
sysdate is already a DATE and hence, you do not use TO_DATE on it.

[Updated on: Wed, 14 February 2007 12:49]

Report message to a moderator

Previous Topic: Tuning PL/SQL with Collections
Next Topic: getting the number of current connections
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Dec 04 18:51:44 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.