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');

Ive tried losing the 'NOT' statement and reversing the '<' to no avail!

 

[Golom]

It's probably giving you an error. You have the "WHERE" keyword in the sub query twice.

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

Also the date test should be ">". The "<" will look for dates BEFORE '01-JAN-2007'.

If you are using Access then you need #...# around the date.

 

[CartoonDog]

No, sorry, that was just a typo in the forum.

 

[Golom]

Maybe something like

SELECT ID

FROM Part AS P LEFT JOIN 

(SELECT DISTINCT L.Part_ID 
 FROM Trans AS T  
 WHERE T.class='I' 
   AND T.trans_date >= '01-JAN-2007') As X  

ON P.ID = X.Part_ID 

WHERE P.qty_on_hand >0
  AND P.qty_on_order <1
  AND X.Part_ID IS NULL;

 

[Golom]

$%%#&@ Typo

SELECT ID

FROM Part AS P LEFT JOIN 

(SELECT DISTINCT [red]T[/red].Part_ID 
 FROM Trans AS T  
 WHERE T.class='I' 
   AND T.trans_date >= '01-JAN-2007') As X  

ON P.ID = X.Part_ID 

WHERE P.qty_on_hand >0
  AND P.qty_on_order <1
  AND X.Part_ID IS NULL;

 

[CartoonDog]

No probs on the typo, I caught it when Typing it in. I think you are on the right track, but my SQL+Worksheet is choking on something. It doesnt seem to like the left join immediatly followed by the select without some keyword in between. This is Oracle 9.2. Any idea?

 

[CartoonDog]

Ok, got it working, my editor does not like the 'AS' keyword.

 

[r937]

you posted in the ANSI SQL forum

the "AS" is valid in ANSI SQL

the fact that oracle doesn't like it should suggest that if you have similar query problems, you are much more likely to get a good answer faster if you were to post in the oracle forum



r937.com | rudy.ca