• Welcome to SQLitening Support Forum.
 

News:

Welcome to the SQLitening support forums!

Main Menu

Debtors List

Started by Fredrick Ughimi, November 05, 2019, 05:19:58 PM

Previous topic - Next topic

Fredrick Ughimi

CJ, Check attached.

#COMPILE EXE
#DIM ALL

#INCLUDE "sqlitening.inc"
#INCLUDE "ddoc_p32.inc"

'DDOC
'  Needed Color Constants (Vb compatible)
'---------------------------------------------------------------------------
%vbBlack = &H0&
%vbRed = &HFF&
%vbGreen = &HFF00&
%vbYellow = &HFFFF&
%vbBlue = &HFF0000
%vbMagenta = &HFF00FF
%vbCyan = &HFFFF00
%vbWhite = &HFFFFFF
%vbGrey = &HC0C0C0
%LIGHT_SHADE = &HE0E0E0

FUNCTION PBDate(sDate AS STRING) AS STRING
     'MM-dd-yyyy to dd-MM-yyyy

     FUNCTION = MID$(sDate,4,2) & "-" & MID$(sDate,1,2) & "-" & MID$(sDate,7,4)

END FUNCTION

FUNCTION NigeriaDate(sDate AS STRING) AS STRING
     'yyyy-MM-dd  to dd-MM-yyyy
     FUNCTION = MID$(sDate,9,2) & "-" & MID$(sDate,6,2) & "-" & MID$(sDate,1,4)

END FUNCTION

FUNCTION SQLiteDate(sDate AS STRING) AS STRING
     'From dd-MM-yyyy To yyyy-MM-dd

     FUNCTION = MID$(sDate,7,4) & "-" & MID$(sDate,4,2) & "-" & MID$(sDate,1,2)

END FUNCTION

FUNCTION PBMAIN () AS LONG

     slOpen ("HospitalProDB.db3", "C")

     ReportDebtors

END FUNCTION

FUNCTION ReportDebtors() AS LONG
      LOCAL dbHandle&, LineSpacing!,iHandle%,Row!,TopMargin!,PageSize!,PageNumber&
      LOCAL detailline AS ASCIIZ * 200
      LOCAL SNo AS LONG
      LOCAL lTotalRecords&
      LOCAL sFrom AS STRING
      LOCAL sTo AS STRING
      LOCAL dTotalAmount AS DOUBLE
      LOCAL dNetAmount AS DOUBLE

      LineSpacing = .2
      TopMargin   = .5
      PAGESIZE    = 10

      ihandle% = dpStartDoc(0,"Mega-Net HospitalPro - Debtors Report","",%DDOC_INCH, %DDOC_PAPER_A4, %DDOC_LANDSCAPE, %DDOC_SYSTEM_DEFAULT, %DDOC_BIN_AUTO OR %DDOC_ALLOWSMTP OR %DDOC_ALLOWSAVE OR %DDOC_ZOOMFIT)
      IF ihandle < 1 THEN
           MSGBOX "Could not StartDoc. Error number" + STR$(Ihandle)
           GOTO EndProgram
      END IF

      dpSetTabs iHandle, "L.5W1 L1.W1 L2.8W1 L5.4W2"

      ROW = PAGESIZE                            'force new heading

      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()

        IF ROW => PAGESIZE THEN
           IF PageNumber THEN      'If first page don't need a new page
              dpNewPage iHandle%,  %ddoc_PAPER_A4, %DDOC_LANDSCAPE, %DDOC_BIN_AUTO
           END IF
           INCR PageNumber
           LOCAL hjpg AS LONG
             hjpg = dpAddGraphic(ihandle%, EXE.PATH$ & "Logo.jpg")
             IF hjpg THEN
                dpDrawGraphic ihandle%, hjpg,5.5,.2,2,1
             END IF

           dpText iHandle%, .5,TopMargin,  %DDOC_Left,"Page: "+ STR$(PageNumber)+""
           dpText iHandle%, .5,TopMargin + .2,  %DDOC_Left,"Total Records: "+ STR$(lTotalRecords&)+""
           dpText iHandle%, 10,TopMargin,  %DDOC_Left,"Date: "+ PBDate(DATE$)+""
           dpText iHandle%, 10,TopMargin +.2,  %DDOC_Left,"Time: "+ TIME$+""
           ROW =TopMargin + LineSpacing * 4
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbGreen, "Arial"
           'dpText ihandle, 6.25, 1.1, %DDOC_CENTER, "" & gName
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbRed, "Arial"
           'dpText IHandle%, 6.25, 1.4, %DDOC_CENTER, "" & gAddress
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 12, %vbBlack, "Arial"
           dpText IHandle%, 6.25, 1.7, %DDOC_CENTER, "Debtors Report"
           dpText IHandle%, 6.25, 2.0, %DDOC_CENTER, "Date Range:  " & sFrom  & " To " & sTo
           dpText IHandle%, .5, 2.3, %DDOC_Left, "Total Amount: " & FORMAT$(dTotalAmount, "#,.00")
           dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 9, %vbBlack, "Arial"
           dpTabText iHandle%, ROW + 1.3, "S/No" + $TAB + "HNO"  + $TAB + "Surname" + $TAB + "Othernames"
           dpFont ihandle%, %DDOC_FONTNORMAL, 8, %vbBlack, "San Serif"
           ROW =  ROW + LineSpacing * 2
        END IF

        DetailLine$ =  _
          STR$(SNo) + $TAB + _
          slFN("HospitalNo") + $TAB + _
          slFN("Surname") + $TAB + _
          slFN("Othernames")

          dpTabText iHandle%, ROW + 1.1, DetailLine
          ROW = ROW + LineSpacing                    'increment line counter

      LOOP

      dpEndDoc iHandle%, %DDOC_END_VIEW + %DDOC_END_PRINT
    EndProgram:
 END FUNCTION
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

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

Fredrick Ughimi

Thanks CJ. I would give it a shot in the morning.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

CJ, Same error near HospitalNo

#COMPILE EXE
#DIM ALL

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
 slOpen ("HospitalProDB.db3", "C")
 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 [b]HospitalNo[[/b] 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             
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Bern Ertl

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] ...

cj

#20
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

Bern Ertl

#21
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.

cj

#22
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
                              

cj

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

cj

#24
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"

Bern Ertl

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:

WITH
   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

#26
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

Bern Ertl

Ooops  (red face).  I misplaced the end parenthesis on the WITH statements....

WITH
   TempT1(TT1Surname,TT1Total) AS (SELECT Surname1, Amount - Discount
     FROM T1),
   TempT2(TT2Surname,TT2Total) AS (SELECT Surname2, Quantity * SellingPrice - Discount
     FROM T2)
...

cj

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
                                 

Fredrick Ughimi

Hello,

What are you guys reading to understand CTEs apart from the official documentation? Is it a new feature added to SQLite? It seems CTEs are powerful arsenal (a supporter of Arsenal by the way) to have.

Kind regards.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet