• Welcome, Guest. Please login.
November 21, 2019, 03:10:21 pm


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 - Bern Ertl

OK.  I played with cj's code and found the problem.  You cannot use Total() in a WHERE clause.  I reworked the statement and it compiles/works now.

#INCLUDE "sqlitening.inc"

 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 REAL, 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)")

 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
 gs = CHR$( GetRs("select * from t1"),$CR,$CR,GetRs("select * from t2"),$CR,$CR,GetRs("select * from t3")) + $CR

 slSelAry sql,s(),"Q9c"

SUB Bern

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+="   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, ST1Total + ST2Total - ST3Total"
s+="   FROM SumT3"
s+="   INNER JOIN SumT1 ON ST3Surname = ST1Surname"
s+="   INNER JOIN SumT2 ON ST3Surname = ST2Surname"
s+="   WHERE ST1Total + ST2Total > ST3Total"

 slselAry s,sArray(),"Q9"
 'slSel s,0,"D"
 ? "Incorrectly attempt Bern statement" + $CR + JOIN$(sArray(),$CR) + STRING$(5,$CR) + "Tables contained:" + $CR + gs

The output doesn't show anything because it's computing zero for TT1Total and TT2Total with the dummy data in this code.  You can remove the WHERE clause from the statement to see it's computing -1 for ST1Total + ST2Total - ST3Total. 

This statement flattens all three tables into a single record per Surname/HospitalNo.  The final SELECT is using T3 as the leftmost FROM table, so the output will only list Surname/HospitalNo records if a record exists in (Sum)T3.  If you want results where no record (for Surname/HospitalNo) exists in T3, you would need to adjust the statement to base the results on either SumT1 or SumT2.

I'm not sure how to combine the output to list every Surname/HospitalNo record that exists across all three tables with the db schema as listed.  If you have another table with a master list of Surname/HospitalNo records, you could use that as the base and use LEFT OUTER JOINS on SumT1, SumT2 and SumT3.
Ooops  (red face).  I misplaced the end parenthesis on the WITH statements....

   TempT1(TT1Surname,TT1Total) AS (SELECT Surname1, Amount - Discount
     FROM T1),
   TempT2(TT2Surname,TT2Total) AS (SELECT Surname2, Quantity * SellingPrice - Discount
     FROM T2)
I wasn't sure if TOTAL(Quantity * SellingPrice - t2.Discount) would work as I was expecting.  If that is what SQLite is choking on, the statement will need to be reworked a bit.  Something like this:

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

I noticed that OP's table definitions included a discount field in T1, but it wasn't included in the desired logic.  Above code assumes that was an oversight and any discounts in T1 should be considered in the calculations.
cj, I'm not near my work (compiling) computer, so I can't test, but it looks to me like your latest test code can (and should) be tweaked:

