In future practice, try avoid use of *, u wld rather use the column names because the use of * adds to the time of execution since the db has to interprate the * rather than being specific on what you want.
in this case jus try selecting one column since u just want to see if exist, u cld get the primary key.
the other thing is avoid sub-queries, u are better off with join. this becomes clear as u pull data from a many tables.!!
This drills down to ua designing of the db structure.....
INSERT INTO REPORT() SELECT custnum,region,status,invoice,inv_date,price,discount
FROM INVOICE i
WHERE EXISTS(select Pk_Field from vw_use u where i.custnum=u.custnum)
AND NOT EXISTS (select Pk_Field from vw_charge c where i.invoice=c.invoice)
On Fri, May 22, 2009 at 4:11 PM, Chris Mwirigi
<mwirigic@gmail.com> wrote:
try joins
insert into reports select
custnum,region,status,invoice,inv_date,price,discount from invoice i
inner join vw_use u on i.custnum = i.custnum left join vw_charge c on
i.invoice = c.invoice where 1 having invoice is null;
--
Regards
Mathu
http://sojjar.blogspot.com