Subject Re: SQL SYNTAX
From Mustansir Ghor <mustan31@hotmail.com>
Date Wed, 21 Mar 2018 15:37:03 -0400
Newsgroups dbase.getting-started

Dear Mervyn

Thank You

Regards
Mustansir

Mervyn Bick Wrote:

> On 2018-03-20 8:21 PM, Mustansir Ghor wrote:
> > Dear Mervyn
> >
> > One more issue is needs help. Some records in  qtyin and qtyout have null. So when I update net= qtyin-qtyout, some net remains blank. How to address this problem.
> >
>
> In "proper" SQL this can be addressed easily in various ways.  localSQL
> does, however, have its limitations and so this will have to be solved
> by using dBASE commands and not SQL.
>
>
> In the example I posted before I started with an empty SUMMARY table by
> deleting the existing table and then using CREATE TABLE to make a new
> table.  When using SQL one can set a default value (0 in this case) when
> creating a table.   Unfortunately this ability isn't available in
> localSQL.  A default value can, however, be set in the dBASE Table designer.
>
> Open the SUMMARY table in the table designer.  If the Inspector doesn't
> open, open it with F11.  Select qtyin and in the Inspector change the
> default from null to 0.  Do the same for qtyout.
>
>
> SET SAFETY OFF
> USE SUMMARY EXCLUSIVE
> ZAP
> USE
> SET SAFETY ON
> INSERT INTO SUMMARY (ITEM, QTYIN) SELECT ITEM,SUM(QTY) FROM ARRIVALS1
> GROUP BY ITEM
> UPDATE SUMMARY SET QTYOUT = (SELECT SUM(SALES1.QTY) FROM SALES1 WHERE
> SALES1.ITEM = SUMMARY.ITEM)
> UPDATE SUMMARY SET NET = QTYIN-QTYOUT
>
> Mervyn.
>