SQLitening Support Forum

Quote from: Fredrick Ughimi on December 10, 2019, 01:56:25 pm...
Yes, there is a master table I called tblBioData which contains the HospitalNo, Surname, Othernames, etc.
...

Then this should work (adjust table names to match your code):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 tblBioData"
s+="   LEFT OUTER JOIN SumT1 ON tblBioData.Surname = ST1Surname"
s+="   LEFT OUTER JOIN SumT2 ON tblBioData.Surname = ST2Surname"
s+="   LEFT OUTER JOIN SumT3 ON tblBioData.Surname = ST3Surname"
s+="   WHERE ST1Total + ST2Total > ST3Total"

Hello CJ,

>>The dummy columns t1,t2,t3 are required to get the correct results.  Please test!

I will test it as soon as I am with my computer. Thanks a lot.

Hello Bern,

>>That's because I used INNER JOINs. You can use LEFT OUTER JOINS if you want results from partial matches, but even so, the table referenced after FROM will control the results set, so the results only return for records in that table. 

Thank you for your explanations.


>>Like I said previously, if you have a fourth table that contains a master list of the Surnames/HopsitalNos, you can use that as the FROM reference and LEFT OUTER JOIN the 3 "SUM" tables from the WITH clause and get a full result set.


Yes, there is a master table I called tblBioData which contains the HospitalNo, Surname, Othernames, etc.

I will follow your instructions and see how it goes. Thanks a lot.

Quote from: Fredrick Ughimi on December 10, 2019, 10:14:35 am...
>>Bern routine results: 1 payment record must exist to return results for a client

I also noticed that all three tables needs to have data for a client to return results for that client.

That's because I used INNER JOINs. You can use LEFT OUTER JOINS if you want results from partial matches, but even so, the table referenced after FROM will control the results set, so the results only return for records in that table. 

Like I said previously, if you have a fourth table that contains a master list of the Surnames/HopsitalNos, you can use that as the FROM reference and LEFT OUTER JOIN the 3 "SUM" tables from the WITH clause and get a full result set.

Hello Cj,

I tried implementing your version today:

#COMPILE EXE
#DIM ALL

'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 PBMAIN () AS LONG
   LOCAL sData(),sBind AS STRING, x AS LONG

   slopen "HospitalProDB.db3","C"
   slexe  "drop table if exists TempBalances;create table TempBalances(HospitalNo unique,Amount)"

 'combine tables and payments
 slselary " select HospitalNo,SUM(Amount),'tblBill' from tblBill group by HospitalNo"  +_     'charges table1
          " union "                                      +_
          " select HospitalNo,SUM(SellingPrice * Quantity - Discount) as Amount,'tblCashSales' from tblCashSales group by HospitalNo"  +_     'charges table2
          " union "                                      +_
          " select HospitalNo,SUM(Amount),'tblPayments' from tblPayments group by HospitalNo",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(HospitalNo) DO update SET Amount=Amount+" + 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

I got incomplete input error.

>>Bern routine results: 1 payment record must exist to return results for a client

I also noticed that all three tables needs to have data for a client to return results for that client.

>>Very interesting read:
https://proprivacy.com/vpn/comparison/nigeria-vpn

Thanks for the link. Interesting read.

Very interesting read:
https://proprivacy.com/vpn/comparison/nigeria-vpn

I have been successfully using SQLitening over a VPN between machines that share the exact same dedicated VPN server using the same user name and password.  This might be the best way to go if this is allowed.
I paid nothing extra and didn't need any dedicated IP address (other then share same exact server.)

I will finally have time to work on encrypting SQLitening on 12/3.

https://help.logmein.com/pkb_Home?q=share%20a%20port&l=en_US&c=products%3Almihamachi2&atype=FAQ__kav

It looks like you share a folder and use Windows mapping.
I only connected 2 machines and used chat.
I didn't use SQLite with it.
There are links to PDF files at the bottom of their home page.
Many use it so there must be better docs on the net, don't know where.

Hello CJ,

>>I got Hamachi to connect, but didn't go any further.

How did you do it?

Windows 10 has a built-in VPN server, but I couldn't connect.
Some on the internet say there is a problem introduced in a Windows update.
https://pureinfotech.com/setup-vpn-server-windows-10/

I got Hamachi to connect, but didn't go any further.

I'll go back to encrypting the TCP data being transmit to the server.