SQL Query

SQL Query

Post by dt » Fri, 25 Apr 2003 01:19:55



Now that I have a flat spot on my head,(from beating my head against
the monitor) I will post my question.

The company I am working with has a database that they can not
control. The software they use to interface with oracle (7.*.*)
requires that some "things" remain the way they are. No views, no
additional tables, no database links, etc. etc.

Here's my problem:

I want to know what items on order have either no inventory or the
inventory available is not enough. The inventory table has a feature
of storing each item with the lot code as a seperate column. Here is
some sample data.

TABLE INVT                             TABLE ORD
LOT_CODE     ITEM    QTY               ITEM       ORD        QTY    
========================               =========================
LOT1         ITEM1    0                ITEM1     12345        5
LOT2         ITEM1   10                ITEM1     98765        5
LOT3         ITEM1    5                ITEM2     12345        5
LOT1         ITEM2    5                ITEM2     98765        5
LOT5         ITEM2    3
LOT4         ITEM2    0

As you can see if I total the INVT for ITEM1 = 15 and ITEM2 = 8.
If I total the ORD for ITEM1 = 10 and ITEM2 = 10.
Then subtracting the difference I should see ITEM1 +5 ITEM2 -2.

But with sql when I run a query of
select ord.item, sum(ord.qty), sum(invt.qty)
,(sum(ord.qty)-sum(invt.qty))
from ord, invt
group by ord.item

my results are
ITEM      ORD.QTY        INVT.QTY       DIFFERENCE
ITEM1        10              30             20
ITEM2        10              16              6

You see my dilema. Anyone have a suggestion?

 
 
 

SQL Query

Post by Fran » Fri, 25 Apr 2003 02:13:49



> Now that I have a flat spot on my head,(from beating my head against
> the monitor) I will post my question.

> The company I am working with has a database that they can not
> control. The software they use to interface with oracle (7.*.*)
> requires that some "things" remain the way they are. No views, no
> additional tables, no database links, etc. etc.

> Here's my problem:

> I want to know what items on order have either no inventory or the
> inventory available is not enough. The inventory table has a feature
> of storing each item with the lot code as a seperate column. Here is
> some sample data.

> TABLE INVT                             TABLE ORD
> LOT_CODE     ITEM    QTY               ITEM       ORD        QTY    
> ========================               =========================
> LOT1         ITEM1    0                ITEM1     12345        5
> LOT2         ITEM1   10                ITEM1     98765        5
> LOT3         ITEM1    5                ITEM2     12345        5
> LOT1         ITEM2    5                ITEM2     98765        5
> LOT5         ITEM2    3
> LOT4         ITEM2    0

> As you can see if I total the INVT for ITEM1 = 15 and ITEM2 = 8.
> If I total the ORD for ITEM1 = 10 and ITEM2 = 10.
> Then subtracting the difference I should see ITEM1 +5 ITEM2 -2.

> But with sql when I run a query of
> select ord.item, sum(ord.qty), sum(invt.qty)
> ,(sum(ord.qty)-sum(invt.qty))
> from ord, invt
> group by ord.item

> my results are
> ITEM      ORD.QTY        INVT.QTY       DIFFERENCE
> ITEM1        10              30             20
> ITEM2        10              16              6

> You see my dilema. Anyone have a suggestion?

how's changing the last lines in:
from ord, invt
where ord.item = invt.item
group by ord.item

--
Regards, Frank van Bortel

 
 
 

SQL Query

Post by Mark Townsen » Fri, 25 Apr 2003 12:21:44


First problem - no join clause, so you are doing a cartesian product -
try adding one as follows (untested)

Quote:> select ord.item, sum(ord.qty), sum(invt.qty)
> ,(sum(ord.qty)-sum(invt.qty))
> from ord, invt

   where invt.item = ord.item
Quote:>  group by ord.item

 
 
 

1. Help!! SQL Query query (simple ?)

: I have a table of customers (customer_id) making orders (order_id) worth
: a certain dollar value (value).  A customer can make many orders.

: How do I write a SQL statement that will:
: a)  sum the values of orders for a customer in a total,
: b)  count the number of orders for a customer, and then
: c)  list all customers in descending order by *totals* of values, and
: show the number of orders.

SELECT customer.customer_id, Count(*) As NumOrders, Sum(value) As Total
FROM customers, orders
WHERE customers.customer_id = orders.order_id
GROUP BY customer.customer_id
ORDER BY Sum(value) DESC

I tested this in Access 2.0 and it worked - I don't know how portable it is.
The only situation not covered by this query is when a customer has zero
corresponding order records. If you want these customers to show up in the
result set with 0 order# and $0 total value, you'll need an outer join.

---

2. Ethernet surge suppressors blocking or slowing down link?

3. Dynamic SQL Query In PL/SQL Stored Procedure, How To????

4. Where is the FAQ & a FAQ.

5. Knowing the SQL queries made running SQL*Net

6. SMS Send for WinCE

7. tricky SQL query (currently handled through PL/SQL)

8. complex expresion help.

9. HELP with Simple SQL Query

10. running total (SQL query)

11. simple SQL query

12. question on timing an SQL query embedded in a C routine

13. Help SQL Query