'Pharmacy Bill changed to t2 (** changed "Name" to "Surname2" **)
 slExe BUILD$("Create Table t2(RecNo TEXT, SNo TEXT, Date TEXT, InvoiceNo TEXT, HospitalNo TEXT, Surname2 TEXT,", _
s+="  FROM t1"  '**Removed the** ADDED T3
s+="   LEFT OUTER JOIN t2 ON Surname1 = Surname2"  'Surname2 is in t2

Also, it's not entirely clear, but I thought the discount field referenced in the total() function was also in t2.  I thought all three fields ( Quantity, SellingPrice, Discount ) were being pulled from t2.  Only amount1 was being pulled from t1 for the TotalBill calculation.
You both need to add a space at the end of the first "GROUP BY HospitalNo" string segment.

... GROUP BY HospitalNo Select [b]HospitalNo[[/b] ...
*not* (as you have posted above):

... GROUP BY HospitalNoSelect [b]HospitalNo[[/b] ...
Quote from: Fredrick Ughimi on November 07, 2019, 06:02:17 pm...
The tblPayments is used to store patients payments against the bills on services (tblBill) and pharmacy (tblCashSales). Installment payments are allowed to clear up the bills.

Looks like you changed some of the table names from what you posted in the OP.  Please make sure that all the table names in the code are correct:slSel "With Bills(Name,TotalBill) As (Select Surname, Total(Amount) + Total( Quantity * SellingPrice - Discount) " & _
          "From ***tblBill*** " & _
          "Left OUTER JOIN tblPharmacyBill On ***tblCashSales***.Surname = tblPharmacyBill.Surname " & _
          "GROUP BY Surname" & _
      "Select Name From Bills " & _
          "Left OUTER JOIN tblPayments On Bills.Name = tblPayments.Surname " & _
          "WHERE TotalBill > Total( tblPayments.Amount) " & _
          "GROUP BY Surname"

*** => should be the same table - whichever is the correct table name.
I think you need to add a space character after "Surname" in line 4:

"GROUP BY Surname " & _

Quote from: Fredrick Ughimi on September 05, 2019, 03:57:53 pmHello,

I am stump trying to figure out how to automatically 'trigger' this sequence of SQL startements. I wonder if Trigger is the best condidate.
But triggers are only triggered when an INSERT, DELETE or UPDATE occurs. I want the statements triggered when this conditions are met:

If (sDepartureDate < Date$) Or (sDepartureDate = Date$ And Time$ > "12") Then
End If

Any help would be appreciated.

TRIGGERs are great.  You will likely need 3 triggers to cover all possible data changes that might impact the condition you want monitored:  INSERT and DELETE on tblRegistration and UPDATE on tblRegistration.DepartureDate
Quote from: cj on July 21, 2019, 11:15:22 am2. Is this to add a time stamp to
  Every insert (unlikely)
  Create a table logging all entries?
  Create a flat file text log on disk?

3. This is for the server, right?

My original code is for generating time stamps on the server.  The SQLite custom function can be called/used from within SQL statements.  This allows for transaction processing using TRIGGERs.  For example, when modifying data in a table (insert, update, delete), a trigger could log the change made and server time in another (transaction) table.  The use of a TRIGGER with the custom function ensures that the logging is atomic with the change.
Quote from: Fim on October 16, 2019, 01:02:33 pm... how to get it from the server?

I posted some code years ago for creating and using a server side time stamp.  It uses Windows FILETIME data, but it's easily converted to whatever format you need:

Quote from: Fredrick Ughimi on July 20, 2019, 08:13:27 pm...
What is the difference SQLiteningProcsA and SQLiteningProcsB?

It doesn't matter. 


You can use any of them.  SQLitening allows you to organize your server side code libraries into separate files with this naming convention, but there is no difference as to which you use.  It's just for you to organize your code.

I mainly use SQLiteningProcsT and SQLiteningProcsP for my server code.  The "T" library contains server code that is loaded, used and unloaded (ie. "Temporary").  The "P" library contains code that is loaded and stays loaded until the app closes (ie. "Permanent").
I believe that you can get the list in one single SQL SELECT statement.  Something like this should work (untested):

WITH Bills(Name,TotalBill) AS (SELECT Surname, Total(Amount) + Total( Quantity * SellingPrice - Discount)
   FROM tblServicesBill
   LEFT OUTER JOIN tblPharmacyBill ON tblServicesBill.Surname = tblPharmacyBill.Surname
   GROUP BY Surname
   LEFT OUTER JOIN tblPayments ON Bills.Name = tblPayments.Surname
   WHERE TotalBill > Total( tblPayments.Amount)
   GROUP BY Surname
I am using SQLitening in conjunction with a grid control (Farpoint's Spread).  I usually add a hidden column or two for each row to save the rowID (and possibly other data) when populating the grid control with data.  When any of the data is edited, it's easy to look up the corresponding rowID and update the database.

You've got Questions? We've got Answers! / Re: Audit Trail
September 12, 2018, 09:34:28 am
Yeah, I created a system that allows me to log every SQL statement being executed in a transaction table.  See here:

I just went through this for a few vBulletin forums that I own over the last three days.  Not fun.

I had http://sqlitening.planetsquires.com/support/index.php bookmarked and was getting all kinds of error messages for the last few times I tried to access the forum.  Might be a good idea to either put a simple "hey, we've moved to this URI" page there or set a redirect from that old address to the new index/home page.