• Welcome, Guest. Please login.
 
May 25, 2020, 06:29:28 pm

News:

Welcome to the SQLitening support forums!


Show posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.

Messages - cj

16
From NordVPN:

Business packages start from 5 annual accounts.

Regarding connectivity, yes, you can connect from anywhere in the world to a particular country of our shared servers. You can also purchase a Dedicated server to have one static IP that is not shared between anyone but your team, which you can assign particular users to it and use it per your preferences.

We also have a Back Friday deal going on this very week, if you make a purchase until the end of it.

We could add AES256 to SQLitening's TCP routines.
17
I am using a VPN service and can connect to the SQLitening server only if using the same dedicated VPN server.

I don't know if a person using the same VPN service could connect to the server from another account or if they need my username and password?
I have sent an email to the VPN service.

18
SQLiteningServer.Cfg:
Port=51234
Hosts=192.168.0.2

Logins using slConnect:
The local computers connect to the local IP address or machine name and port of the server.
The remote computers connect to the remote IP address and port of the server.

Router:
The router needs to forward the local IP address and port to the server machine.
The IP address at command prompt of server: Type IPCONFIG  it is the IPV4 address.
I would use the address reservation page of the router to lock in the IP address of the server
so it never changes if the network is rebooted.

Windows Firewall:

SQLiteningServer.exe needs to be added to "Allow an app through the firewall."

Shortcuts:
Mark "Run as Administrator" to SqliteningServer.exe and SqliteningServerAdmin.exe

SQLitening is not secure over the internet.  Connections are not encrypted or authenticated.

CubeSQL seems to be a lot like SQLitening and uses JSON.  It might be an option.
https://sqlabs.com/cubesql

SQLTools might also be an option to consider and use another SQL.



Test server with 12 connections by using threads

#INCLUDE "win32api.inc"
#INCLUDE "sqlitening.inc"
GLOBAL gs() AS STRING
FUNCTION PBMAIN () AS LONG
 LOCAL x,hThread AS LONG
 DIM gs(12) 'each user puts recordset into an element
 FOR x = 1 TO UBOUND(gs)
  THREAD CREATE MyThread(x) TO hthread
  THREAD CLOSE hThread TO hThread
 NEXT
 DO:SLEEP 50:LOOP UNTIL THREADCOUNT=1
 ? JOIN$(gs(),$CR)
END FUNCTION

THREAD FUNCTION MyThread(BYVAL Num AS LONG) AS LONG
 DIM s() AS STRING
 slConnect "192.168.0.2",51234
 slOpen "sample.db3"
 slselary "select rowid,redref from parts where rowid=" + STR$(Num),s(),"Q9c"
 gs(Num) = JOIN$(s(),$CR)  'put into array
 slDisconnect
END FUNCTION
19
If using the server everyone can share the server without a problem.
I've read many reasons on the SQLite site why not to use SQLite peer to peer.

I am sure TLS with SocketTools would work by modifying the client and server code, but
the server would require a certificate.  I noticed SocketTools added some encryption
code, but that isn't unique to SocketTools. If you create your own certificates is
not suggested, but talking to SocketTools they might be interested.
It would be easy for them.



20
Both routines are compared in this posting.

Bern,
Nice, learned a lot!!

