On Wed, 2 Jun 1999, Banerjee, Poolak wrote:
> Hi Ari,
>
> I need a quick help.
>
> I have a table lot-header ('desc'ribed below). I have to make a query which
> will bring all the parentlotid (unique) and some other fields where the
> lotstarttime is NULL.
>
> For the data set shown below, I expect the query will bring (2 records) only
> P1 and P2 as parentlots. It will ignore P3 as atleast one lotid (L18 here)
> for the parentlot P3 has non-null lotstarttime.
>
> I tried the following one but gettingvery erratic values.
>
>
> 1 select unique(parentlotid), parentlotqty, numberofcarrier
> 2 from lot_header
> 3 where
> 4 lotstarttime = ALL (select lotstarttime from lot_header where
> lotstarttime is NOT NULL)
> 5* order by parentlotid desc
> SQL> /
>
> PARENTLOTID PARENTLOTQ NUMBEROFCARRIER
> ----------- ---------- ---------------
> P3 3
>
> This works fine...
>
> But if I do
>
> 1 select unique(parentlotid), parentlotqty, numberofcarrier
> 2 from lot_header
> 3 where
> 4 lotstarttime = ALL (select lotstarttime from lot_header where
> lotstarttime is NULL)
> 5* order by parentlotid desc
> 6 /
>
> no rows selected
>
> Here I expected atleast P1 and P2 will appear. If L18 also has null
> lotstarttime, then I expect P1, P2 and P3 to appear.
>
> Please help me in having the correct sql query. This is very urgent. I am
> sure no point in mentioning that you will test the script before letting me
> know, but please understand the urgency of the situation.
>
> Thanks in advance
>
> Cheers,
>
> Poolak
>
> Data contents.....
>
> SQL> select lotid, parentlotid,lotstarttime from lot_header;
>
> LOTID PARENTLOTID LOTSTARTT
> ----------- ----------- ---------
> L1 P1
> L2 P1
> L3 P1
> L4 P1
> L5 P1
> L6 P1
> L7 P1
> L8 P1
> L9 P2
> L10 P2
> L11 P2
> L12 P2
> L13 P2
> L14 P3
> L15 P3
> L16 P3
> L17 P3
> L18 P3 02-JUN-99
>
>
>
>
> SQL> desc lot_header
> Name Null? Type
> ------------------------------- -------- ----
> LOTID VARCHAR2(11)
> FTGDFILENAME VARCHAR2(32)
> FTGDLOADCOUNT NUMBER(2)
> SOURCEBAUNUMBER VARCHAR2(8)
> PARENTLOTID VARCHAR2(11)
> LOTPRIORITY VARCHAR2(15)
> BAU_NO_951 VARCHAR2(8)
> AUTOLINE VARCHAR2(20)
> PACKAGE VARCHAR2(24)
> DEVICE VARCHAR2(11)
> PARENTLOTQTY VARCHAR2(10)
> LOTQTY VARCHAR2(10)
> NUMBEROFCARRIER NUMBER(2)
> CELLNAME VARCHAR2(20)
> QTYINSPECTEDATABOS NUMBER(4)
> TOTALDEFECTQTY NUMBER(4)
> LASTPROCESSSTEP VARCHAR2(15)
> LOTSTARTTIME DATE
> LOTCOMPLETEDFLAG VARCHAR2(1)
> LOTCOMPLETEDTIME DATE
> MULTICHIPINDICATOR VARCHAR2(1)
> LASTWSTXN VARCHAR2(4)
> LOTSTATUSFORWS VARCHAR2(4)
> CREATEDON DATE
> CREATEDBY VARCHAR2(8)
> LASTMODIFIEDON DATE
> LASTMODIFIEDBY VARCHAR2(8)
> CCROLLBACKFLAG VARCHAR2(1)
> PRINTCOUNTER NUMBER(2)
>
> poolak banerjee
> Manufacturing Solutions Practice
> COMPAQ Computers Asia/Pacific PTE LTD
> E-Mail: poolak.banerjee@compaq.com
> Phone: 65-580-5445
>
Poolak,
Your first query will work fine, as you noted:
select unique(parentlotid), parentlotqty, numberofcarrier
from lot_header
where lotstarttime = ALL (select lotstarttime from lot_header where
lotstarttime is NOT NULL)
order by parentlotid desc
/
PARENTLOTID PARENTLOTQ NUMBEROFCARRIER
----------- ---------- ---------------
P3 3
Your second query should be switched to the following:
select unique(parentlotid), parentlotqty, numberofcarrier
from lot_header
where parentlotid not in (select parentlotid
from lot_header
where lotstarttime is not null)
order by parentlotid desc
/
This will only select PARENTLOTIDs NOT from the inner-select, which is a
list of all PARENTLOTIDs where there are no lotstarttime values. It sounds
confusing (a few double-negatives) but it works.
Basically, the inner select shows all PARENTLOTID values that have at
least one record with a LOTSTARTTIME value. Then the outer-select returns
all unique records where their PARENTLOTID is NOT in this list of
PARENTLOTIDs.
Hope that this helps!
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 300+ Oracle tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page