• Welcome, Guest. Please login.
 
May 26, 2019, 01:49:52 pm

News:

Welcome to the SQLitening support forums!


sql query

Started by D. Wilson, April 09, 2019, 04:10:12 pm

Previous topic - Next topic

D. Wilson

I 'know it can be done - I am just having trouble creating it'

I have two tables

1) Inventory
2) Holds

I want to run a query that will give me result the shows the total holds vs inventory qty on hand. I want to show all the items that have more holds than the current inventory level.

In theory the system will never allow a user to put on hold if there is not an enough inventory.

But I want to run the query on program startup to create a dashboard for the user to know those inventory items have 'issues'

There could be multiple holds per inventory item

I know it would involve sum and group by. But I am stuck trying to create the needed sql query.

Any help would be appreciated.

cj

April 12, 2019, 04:49:33 pm #1 Last Edit: April 12, 2019, 05:56:53 pm by cj
QuoteI want to run a query that will give me result the shows the total holds vs inventory qty on hand. I want to show all the items that have more holds than the current 
inventory level.

1. Add OnHold column to Inventory table
2. select * from table1 where qty < OnHold"

QuoteIn theory the system will never allow a user to put on hold if there is not an enough inventory.

UpdateteFlag& = Increment(sCode$,HoldMore)  ' return 1 if successful (doesn't allow out of limits)

slexe "create table table1(code unique, qty, OnHold)"
slexe "insert into table1 values('wilson',9,0)"
slexe "insert into table1 values('klume',100,103)"
slexe "insert into table1 values('squires',500,602)"

Test program
GLOBAL gs AS STRING
#INCLUDE "sqlitening.inc"

FUNCTION Increment(sCode AS STRING, hold AS LONG) AS LONG
'Update OnHold if qty& >= OnHold and hold > 0
IF hold < 1 THEN EXIT FUNCTION 'updating by less than 1 could be endless
slexe "update table1 set OnHold=OnHold+"+STR$(hold) + " where code='" + sCode$ + "' and qty >= OnHold+" + STR$(hold)
FUNCTION = slGetChangeCount
END FUNCTION

FUNCTION PBMAIN () AS LONG
LOCAL sCode         AS STRING
LOCAL HoldMore      AS LONG
LOCAL UpdateFlag    AS LONG
gs+= CHR$("Update OnHold until >=Quantity",$CR,$CR,"Code",$TAB,"Qty",$TAB,"OnHold",$CR)
CreateTable

scode = "wilson"
HoldMore = 1
DO 'test Increment function by holding more until limit reached
  ShowTable "select * from table1 where code = '" + sCode + "'"
  UpdateFlag = Increment(sCode,HoldMore)
LOOP WHILE UpdateFlag

gs+= CHR$($CR,$CR,"Qty <= OnHold",$CR,$CR,"Code",$TAB,"Qty",$TAB,"OnHold",$CR)
ShowTable "select * from table1 where Qty <= OnHold"
? gs,,"Done"
END FUNCTION

SUB ShowTable(sql AS STRING)
LOCAL sArray() AS STRING
slSelAry sql,sArray(),"Q9c"
IF UBOUND(sArray) > 0 THEN
  gs = gs + JOIN$(sArray(),$CR) + $CR
ELSE
  gs = "No data" + $CR
END IF
END SUB

SUB CreateTable
LOCAL sCode AS STRING
LOCAL hold,UpdateFlag AS LONG
slOpen "table1","C"
slexe "drop table if exists table1"
slexe "create table table1(code unique, qty, OnHold)"
slexe "insert into table1 values('wilson',9,0)"
slexe "insert into table1 values('klume',100,103)"
slexe "insert into table1 values('squires',500,602)"
END SUB

cj

April 13, 2019, 01:27:46 pm #2 Last Edit: April 18, 2019, 09:30:11 pm by cj Reason: Testing new forum software
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG 'group_by_having.bas alias wilson3.bas 4/13/19

 LOCAL sql     AS STRING
 LOCAL sresult AS STRING

 slopen "group_having.db3","C"

 slexe  "drop table if exists iTable"
 slexe  "drop table if exists hTable"
 slexe  "create table if not exists iTable (iKey unique,iQty INTEGER)"
 slexe  "create table if not exists hTable (hKey,       hQty INTEGER)"

 slexe  "insert into iTable values ('bear',1),('dog',0),('fox',3)"
 slexe  "insert into hTable values ('dog',3) ,('dog',2),('fox',4)"

 sql =  "select iKey as Code,sum(hQty)as Held,iQTY as OnHand from hTable,iTable" +_
        " where iKey=hKey" +_
        " group by hKey" + _
        " having Held > iQTY" 'changed sum(hQty) to Held

 sresult = getrs(sql)
 ? sresult,,"Held Report"

END FUNCTION

FUNCTION getrs (sql AS STRING) AS STRING
 LOCAL sArray() AS STRING
 slSelAry sql,sArray(),"Q9 E0"
 IF slGetErrorNumber THEN ? slGetError,,"GetData":EXIT FUNCTION
 IF UBOUND(sArray)>0 THEN
  FUNCTION = JOIN$(sArray(),$CR)
 ELSE
  FUNCTION = "No records found"
 END IF
END FUNCTION


'Testing new forum software 4/18/19
'Can edit, but can't create a new thread  or reply