Bern routine results:
1 payment record must exist to return results for a client
2 credit balances do not display (they weren't asked for)
3 optional, modified to add payments (passed as negative amounts)

The CJ routine could eliminate the creating temp table by reading
through the recordset and handling the debit/credit as they occur.
Made it easier by just creating a temp file with the balances.

Also, for some reason when I dropped the what I thought was unneeded third column in the
union statement t1,t2,t3 the union was not always correct.  Need to look into that.
Leaving "as is" is suggested.

In this example CJ will return 4-records and Bern will return 2-records.
Fredrick, these differences should be considered.

#INCLUDE "sqlitening.inc"

FUNCTION showit(sql AS STRING) AS STRING
 LOCAL sArray() AS STRING
 slselary sql,sArray(),"Q9"
 FUNCTION = JOIN$(sArray(),$CR) + $CR
END FUNCTION

FUNCTION PBMAIN AS LONG
 bern
 cj
END FUNCTION

SUB InsertData
 slexe  "insert into T1 values(1,10),(1,20),(2,10),(4,100)"
 slexe  "insert into T2 values(1,30),(1,40),(2,10)"
 slexe  "insert into T3 values(1,-200),(2,0),(3,-1)"
END SUB

FUNCTION Bern AS LONG

 LOCAL s AS STRING
 KILL   "temp.db3":IF ERR THEN ? STR$(ERRCLEAR)
 slopen "temp.db3","C"

 slexe  "create table if not exists T1 (surname1 int,Amount1 int)"
 slexe  "create table if not exists T2 (surname2 int,Amount2 int)"
 slexe  "create table if not exists T3 (surname3 int,Amount3 int)"
 InsertData

 '? ShowIt("select * from t1") + $CR + ShowIt("select * from t2") + $CR + ShowIt("select * from t3")

 s=  "WITH"
 s+="   TempT1(TT1Surname,TT1Total) AS (SELECT Surname1, Amount1"
 s+="     FROM T1),"
 s+="   TempT2(TT2Surname,TT2Total) AS (SELECT Surname2, Amount2"
 s+="     FROM T2),"
 s+="   SumT1( ST1Surname, ST1Total) AS (SELECT TT1Surname, TOTAL( TT1Total)"
 s+="     FROM TempT1 GROUP BY TT1Surname),"
 s+="   SumT2( ST2Surname, ST2Total) AS (SELECT TT2Surname, TOTAL( TT2Total)"
 s+="     FROM TempT2 GROUP BY TT2Surname),"
 s+="   SumT3( ST3Surname, ST3Total) AS (SELECT Surname3, TOTAL( Amount3)"
 s+="     FROM T3 GROUP BY Surname3)"
 s+=" SELECT ST3Surname as acct, ST1Total + ST2Total + ST3Total as amt"
 s+="   FROM SumT3"
 s+="   INNER JOIN SumT1 ON ST3Surname = ST1Surname"
 s+="   INNER JOIN SumT2 ON ST3Surname = ST2Surname"
 s+="   WHERE ST1Total + ST2Total <> ST3Total" 'made <> instead of subtract

 '? s,,"Sql"
 ?  ShowIt(s)
 'DIM sArray() AS STRING
 'slselAry s,sArray(),"Q9"
 '? JOIN$(sArray(),$CR),,FUNCNAME$
 slClose

END FUNCTION

FUNCTION CJ () AS LONG

 LOCAL sData(),sBind AS STRING, x AS LONG
 KILL   "temp.db3"
 slopen "temp.db3","C"

 slexe  "create table if not exists t1 (surname1 int,amt int)"     'charges table1
 slexe  "create table if not exists t2 (surname2 int,amt int)"     'charges table2
 slexe  "create table if not exists t3 (surname3 int,amt int)"     'payments
 slexe  "drop table if exists TempBalances
 slexe  "create table TempBalances(acct unique,amt)"
 InsertData

 'combine tables and payments
 slselary " select surname1,sum(amt),'t1' from t1 group by surname1"  +_     'charges table1
          " union "                                      +_
          " select surname2,sum(amt),'t2' from t2 group by surname2"  +_     'charges table2
          " union "                                      +_
          " select surname3,sum(amt),'t3' from t3 group by surname3",sData() 'payments table3

 'update TempBalances() from above sData()
 FOR x = 1 TO UBOUND(sdata,2)
  sBind = slBuildBindDat(sdata(1,x),"T") + slBuildBindDat(sData(2,x),"T")
  slexebind "Insert into TempBalances values(?,?) ON conflict(acct) DO update SET amt=amt+" + sData(2,x),sBind
 NEXT
 ? showit("select * from TempBalances"),,FUNCNAME$
 slclose
END FUNCTION
21
balances.png

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
 LOCAL sData(),sBind AS STRING, x AS LONG

 KILL   "temp.db3"
 slopen "temp.db3","C"

 slexe  "create table if not exists t1 (acct,amt)"     'charges table1
 slexe  "create table if not exists t2 (acct,amt)"     'charges table2
 slexe  "create table if not exists t3 (acct,amt)"     'payments
 slexe  "drop table if exists TempBalances;create table TempBalances(acct unique,amt)"

 slexe  "insert into t1 values(1,1000),(2,2000),(3,3000)"             'charges t1
 slexe  "insert into t2 values(4,4000)"                               'charges t2
 slexe  "insert into t3 values(4,-2000),(1,-500),(2,-1000),(3,-1500)" 'apply payments

 'combine tables and payments
 slselary " select acct,sum(amt),'t1' from t1 group by acct"  +_     'charges table1
          " union "                                      +_
          " select acct,sum(amt),'t2' from t2 group by acct"  +_     'charges table2
          " union "                                      +_
          " select acct,sum(amt),'t3' from t3 group by acct",sData() 'payments table3

 'update TempBalances() from above sData()
 FOR x = 1 TO UBOUND(sdata,2)
  sBind = slBuildBindDat(sdata(1,x),"T") + slBuildBindDat(sData(2,x),"T")
  slexebind "Insert into TempBalances values(?,?) ON conflict(acct) DO update SET amt=amt+" + sData(2,x),sBind
 NEXT
 ? showit("select * from TempBalances"),,"Balances"
END FUNCTION

FUNCTION showit(sql AS STRING) AS STRING
 LOCAL sArray() AS STRING
 slselary sql,sArray(),"Q9"
 FUNCTION = JOIN$(sArray(),$CR) + $CR
END FUNCTION
22
Need the relationships and what a detail line or the column heading should be.
Hosital Number  Client Number Invoice Number ....
23
https://sqlite.org/lang_with.html only for me.
This is a first for me, CTE is new have no idea what arsenal is?

I couldn't figure out the relational keys of your 3 tables.
key1
key2
key3

Do payments have to be applied to invoices for some unknown reason?
Is this a running system?
You make it hard for us.



24
Changed Amount to Amount1
Misuse of aggregate Total()

#INCLUDE "sqlitening.inc"
GLOBAL gs AS STRING

FUNCTION PBMAIN () AS LONG
 KILL   "test.db3"
 slOpen ("test.db3", "C")
 'ServicesBill  changed to t1
 slExe "Create Table t1(Date TEXT, BillNo TEXT, HospitalNo TEXT COLLATE NOCASE, Surname1 TEXT, Othernames TEXT, Category TEXT," & _
 "Department TEXT, CodeNo TEXT COLLATE NOCASE, Service TEXT, amount1 REAL, Discount REAL, Status TEXT)"

 'Pharmacy Bill changed to t2
 slExe BUILD$("Create Table t2(RecNo TEXT, SNo TEXT, Date TEXT, InvoiceNo TEXT, HospitalNo TEXT, Surname2 TEXT,", _
 "ProductNo TEXT COLLATE NOCASE, Description TEXT, PatientCategory TEXT, Department TEXT, Quantity INTEGER, SellingPrice REAL, Discount REAL, Username TEXT)")

 'Payment       changed to t3
 slExe BUILD$("Create Table t3(ReceiptNo INTEGER, BillNo INTEGER, Date TEXT, HospitalNo TEXT, Surname3 TEXT, Othernames TEXT,", _
 "Category TEXT, Stage TEXT, PaymentMode TEXT, CheckNo TEXT, Amount3 REAL, Purpose TEXT, Department TEXT, Cashier TEXT)")

 LOCAL x AS LONG
 FOR x = 1 TO 1
  slexebind "insert into t1 values (?,?,?,?,?,?,?,?,?,?,?,?)",     REPEAT$(12,slbuildbindDat("1","T")) '12
  slexebind "insert into t2 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("1","T")) '14
  slexebind "insert into t3 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("1","T")) '14
 NEXT
 gs = CHR$( GetRs("select * from t1"),$CR,$CR,GetRs("select * from t2"),$CR,$CR,GetRs("select * from t3")) + $CR
 Bern
END FUNCTION

FUNCTION Getrs(sql AS STRING) AS STRING
 DIM s() AS STRING
 slSelAry sql,s(),"Q9c"
 FUNCTION = JOIN$(s(),$CR)
END FUNCTION

SUB Bern
LOCAL s AS STRING

s=  "WITH"
s+= "   TempT1(TT1Surname,TT1Total) AS (SELECT Surname1, Amount1 - Discount"
s+="     FROM T1),"
s+="   TempT2(TT2Surname,TT2Total) AS (SELECT Surname2, Quantity * SellingPrice - Discount"
s+="     FROM T2)"
s+=" SELECT Surname3, Total( TT1Total) + Total( TT2Total) - Total(Amount3)"
s+="   FROM T3"
s+="   LEFT OUTER JOIN TempT1 ON Surname3 = TT1Surname"
s+="   LEFT OUTER JOIN TempT2 ON Surname3 = TT2Surname"
s+="   WHERE Total( TT1Total) + Total( TT2Total) > Total(Amount3)"
s+="   GROUP BY surname3"

 DIM sArray() AS STRING
 slselAry s,sArray(),"Q9"
 'slSel s,0,"D"
 ? "Incorrectly attempt Bern statement" + $CR + JOIN$(sArray(),$CR) + STRING$(5,$CR) + "Tables contained:" + $CR + gs
END SUB
                                 
25
Error near FROM  (image at bottom)
Hope Fredrick needs this  :o
I'm going back to learning mode (about WITH)
I don't match payments to invoices so this is all new to me.

#INCLUDE "sqlitening.inc"
GLOBAL gs AS STRING

FUNCTION PBMAIN () AS LONG
 KILL  "test.db3"
 slOpen ("test.db3", "C")
 'ServicesBill  changed to t1
 slExe "Create Table t1(Date TEXT, BillNo TEXT, HospitalNo TEXT COLLATE NOCASE, Surname1 TEXT, Othernames TEXT, Category TEXT," & _
 "Department TEXT, CodeNo TEXT COLLATE NOCASE, Service TEXT, amount1 REAL, Discount REAL, Status TEXT)"

 'Pharmacy Bill changed to t2
 slExe BUILD$("Create Table t2(RecNo TEXT, SNo TEXT, Date TEXT, InvoiceNo TEXT, HospitalNo TEXT, Surname2 TEXT,", _
 "ProductNo TEXT COLLATE NOCASE, Description TEXT, PatientCategory TEXT, Department TEXT, Quantity INTEGER, SellingPrice REAL, Discount REAL, Username TEXT)")

 'Payment      changed to t3
 slExe BUILD$("Create Table t3(ReceiptNo INTEGER, BillNo INTEGER, Date TEXT, HospitalNo TEXT, Surname3 TEXT, Othernames TEXT,", _
 "Category TEXT, Stage TEXT, PaymentMode TEXT, CheckNo TEXT, Amount3 REAL, Purpose TEXT, Department TEXT, Cashier TEXT)")

 LOCAL x AS LONG
 FOR x = 1 TO 1
  slexebind "insert into t1 values (?,?,?,?,?,?,?,?,?,?,?,?)",    REPEAT$(12,slbuildbindDat("1","T")) '12
  slexebind "insert into t2 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("1","T")) '14
  slexebind "insert into t3 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("1","T")) '14
 NEXT
 gs = CHR$( GetRs("select * from t1"),$CR,$CR,GetRs("select * from t2"),$CR,$CR,GetRs("select * from t3")) + $CR
 Bern
END FUNCTION

FUNCTION Getrs(sql AS STRING) AS STRING
 DIM s() AS STRING
 slSelAry sql,s(),"Q9c"
 FUNCTION = JOIN$(s(),$CR)
END FUNCTION

SUB Bern
LOCAL s AS STRING

s=  "WITH"
s+= "  TempT1(TT1Surname,TT1Total) AS (SELECT Surname1, Amount - Discount)"
s+="    FROM T1,"
s+="  TempT2(TT2Surname,TT2Total) AS (SELECT Surname2, Quantity * SellingPrice - Discount)"
s+="    FROM T2"
s+=" SELECT Surname3, Total( TT1Total) + Total( TT2Total) - Total(Amount3)"
s+="  FROM T3"
s+="  LEFT OUTER JOIN TempT1 ON Surname3 = TT1Surname"
s+="  LEFT OUTER JOIN TempT2 ON Surname3 = TT2Surname"
s+="  WHERE Total( TT1Total) + Total( TT2Total) > Total(Amount3)"
s+="  GROUP BY surname3"

 DIM sArray() AS STRING
 slselAry s,sArray(),"Q9"
 'slSel s,0,"D"
 ? "Incorrectly attempt Bern statement" + $CR + JOIN$(sArray(),$CR) + STRING$(5,$CR) + "Tables contained:" + $CR + gs
END SUB
26
Almost accepted (incorrect use of aggregate Total()

SUB Bern
 LOCAL s AS STRING
 'table1 and table3 surname1,surname3 amount1,amount3

s = " WITH Bills(Name,TotalBill) AS (SELECT Surname1, Total(amount1) + TOTAL(Quantity * SellingPrice - t2.Discount)"
s+="  FROM t1,t2"
'       LEFT OUTER JOIN t2 ON t1.Surname1 = t2.surname2"
s+= "   WHERE surname1 = surname2"
s+="   GROUP BY surname2)"
s+="  SELECT Name,TotalBill FROM Bills"
s+="   LEFT OUTER JOIN t3 ON Bills.Name = surname3"
s+="   WHERE TotalBill > Total(Amount3)"
s+="   GROUP BY surname3

or using OUTER JOIN still returns incorrect use of Total()

SUB Bern
s = " WITH Bills(Name,TotalBill) AS (SELECT Surname1, Total(amount1) + TOTAL(Quantity * SellingPrice - t2.Discount)"
s+="  FROM t1"
s+="   LEFT OUTER JOIN t2 ON t1.Surname1 = t2.surname2"
s+="   GROUP BY surname2)"
s+="  SELECT Name,TotalBill FROM Bills"
s+="   LEFT OUTER JOIN t3 ON Bills.Name = surname3"
s+="   WHERE TotalBill > Total(Amount3)"
s+="   GROUP BY surname3"
27
I missed that incorrect naming SurnameName2 should be "Surname2".
It works now except incorrect use of aggregate Total().

#INCLUDE "sqlitening.inc"
GLOBAL gs AS STRING

FUNCTION PBMAIN () AS LONG
 KILL   "test.db3"
 slOpen ("test.db3", "C")
 'ServicesBill  changed to t1
 slExe "Create Table t1(Date TEXT, BillNo TEXT, HospitalNo TEXT COLLATE NOCASE, Surname1 TEXT, Othernames TEXT, Category TEXT," & _
 "Department TEXT, CodeNo TEXT COLLATE NOCASE, Service TEXT, amount1 REAL, Discount REAL, Status TEXT)"

 'Pharmacy Bill changed to t2
 slExe BUILD$("Create Table t2(RecNo TEXT, SNo TEXT, Date TEXT, InvoiceNo TEXT, HospitalNo TEXT, Surname2 TEXT,", _
 "ProductNo TEXT COLLATE NOCASE, Description TEXT, PatientCategory TEXT, Department TEXT, Quantity INTEGER, SellingPrice REAL, Discount REAL, Username TEXT)")

 'Payment       changed to t3
 slExe BUILD$("Create Table t3(ReceiptNo INTEGER, BillNo INTEGER, Date TEXT, HospitalNo TEXT, Surname3 TEXT, Othernames TEXT,", _
 "Category TEXT, Stage TEXT, PaymentMode TEXT, CheckNo TEXT, Amount3 REAL, Purpose TEXT, Department TEXT, Cashier TEXT)")

 LOCAL x AS LONG
 FOR x = 1 TO 1
  slexebind "insert into t1 values (?,?,?,?,?,?,?,?,?,?,?,?)",     REPEAT$(12,slbuildbindDat("1","T")) '12
  slexebind "insert into t2 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("1","T")) '14
  slexebind "insert into t3 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("1","T")) '14
 NEXT
 gs = CHR$( GetRs("select * from t1"),$CR,$CR,GetRs("select * from t2"),$CR,$CR,GetRs("select * from t3")) + $CR
 Bern
END FUNCTION

FUNCTION Getrs(sql AS STRING) AS STRING
 DIM s() AS STRING
 slSelAry sql,s(),"Q9c"
 FUNCTION = JOIN$(s(),$CR)
END FUNCTION

SUB Bern
 LOCAL s AS STRING
 'table1 and table3 surname1,surname3 amount1,amount3
s = " WITH Bills(Name,TotalBill) AS (SELECT t1.Surname1, t1.amount1 + TOTAL(Quantity * SellingPrice - t2.Discount)"
s+="  FROM t2,t1"
's+="   LEFT OUTER JOIN t2 ON t1.Surname1 = t2.surname2"
s+= "   WHERE surname1 = surname2"
s+="   GROUP BY surname2 )"
s+="  SELECT Name,TotalBill FROM Bills"
s+="   LEFT OUTER JOIN t3 ON Bills.Name = t3.surname3"
s+="   WHERE TotalBill > Total(t3.Amount3)"
s+="   GROUP BY t3.surname3"

 'DIM sArray() AS STRING
 'slselAry s,sArray(),"Q9"
 slSel s,0,"D"
 '? "Incorrectly attempt Bern statement" + $CR + JOIN$(sArray(),$CR) + STRING$(5,$CR) + "Tables contained:" + $CR + gs
END SUB
28
Ambiquous column name errors on variables after first select.
Removed the first with the error and the other are duplicates.
That may be need a few "AS".
Surname for a relational key doesn't seem very reliable.
Since I changed from statement may need to get rid of JOIN.

Added ( before first SELECT and ) before ) SELECT

#INCLUDE "sqlitening.inc"
GLOBAL gs AS STRING

FUNCTION PBMAIN () AS LONG
 KILL   "test.db3"
 slOpen ("test.db3", "C")
 'ServicesBill  changed to t1
 slExe "Create Table t1(Date TEXT, BillNo TEXT, HospitalNo TEXT COLLATE NOCASE, Surname1 TEXT, Othernames TEXT, Category TEXT," & _
 "Department TEXT, CodeNo TEXT COLLATE NOCASE, Service TEXT, amount1 REAL, Discount REAL, Status TEXT)"

 'Pharmacy Bill changed to t2
 slExe BUILD$("Create Table t2(RecNo TEXT, SNo TEXT, Date TEXT, InvoiceNo TEXT, HospitalNo TEXT, SurnameName2 TEXT,", _
 "ProductNo TEXT COLLATE NOCASE, Description TEXT, PatientCategory TEXT, Department TEXT, Quantity INTEGER, SellingPrice REAL, Discount REAL, Username TEXT)")

 'Payment       changed to t3
 slExe BUILD$("Create Table t3(ReceiptNo INTEGER, BillNo INTEGER, Date TEXT, HospitalNo TEXT, Surname3 TEXT, Othernames TEXT,", _
 "Category TEXT, Stage TEXT, PaymentMode TEXT, CheckNo TEXT, Amount3 REAL, Purpose TEXT, Department TEXT, Cashier TEXT)")

 LOCAL x AS LONG
 FOR x = 1 TO 1
  slexebind "insert into t1 values (?,?,?,?,?,?,?,?,?,?,?,?)",     REPEAT$(12,slbuildbindDat("1","T")) '12
  slexebind "insert into t2 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("1","T")) '14
  slexebind "insert into t3 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("1","T")) '14
 NEXT
 gs = CHR$( GetRs("select * from t1"),$CR,$CR,GetRs("select * from t2"),$CR,$CR,GetRs("select * from t3")) + $CR
 Bern
END FUNCTION

FUNCTION Getrs(sql AS STRING) AS STRING
 DIM s() AS STRING
 slSelAry sql,s(),"Q9c"
 FUNCTION = JOIN$(s(),$CR)
END FUNCTION

SUB Bern
 LOCAL s AS STRING
 'table1 and table3 surname1,surname3 amount1,amount3
s = " WITH Bills(Name,TotalBill) AS (SELECT t1.Surname1, t1.amount1 + TOTAL(t2.Quantity * t2.SellingPrice - t2.Discount)"
s+="  FROM t1,t2"
s+="   LEFT OUTER JOIN t2 ON t1.Surname1 = t2.surname2"
s+="   GROUP BY t2.surname2 )"
s+="  SELECT Name,TotalBill FROM Bills"
s+="   LEFT OUTER JOIN t3 ON Bills.Name = t3.surname3"
s+="   WHERE TotalBill > Total(t3.Amount3)"
s+="   GROUP BY t3.surname3"

 DIM sArray() AS STRING
 slselAry s,sArray(),"Q9"
 ? "Incorrectly attempt Bern statement" + $CR + JOIN$(sArray(),$CR) + STRING$(5,$CR) + "Tables contained:" + $CR + gs
END SUB
                              
29
I thought the system was under construction, but obviously already done.
Would have to see what the output is supposed to look like.  Nothing like I have seen.
I renamed tables to t1,t2,t3  to try to keep in brain. (services bill, cash/pharmacy bill, payments)
https://sqlite.org/lang_with.html  'need to study WITH

Bern,
I created some data (not good data for testing.)
Renamed amount and surname so they were not duplicates in table1 and table3
Get misuse of aggregate TOTAL().  Remarked Total and it runs.
Learning.  Never even knew about the WITH statement.

Fredrick,
Could you add a few records to have some relational data to test?

I find it a lot easier to use table  T1,T2,T3 while testing.

#INCLUDE "sqlitening.inc"
GLOBAL gs AS STRING

FUNCTION PBMAIN () AS LONG
 KILL   "test.db3"
 slOpen ("test.db3", "C")
 'ServicesBill  changed to t1
 slExe "Create Table t1(Date TEXT, BillNo TEXT, HospitalNo TEXT COLLATE NOCASE, Surname1 TEXT, Othernames TEXT, Category TEXT," & _
 "Department TEXT, CodeNo TEXT COLLATE NOCASE, Service TEXT, amount1 REAL, Discount REAL, Status TEXT)"

 'Pharmacy Bill changed to t2
 slExe BUILD$("Create Table t2(RecNo TEXT, SNo TEXT, Date TEXT, InvoiceNo TEXT, HospitalNo TEXT, Name TEXT,", _
 "ProductNo TEXT COLLATE NOCASE, Description TEXT, PatientCategory TEXT, Department TEXT, Quantity INTEGER, SellingPrice REAL, Discount REAL, Username TEXT)")

 'Payment       changed to t3
 slExe BUILD$("Create Table t3(ReceiptNo INTEGER, BillNo INTEGER, Date TEXT, HospitalNo TEXT, Surname3 TEXT, Othernames TEXT,", _
 "Category TEXT, Stage TEXT, PaymentMode TEXT, CheckNo TEXT, Amount3 REAL, Purpose TEXT, Department TEXT, Cashier TEXT)")

 LOCAL x AS LONG
 FOR x = 1 TO 1
  slexebind "insert into t1 values (?,?,?,?,?,?,?,?,?,?,?,?)",     REPEAT$(12,slbuildbindDat("1","T")) '12
  slexebind "insert into t2 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("2","T")) '14
  slexebind "insert into t3 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("3","T")) '14
 NEXT
 gs = CHR$( GetRs("select * from t1"),$CR,$CR,GetRs("select * from t2"),$CR,$CR,GetRs("select * from t3")) + $CR
 Bern
END FUNCTION

FUNCTION Getrs(sql AS STRING) AS STRING
 DIM s() AS STRING
 slSelAry sql,s(),"Q9c"
 FUNCTION = JOIN$(s(),$CR)
END FUNCTION

SUB Bern
 LOCAL s AS STRING
 'table1 and table3 surname1,surname3 amount1,amount3
s = " WITH Bills(Name,TotalBill) AS (SELECT Surname1, amount1 + TOTAL(Quantity * SellingPrice - t1.Discount)"
s+="  FROM t1,t3"  'ADDED T3
s+="   LEFT OUTER JOIN t2 ON Surname1 = surname3"
s+="   GROUP BY surname1) "
s+="  SELECT Name,TotalBill FROM Bills"
s+="   LEFT OUTER JOIN t3 ON Bills.Name = surname3"
s+="   WHERE TotalBill > Total(Amount3)"
s+="   GROUP BY surname3"

 DIM sArray() AS STRING
 slselAry s,sArray(),"Q9"
 ? "Incorrectly attempt Bern statement" + $CR + JOIN$(sArray(),$CR) + STRING$(5,$CR) + "Tables contained:" + $CR + gs
END SUB



#INCLUDE "sqlitening.inc"
GLOBAL gs AS STRING

FUNCTION PBMAIN () AS LONG
 KILL  "test.db3"
 slOpen ("test.db3", "C")
 'ServicesBill  changed to t1
 slExe "Create Table t1(Date TEXT, BillNo TEXT, HospitalNo TEXT COLLATE NOCASE, Surname1 TEXT, Othernames TEXT, Category TEXT," & _
 "Department TEXT, CodeNo TEXT COLLATE NOCASE, Service TEXT, amount1 REAL, Discount REAL, Status TEXT)"

 'Pharmacy Bill changed to t2
 slExe BUILD$("Create Table t2(RecNo TEXT, SNo TEXT, Date TEXT, InvoiceNo TEXT, HospitalNo TEXT, Name TEXT,", _
 "ProductNo TEXT COLLATE NOCASE, Description TEXT, PatientCategory TEXT, Department TEXT, Quantity INTEGER, SellingPrice REAL, Discount REAL, Username TEXT)")

 'Payment      changed to t3
 slExe BUILD$("Create Table t3(ReceiptNo INTEGER, BillNo INTEGER, Date TEXT, HospitalNo TEXT, Surname3 TEXT, Othernames TEXT,", _
 "Category TEXT, Stage TEXT, PaymentMode TEXT, CheckNo TEXT, Amount3 REAL, Purpose TEXT, Department TEXT, Cashier TEXT)")

 LOCAL x AS LONG
 FOR x = 1 TO 1
  slexebind "insert into t1 values (?,?,?,?,?,?,?,?,?,?,?,?)",    REPEAT$(12,slbuildbindDat("1","T")) '12
  slexebind "insert into t2 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("2","T")) '14
  slexebind "insert into t3 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", REPEAT$(14,slbuildbindDat("3","T")) '14
 NEXT
 gs = CHR$( GetRs("select * from t1"),$CR,$CR,GetRs("select * from t2"),$CR,$CR,GetRs("select * from t3")) + $CR
 Bern
END FUNCTION

FUNCTION Getrs(sql AS STRING) AS STRING
 DIM s() AS STRING
 slSelAry sql,s(),"Q9c"
 FUNCTION = JOIN$(s(),$CR)
END FUNCTION

SUB Bern
 LOCAL s AS STRING
 'table1 and table3 surname1,surname3 amount1,amount3
s = " WITH Bills(Name,TotalBill) AS (SELECT Surname1, amount1 + TOTAL(Quantity * SellingPrice - t1.Discount)"
s+="  FROM t1,t3"  'ADDED T3 to get to work
s+="  LEFT OUTER JOIN t2 ON Surname1 = surname3"
s+="  GROUP BY surname1) "
s+="  SELECT Name,TotalBill FROM Bills"
s+="  LEFT OUTER JOIN t3 ON Bills.Name = surname3"
s+="  WHERE TotalBill > Total(Amount3)"
s+="  GROUP BY surname3"

 DIM sArray() AS STRING
 slselAry s,sArray(),"Q9"
 ? "Incorrectly attempt Bern statement" + $CR + JOIN$(sArray(),$CR) + STRING$(5,$CR) + "Tables contained:" + $CR + gs
END SUB
30
Sorry, I can't figure this one out.  I do in a different way.

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
 slOpen "Need data"
 ReportDebtors
END FUNCTION

FUNCTION ReportDebtors() AS LONG
 slSel "With Bills(HospitalNo,TotalBill) As (Select HospitalNo, Total(Amount) + Total( Quantity * SellingPrice - Discount) " & _
          "From tblCashSales " & _
          "Left OUTER JOIN tblBill On tblCashSales.HospitalNo = tblBill.HospitalNo " & _
          "GROUP BY HospitalNo" & _
      "Select HospitalNo From Bills " & _
          "Left OUTER JOIN tblPayments On Bills.HospitalNo = tblPayments.HospitalNo " & _
          "WHERE TotalBill > Total( tblPayments.Amount) " & _
          "GROUP BY HospitalNo"
    DO WHILE slGetRow()
      ? slf(1)
    LOOP
END FUNCTION