Subject Re: SQL SYNTAX
From Mustansir Ghor <mustan31@hotmail.com>
Date Tue, 20 Mar 2018 12:58:10 -0400
Newsgroups dbase.getting-started

Dear Mervyn

Thank you sir. Its really fascinating that you read exactly what the problem is and advise solution. The IT doctor.

I have yet to take medicine but I am sure it will work.

Best regards
Mustansir


Mervyn Bick Wrote:

> On 2018-03-18 9:27 PM, Mustansir Ghor wrote:
> > Dear All
> >
> > I have dbf table SUMMARY with item (c), qtyin (n), qtyout (n)  as fields.
> >
> > With have following command I am able to insert all  qtyin into the SUMMARY table
> >
> > INSERT INTO SUMMARY (ITEM, QTYIN) SELECT ITEM,SUM(QTY) FROM ARRIVALS GROUP BY ITEM
> >
> > Then for the same item record in the SUMMARY table I need to add qtyout  from SALES table. Please can anybody guide me what SQL syntax with which I shall be able to do this
>
> I don't know too much SQL (yet :-) ) and this may be doing things the
> hard way.
>
> I'm assuming that you are starting with an empty SUMMARY table.  If this
> is not so it's a whole new ball game.   All SQL tables need a primary
> key so that individual records can be identified when updating of data
> is needed.  As you are inserting grouped values from the ARRIVALS table
> there will only be one record for each item in the SUMMARY table.  This
> will serve to identify the record for the values from the SALES table.
>
> Your SQL INSERT command creates a new record in SUMMARY for each item in
> the ARRIVAL table and saves the QTYIN value.  So far, so good. :-)
>
> To add the QTYOUT values to the same records you need to use the SQL
> UPDATE command.  You also need a WHERE clause specifying the actual
> record in SUMMARY to update.   This can work IF the item is already in
> SUMMARY i.e  there was an arrival for the item.  The problem arises if
> you've sold an item that isn't already in the SUMMARY table.  You can't
> update a record that isn't there
>
> If the items in SALES are always already in SUMMARY the the following
> should do what you need.
>
> if file('summary.dbf')
>    drop table summary
> endif
> if not file('summary.dbf')
>     create table summary  (item numeric(10,2),qtyin numeric(10,2),qtyout
> numeric(10,2))
> endif
>
> INSERT INTO SUMMARY (ITEM, QTYIN) SELECT ITEM,SUM(QTY) FROM ARRIVALS
> GROUP BY ITEM
> UPDATE SUMMARY SET QTYOUT = (SELECT SUM(SALES.QTY) FROM SALES WHERE
> SALES1.ITEM = SUMMARY.ITEM)
>
> If there is an item in the SALES table which is not already in the
> SUMMARY table it will be ignored without flagging an error.
>
> If there can be cases where items sold aren't already in the SUMMARY
> table the following should do the job.  For this you will need a table
> containing ALL the item numbers.  It doesn't matter if there are any
> other fields as you can simply add the fields QTYIN and QTYOUT, populate
> them, create SUMMARY.DBF and then remove the fields QTYIN and QTYOUT
>
>
> if file('summary.dbf')
>    drop table summary
> endif
>
> ALTER TABLE ITEMS ADD QTYIN NUMERIC(10,2), ADD QTYOUT NUMERIC(10,2)
> UPDATE ITEMS SET QTYIN = (SELECT SUM(QTY) FROM ARRIVALS WHERE
> ARRIVALS.ITEM = ITEMS.ITEM )
> UPDATE ITEMS SET QTYOUT = (SELECT SUM(QTY) FROM SALES WHERE SALES.ITEM =
> ITEMS.ITEM )
> SELECT ITEM,QTYIN,QTYOUT FROM ITEMS1 WHERE QTYIN <> 0 OR QTYOUT <> 0
> SAVE TO SUMMARY
> ALTER TABLE ITEMS1 DROP QTYIN, DROP QTYOUT
>
>
> Not that the 'SAVE TO SUMMARY' must be on the same line as the 'SELECT'
>
> Mervyn.
>
>
>