Re: Select Queries

Arthur Merar schrieb:
>
> Hello,
>
> I am having trouble creating a query that will select all records in
> one table that do NOT have a matching record in another table.....
>
> I tried something like this:
>
> Insert into INT_POHEADER
> ponum, podate
> (Select * from TMP_POHEADER where
> ponum NOT IN (select * from POHEADER))
>
> Can anyone help me out?
>

Assuming TMP_POHEADER has a field field1 that doesn't exist in POHEADER, your querry could look like (1) which will let you use indexes.

(1) 	Select 
		t.ponum, t.podate 
	from 
		TMP_POHEADER t,
		POHEADRE p 
	where
		t.ponum = p.ponum(+)
	and
		t.field1 is null
	;

Otherwise use (2) which might not use indexes and therefor takes more time:

(2) 	Select 
		t.ponum, t.podate 
	from 
		TMP_POHEADER 
	where
 		t.ponum NOT IN 
			(select 
				ponum 
			from 
				POHEADER)

HTH
Matthias
--
grema_at_t-online.de

Es gibt nichts Neues mehr.
Alles, was man erfinden kann, ist schon erfunden worden. Charles H. Duell, Leiter des US Patentamtes bei seinem Rücktritt 1899 Received on Thu Mar 04 1999 - 01:27:46 CST