||Re: SQL SYNTAX
Mervyn Bick <email@example.com>
||Wed, 21 Mar 2018 08:55:40 +0200
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
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