Re: Program problem

[Jennifer Wagner 301-827-3943 FAX 301-594-6463 <WAGNERJ@CDER.FDA.GOV>]

Thanks!  That did the trick!  I didn't think about not exists, although
I did try a sub-query like that.



>How about
>
>begin-select distinct
>dmf_no
>from dmf_document d
>where stamp_date <= to_date('31-dec-1995', 'dd-mon-yyyy')
>and not exists
>(select 1
>  from dmf_document d1
>  where d1.dmf_no = d.dmf_no
>      and d1.stamp_date >= to_date('01-jan-1996, 'dd-mon-yyyy')
>      and d1.doc_type <> 'AUT'
>)
>
>
>>>> WAGNERJ@CDER.FDA.GOV 04/07/00 11:11AM >>>
>Again...  and it is probably something soooo simple!
>
>I have a request for a list of number of applications which have had
>activity on them before 1996 but no activity (except for AUT letters)
>since 1996.
>
>Now, I started out with this program (I've since tried a zillion):
>
>begin-select
>dmf_no &dmfno
>from dmf_document
>where to_char(stamp_date,'DD-MON-YYYY') < '01-JAN-1996'
>having count(dmf_no) > 1
>minus select dmf_no from dmf_document
>where to_char(stamp_date,'DD-MON'YYYY') > '31-DEC-1995'
>and doc_type <> 'AUT'
>having count(dmf_no) < 1
>end-select
>end-procedure
>
>
>I wasn't sure how to put the AUT letters in, but I've tried it every
>which way but loose.  Also it wouldn't let me to do to_date, which is
>what I've been using.  No matter how I run this program, I get dmf_nos
>for things with action since 1996.  Since I am taking from the
>dmf_document table, only actionable items would be put in there.  The
>data entry clerks would enter the dmf_no and what type of action it is
>(AUT, ANN, LET, MEM, REV, etc...).  So, it would seem that getting all
>numbers that have had any action (> 1) before 1996 and then
>subtracting
>out all those that have any no action (< 1) should work.  But it
>doesn't!  Help?!
>
>Thanks,
>
>
>jenny