Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: bad SQL day...help please

Re: bad SQL day...help please

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 27 Nov 2003 14:49:26 -0800
Message-ID: <F001.005D8077.20031127144926@fatcity.com>


I have had good success with the minus operator:

select ob_oid, sku, qty from tbl where transact in ('RPCK','PICK') minus
select ob_oid, sku, qty from tbl where transact = 'SHIP'

At 12:14 PM 11/27/2003, you wrote:
>List,
>
>Please excuse the content of this question. I haven't had a breakthrough
>yet so I'm hoping for some assistance... it may seem trivial to some but
>for some reason I am SQL-ly challenged today.
>
>I have a table which holds historical transaction records. Each PICK or
>RPCK record should have a corresponding SHIP record with a match on
>quantity, sku, and order_id. I have to create an exception report where
>if for any PICK/RPCK record there isn't a corresponding SHIP record, I
>should be shown the PICK/RPCK record. In other words, each sku has
>records in multiples of 2 - i.e. 1 PICK/RPCK, then 1 SHIP record; 2
>PICK/RPCK records, then 2 SHIP records.
>
>I know what I want in English, but I'm having trouble designing the
>query in SQL. In the table below, you can see that SKU 117127 has a PICK
>record but no SHIP record, same case for SKU 701206.
>
>Is someone kind enough to offer me some SQL advice?
>
>Thanks in advance,
>Saira
>
>OB_OID SKU TRANSACT QTY
>50340 115227 RPCK 36
>50340 115227 SHIP 36
>50340 115304 RPCK 36
>50340 115304 SHIP 36
>50340 174040 RPCK 12
>50340 174040 SHIP 12
>50340 177127 PICK 36
>50340 177144 PICK 24
>50340 177144 SHIP 24
>50340 177624 PICK 24
>50340 177624 SHIP 24
>50340 177634 PICK 48
>50340 177634 SHIP 48
>50340 190000 PICK 20
>50340 190000 SHIP 20
>50340 20020 RPCK 6
>50340 20020 SHIP 6
>50340 701079 PICK 100
>50340 701079 SHIP 100
>50340 701206 RPCK 30
>
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Saira Somani-Mendelin
> INET: saira_somani_at_yahoo.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Nov 27 2003 - 16:49:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US