RE: sql question

Sheri,

Not sure what you are asking for, perhaps sharing the two statement you are currently using would
help.

But here's a quick thought:

Select a.*, b.*

From podet A

left outer join pordt B
on a.key = b.key
and b.status <> 'C'
Where a.status = 'O'

Charles

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rowe, Sheri
Sent: Wednesday, October 03, 2007 9:38 AM
To: midrange-l@xxxxxxxxxxxx
Subject: sql question

Hi all,

I have an SQL Question. I can do this in 2 passes but think
it should be able to be done with 1.

I have 2 files, PODET - PO detail and PORDT - PO receipts
file. I want to join the files to get a list of:

1. All PO's from PODET that have a status of 'O' (regardless
if it is in PORDT)
2 All PO receipts records which match the PODET file with a
status of
<> 'C'
3. If there is a matching record between PODET and PORDT and
the PODET status = 'O' but the PORDT records = 'C' I still
want the PODET record to appear but not the PORDT record.
4. There may be multiple records in PORDT that correspond
with PODET (Co#, PO#, PO line #, Item #)

I have tried multiple iterations but I can't get the all the
PODET records with a status of 'O' and only the PORDT records
that do not have a status = 'C', without performing 2 passes.

Any help would be greatly appreciated.

Thanks.

Sheri

--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list To post a message email:
MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,
please take a moment to review the archives at
http://archive.midrange.com/midrange-l.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.