May 7, 2004 at 8:56 am
I have 2 tables (F0413 and F0911) that are related by a document number field. In the F0911 table there is a doc type field where the field can have a value of both PK and PO. I am attemtpting to write a query to join the 2 tables on document number (that's no problem) and to find the corresponding records in the F0911 table that have a doc type of PK but not PO.
Any suggestions would be appreciated.
Thanks,
Bill
May 7, 2004 at 1:46 pm
This should give you a list of the document numbers.
select distinct a.*
from (select f.docnum from F0413 f, F0911 ff where f.docnum=ff.docnum and ff.doctype='pk') a
left outer join
(select f.docnum from F0413 f, F0911 ff where f.docnum=ff.docnum and ff.doctype='po') b
on a.docnum=b.docnum
WHERE b.docnum is null
Brian
May 10, 2004 at 6:58 am
or
select a.docnum
from F0413 a
inner join F0911 b
on b.docnum = a.docnum
and b.doctype IN ('PK','PO')
group by a.docnum
having sum(case when b.doctype = 'PK' then 1 else 0 end) > 0
and sum(case when b.doctype = 'PO' then 1 else 0 end) = 0
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply