Help with an SQL statement.

[CartoonDog]

I am have been trying to write a sql statement and could use some help.

I am querying two tables. The first table holds the part numbers called PART. The second holds the transactions for the part table and is called TRANS. I need to find all the parts that have not had any transactions since 1/1/2007. The tables are linked by part number, called ID. The transaction table has the dates of when parts were used.

My initial query looked like this:

select ID from part where
qty_on_hand >0
and qty_on_order <1
and id not IN (select part_id from trans where
where trans.class='I'
and trans.trans_date <'01-JAN-2007');

 

[SantaMufasa]

CartoonDog,

I haven't run your code against any sample data, but a quick desk check of your logic tells me your request is:

"Display IDs for parts that were never ordered prior to '01-JAN-2007'."

I believe you want just the opposite:

"Display IDs for parts that were never ordered after to '01-JAN-2007'."

To make that happen, I would change the "<" to ">".

Let us know how that works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CartoonDog]

Ive tried it both ways and it doesnt work correctly.

 

[BJCooperIT]

The code posted could not have run due to the two WHERE clauses for the TRANS table.

SantaMufasa is correct. If you really meant since 1/1/2007, it should be >'01-JAN-2007'.

We are missing the other part of your specifications. Is this what you meant?

SELECT id                                               --Get IDs that:
FROM   part
WHERE  qty_on_hand > 0                                  --Have part(s) on hand
AND    qty_on_order < 1                                 --None are on order
AND    id NOT IN                                        --Part was NOT used in:
        (   SELECT part_id
            FROM   trans
            WHERE  trans.CLASS = 'I'                    --class "I"
            AND    trans.trans_date < '01-JAN-2007');   --before Jan. 1st 2007

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[CartoonDog]

yes, sorry about the typo of the second where. It does not seem to work. Part 'X' in the parts table that does not show a Class 'I' transaction between today and 1/1/2007 should show in the results list, but does not. It may although have other Classes of transactions, as long as they are not class 'I'

Thanks.

 

[BJCooperIT]

Perhaps this?

SELECT id                                               --Get IDs that:
FROM   part
WHERE  qty_on_hand > 0                                  --Have part(s) on hand
AND    qty_on_order < 1                                 --None are on order
AND    id NOT IN                                        --Part was NOT used in:
        (   SELECT part_id
            FROM   trans
            WHERE  trans.CLASS = 'I'                    --class "I"
            AND    trunc(trans.trans_date) 
                   BETWEEN  '01-JAN-2007'               --on/after Jan. 1st 2007
                   AND      trunc(SYSDATE));            --on/before Today

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[ChrisHunt]

Part 'X' [...] should show in the results list, but does not

Maybe this is a silly question, but have you double-checked that Part X has a qty_on_hand > 0, and a qty_on_order < 1?

Like Dave, it seems to me that if you changed the < to a > in your original query (and removed the extra "where"), you'd get the results you're after. If that's not happening, can you post the query you're using and the data you're putting in to it.



-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[ChrisHunt]

PS. When I'm testing a query and not seeing records that I expect to find in the results, I tend to comment out each condition of the where clause in turn to find out which one is excluding the row in question. It can narrow down the search for what's going wrong (in the query or in my assumptions!)

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[weberm]

How about using a simple join like:

SELECT ID
  FROM part, trans
 WHERE part.ID = trans.ID
   AND part.qty_on_hand > 0
   AND part.qty_on_order < 1
   AND trans.CLASS = 'I'
   AND trans.trans_date > TO_DATE('01012007', 'MMDDYYYY');

Also, I implicitly cast the date to make sure the format is right.

 

[SantaMufasa]

I agree, weberm...your code is much tighter and I'm confident that it would run faster, as well. Goodonya'!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ChrisHunt]

Tighter and faster maybe, but not what the OP asked for:

I need to find all the parts that have not had any transactions since 1/1/2007.

Weberm's query will find the exact opposite (and return multiple entries where a part has had more than one transaction).

This should work, but it's pretty much what's already been suggested:

SELECT id
FROM   part p
WHERE  p.qty_on_hand > 0
AND    p.qty_on_order < 1
AND    p.id NOT IN (SELECT t.part_id
                    FROM   trans t
                    WHERE  t.class = 'I' 
                    AND    t.trans_date > '01-JAN-2007')

If it doesn't - put up some test data so we can see more clearly what the problem is.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[Thargy]

In the true spirit of polished apples and picked nits, for speed, how about

SELECT id
FROM   part p
WHERE  p.qty_on_hand > 0
AND    p.qty_on_order < 1
MINUS
SELECT t.part_id
  FROM trans t
 WHERE t.class = 'I'
   AND t.trans_date > '01-JAN-2007')

Regards

T