SQLitening Support Forum

Please login or register.

Login with username, password and session length
Advanced search  

News:

Welcome to the SQLitening support forums!

Author Topic: How to put two Select statements in one?  (Read 774 times)

Fim

  • Guru
  • ***
  • Posts: 139
    • View Profile
    • Fims sajt
How to put two Select statements in one?
« on: November 02, 2017, 10:52:39 AM »

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.
Logged
Fim W

cj

  • Master Geek
  • ****
  • Posts: 636
    • View Profile
Re: How to put two Select statements in one?
« Reply #1 on: November 02, 2017, 04:56:36 PM »

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.
Code: [Select]
#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
« Last Edit: November 02, 2017, 05:14:55 PM by cj »
Logged

Fim

  • Guru
  • ***
  • Posts: 139
    • View Profile
    • Fims sajt
Re: How to put two Select statements in one?
« Reply #2 on: November 03, 2017, 02:34:17 AM »


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

cj

  • Master Geek
  • ****
  • Posts: 636
    • View Profile
Re: How to put two Select statements in one?
« Reply #3 on: November 03, 2017, 10:22:07 AM »

Is this issue resolved?

Fim

  • Guru
  • ***
  • Posts: 139
    • View Profile
    • Fims sajt
Re: How to put two Select statements in one?
« Reply #4 on: November 03, 2017, 10:45:44 AM »

I will have a look at it in some days.
/Fim
Logged
Fim W

Fim

  • Guru
  • ***
  • Posts: 139
    • View Profile
    • Fims sajt
Re: How to put two Select statements in one?
« Reply #5 on: November 04, 2017, 10:49:15 AM »

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.


Logged
Fim W

cj

  • Master Geek
  • ****
  • Posts: 636
    • View Profile
Re: How to put two Select statements in one?
« Reply #6 on: November 04, 2017, 11:07:05 AM »

Glad you have it working!

Bern Ertl

  • Master Geek
  • ****
  • Posts: 481
  • Excellent
    • View Profile
    • InterPlan Systems
Re: How to put two Select statements in one?
« Reply #7 on: November 10, 2017, 09:36:42 AM »

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

  • Guru
  • ***
  • Posts: 139
    • View Profile
    • Fims sajt
Re: How to put two Select statements in one?
« Reply #8 on: November 12, 2017, 11:36:15 AM »

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

Fim

  • Guru
  • ***
  • Posts: 139
    • View Profile
    • Fims sajt
Re: How to put two Select statements in one?
« Reply #9 on: November 29, 2017, 08:31:34 AM »

Here is my attempt with trigger:
Code: [Select]
    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.

Logged
Fim W

Bern Ertl

  • Master Geek
  • ****
  • Posts: 481
  • Excellent
    • View Profile
    • InterPlan Systems
Re: How to put two Select statements in one?
« Reply #10 on: November 29, 2017, 09:50:40 AM »

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