• Welcome, Guest. Please login.
 
December 13, 2019, 09:04:27 pm

News:

Welcome to the SQLitening support forums!


How to put two Select statements in one?

Started by Fim, November 02, 2017, 01:22:39 pm

Previous topic - Next topic

Fim

I have two tables. One ART with all data (except inventory on hand) about all parts.
The other table is LPL with inventory on hand with one record for each part and each stock location.
Now I want a list, for each part where total inventory on hand for each part is less then the reorder point.
Is it possible to put these two SQL-statement into one statement?
SELECT PARTNO, SUM(QOH) AS QOH_SUM FROM LPL GROUP BY PARTNO
SELECT PARTNO, REORDEP, QOH_SUM FROM ART WHERE QOH_SUM < REORDP
As you see QOH_SUM a computed term and not a term in any table.

/Fim W.
Fim W

cj

November 02, 2017, 07:26:36 pm #1 Last Edit: November 02, 2017, 07:44:55 pm by cj
select LPL.PARTNO,QOH from ART,LPL where ART.PARTNO = LPL.PARTNO and QOH < REORDEP 'group and sum still needed  no database to test

I don't see anything to  group by if partnum is unique each partnum is a unique group.

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
KILL "junk.db3"
slopen "junk.db3","C"
slexe "create table if not exists ART(PARTNO INTEGER PRIMARY KEY,REORDEP)"
slexe "create table if not exists LPL(PARTNO INTEGER PRIMARY KEY,QOH)"
slexe "Replace into ART values(1,10)"
slexe "Replace into ART values(2,10)"
slexe "Replace into LPL values(1,9)"
slexe "Replace into LPL values(2,9)"
REDIM s(0) AS STRING
slselAry "select LPL.PARTNO,SUM(QOH) AS SUMMED from ART,LPL where ART.PARTNO = LPL.PARTNO and QOH < REORDEP group by LPL.PARTNO",s(),"Q9"
? JOIN$(s(),$CR)
END FUNCTION

Fim


Thank you, That looks very interesting, will look closer to it.
/Fim
Fim W

cj


Fim

Fim W

Fim

Now it is solved.
Here it is, in Swedish.

SELECT ART.ARTNR, ARTBEN, SUM(LPL.LSALDO_ANT) AS SALDO, ART.BESTPKT, PARTISTORLEK, ART.LEVNR FROM ART, LPL WHERE LPL.ARTNR = ART.ARTNR GROUP BY ART.ARTNR ORDER BY ART.LEVNR

The rest of my problems I will solve in the program not using SQL.

It had taken years to solve this on its own.
Thank you for your help.

/Fim W.


Fim W

cj


Bern Ertl

Glad you have a solution Fim, but if you are interested, it's also possible to add a QoH column to your ART table and add some triggers to keep it updated with the current inventory amount in the LPL table.  You just need 3 triggers (when LPL records are inserted, deleted or updated).  Then you could pull the data directly from the ART table without the overhead of the SUM() or table join. 

Fim

Bern,
A trigger that you suggest seems to be a good solution, thanks for the tip.
I'll think about it.
/Fim W.
Fim W

Fim

Here is my attempt with trigger:

    slExe "Drop Table If Exists LPL"
    slExe "Create Table LPL (" +  _
"LAGERPLATS,"                         + _
"ARTNR,"                              + _
"LSALDO_ANT,"                         + _
"LAGERKONTO,"                         + _
"MATERIALPALAGGKONTO)"

    slExe "CREATE UNIQUE INDEX LPL_INDEX0 ON LPL (LAGERPLATS, ARTNR)"
    slExe ("CREATE TRIGGER LSALDO_I AFTER INSERT ON LPL FOR EACH ROW BEGIN UPDATE ART SET LSALDO_ANT = LSALDO_ANT + LPL.LSALDO_ANT WHERE ARTNR = LPL.ARTNR; END", "E2")

But I get the error "1 = No such column: LPL.LSALDO_ANT
It must be "UPDATE ART ...... LPL.LSALDO_ANT .."
Can not I use a term from LPL when updating ART?
/Fim W.

Fim W

Bern Ertl

Instead of using LPL. in your update statement, you need to use "NEW".  See docs for trigger:
Quote...
Both the WHEN clause and the trigger actions may access elements of the row being inserted, deleted or updated using references of the form "NEW.column-name" and "OLD.column-name", where column-name is the name of a column from the table that the trigger is associated with. OLD and NEW references may only be used in triggers on events for which they are relevant, as follows:
INSERT    NEW references are valid
UPDATE    NEW and OLD references are valid
DELETE    OLD references are valid
...


http://www.sqlite.org/lang_createtrigger.html