• Welcome, Guest. Please login.
 
June 16, 2019, 10:06:00 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

2
Instead of listing debits and then credits, how about different reports just listing the table?

DATE/CLIENT REPORT
DATE     CLIENT     TYPE           DEBIT       CREDIT    PAGE 1
------   ------     -------        ------      ------
6/6/19   1          Car            1000        1000   
                    Car            3000          50   
                    Car             400          20

6/7/19   2          Horse          1000        1000   
                    Horse          3000          50   
                    Horse           400          20




CLIENT/DATE REPORT
CLIENT   DATE       TYPE           DEBIT       CREDIT    PAGE 1
------   ------     -------        ------      ------
1        6/6/19     Car            1000        1000   
                    Car            3000          50   
                    Car             400          20

2        6/7/19     Horse          1000        1000   
                    Horse          3000          50   
                    Horse           400          20
3
I think there should only be one line counter.
Lines will print over themselves in this demo.
#COMPILE EXE
#DIM ALL

#INCLUDE "sqlitening.inc"
#INCLUDE "ddoc_p32.inc"
%DropTable = 1

MACRO WRAP(p1)  = WRAP$(p1,$SQ,$SQ)
MACRO UNWRAP(p1) = UNWRAP$(p1,$SQ,$SQ)
MACRO BindT(p1)  = slBuildBindDat((p1),"T")
GLOBAL gName,gAddress AS STRING
GLOBAL gbytes AS LONG

%vbBlack=&H0&:%vbRed=&HFF&:%vbGreen=&HFF00&:%vbYellow=&HFFFF&:%vbBlue=&HFF0000
%vbMagenta=&HFF00FF:%vbCyan=&HFFFF00:%vbWhite=&HFFFFFF:%vbGrey=&HC0C0C0:%LIGHT_SHADE=&HE0E0E0

FUNCTION PBMAIN () AS LONG 'fredrick10.bas
 slOpen "junk1.db3","C"
 IF %DropTable THEN
  slexe  "drop table if exists tblJournalEntry"
 END IF
 slexe  "create table if not exists tblJournalEntry(AccountName,AccountType,Date,Credit INTEGER, Debit INTEGER)"
 InsertData
 gName="Green line":gAddress = "Red line"
 'slOpen "meganetERPDB.db3","C"
 PrintReport
END FUNCTION

FUNCTION PrintReport() AS LONG

  LOCAL PageHeightMaximum AS SINGLE
  LOCAL iHandle,EndCode,TheBin,TheStyle,DefaultZoom,NoDialog,x,pagenum,fontsize,fontstyle,defaultcolor AS LONG
  LOCAL zOutputFile,zDocTitle,zDefaultFont AS ASCIIZ * 64
  LOCAL lineheight,LeftMargin AS SINGLE  'height deleted
  LOCAL AssetsHeight AS SINGLE
  LOCAL EquityHeight AS SINGLE
  LOCAL RevenueHeight AS SINGLE
  LOCAL ExpensesHeight AS SINGLE
  LOCAL NetProfitHeight AS SINGLE
  LOCAL szExpensesAccount AS ASCIIZ * 200
  LOCAL szExpensesAmount AS ASCIIZ * 100
  LOCAL szRevenueAccount AS ASCIIZ * 200
  LOCAL szRevenueAmount AS ASCIIZ * 100
  LOCAL szEquityAccount AS ASCIIZ * 200
  LOCAL szEquityAmount AS ASCIIZ * 100
  LOCAL szAssetsAccount AS ASCIIZ * 200
  LOCAL szAssetsAmount AS ASCIIZ * 100
  LOCAL sName,sAddress, sFrom, sTo AS STRING
  LOCAL dTotalRevenue, dTotalExpenses, dNetProfit, dTotalCredit, dTotalDebit AS DOUBLE

  sName="Green line":sAddress = "Red line"
  sFrom = "01-01-2019"
  sTo  = "28-05-2019"


  PageHeightMaximum = 10.1  'inches

  defaultzoom = %DDOC_ZOOM75
  zdefaultFont = "Courier New"
  TheStyle = %ddoc_Portrait
  fontsize = 8
  TheBin = %DDOC_Bin_Auto '7
  'topmargin= 1.6    'not in use
  lineheight = .1666 '1/6  'each line height
  LeftMargin = 0.5



  zdoctitle = "Mega-Net ERP - General Ledger Summary Report"
  DefaultZoom=%DDOC_ZOOMFIT + %DDOC_VIEWBUILD
  'DefaultZoom=%DDOC_ZOOM75  + %DDOC_VIEWBUILD
  'DefaultZoom=%DDOC_ZOOM100 + %DDOC_VIEWBUILD

  ihandle = dpStartDoc(0,_
                      zDocTitle, _
                      zoutputfile,_
                      %DDOC_INCH,_
                      %DDOC_PAPER_A4,_
                      %DDOC_SYSTEM_DEFAULT, _
                      TheBin,_
                      DefaultZoom&)
  IF ihandle < 1 THEN
    SELECT CASE ihandle
      CASE %DDOC_NOHANDLES  : ? "no file handles",,"dpStartDoc"
      CASE %DDOC_BADFILE    : ? "bad file name",,  "dpStartDoc"
      CASE %DDOC_FILEEXISTS
        ?  zoutputfile + " already exists so will delete document",,"dpStartDoc"
        KILL zoutputfile
        IF ERR THEN ? "Could not delete file",,EXE.NAME$
      CASE ELSE:      ?  "Error code" + STR$(iHandle),,"dpStartDoc"
    END SELECT
    EXIT FUNCTION
  END IF


  GOSUB PrintHeading

  'Revenue
  dpFont ihandle, %DDOC_FONTNORMAL, 8, %vbBlack, "Courier"
  slSel "Select AccountName, AccountType, Date, SUM(Credit) as CreditSUM From tblJournalEntry " & _
  "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Revenue' GROUP BY AccountName"
    DO WHILE slGetRow()
      szRevenueAccount = slFN("AccountName")
      szRevenueAmount = FORMAT$(VAL(slFN("CreditSUM")), "#,.00")
    GOSUB PrintRevenueDetailLine
    'IF height => PageHeightMaximum THEN
    IF RevenueHeight => PageHeightMaximum THEN
      INCR pagenum
      dpNewPage iHandle,%ddoc_PAPER_LETTER, TheStyle, TheBin
      GOSUB PrintHeading
      dpFont ihandle, %DDOC_FONTNORMAL, 8, %vbBlack, "Arial"
    END IF
  LOOP
  SLEEP 200

  'Expenses
  dpFont ihandle, %DDOC_FONTNORMAL, 9, %vbBlack, "Courier"
  slSel "Select AccountName, AccountType, Date, SUM(Debit) as DebitSUM From tblJournalEntry " & _
  "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Expenses' GROUP BY AccountName"
  DO WHILE slGetRow()
      szExpensesAccount =  slFN("AccountName")
      szExpensesAmount = FORMAT$(VAL(slFN("DebitSUM")), "#,.00")
    GOSUB PrintExpensesDetailLine
    'IF height => PageHeightMaximum THEN
    IF ExpensesHeight => PageHeightMaximum THEN
      INCR pagenum
      dpNewPage iHandle,%ddoc_PAPER_LETTER, TheStyle, TheBin
      GOSUB PrintHeading
      dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
    END IF
 LOOP
  SLEEP 200

  'Assets
  dpFont ihandle, %DDOC_FONTNORMAL, 5, %vbBlack, "Courier"
  LOCAL s AS STRING
  slSel "Select  AccountName, AccountType, Date, SUM(Debit) as DebitSUM From tblJournalEntry " & _
  " WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Assets' GROUP BY AccountName"
  DO WHILE slGetRow()
      szAssetsAccount = slFN("AccountName")
      szAssetsAmount = FORMAT$(VAL(slFN("DebitSUM")), "#,.00")
      GOSUB PrintAssetsDetailLine
    'IF height => PageHeightMaximum THEN
    IF AssetsHeight => PageHeightMaximum THEN
      INCR pagenum
      dpNewPage iHandle,%ddoc_PAPER_LETTER, TheStyle, TheBin
      GOSUB PrintHeading
      dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
    END IF
  LOOP
  SLEEP 200

  'Equity
  dpFont ihandle, %DDOC_FONTNORMAL, 8, %vbBlack, "Courier"
  slSel "Select AccountName, AccountType, Date, SUM(Credit) as CreditSUM From tblJournalEntry " & _
  "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Equity' GROUP BY AccountName"
    DO WHILE slGetRow()
      szEquityAccount = slFN("AccountName")
      szEquityAmount = FORMAT$(VAL(slFN("CreditSUM")), "#,.00")
    GOSUB PrintEquityDetailLine
    'IF height => PageHeightMaximum THEN
    IF RevenueHeight => PageHeightMaximum THEN
      INCR pagenum
      dpNewPage iHandle,%ddoc_PAPER_LETTER, TheStyle, TheBin
      GOSUB PrintHeading
      dpFont ihandle, %DDOC_FONTNORMAL, 8, %vbBlack, "Arial"
    END IF
  LOOP
  SLEEP 200

  'Total Credit
  slSel "Select AccountName, AccountType, Date, SUM(Credit) as TotalCredit From tblJournalEntry " & _
          "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "'"
  IF ISTRUE slGetRow() THEN
    dTotalCredit = VAL(slFN("TotalCredit"))
  END IF
  slCloseSet

  'Total Debit
  slSel "Select AccountName, AccountType, Date, SUM(Debit) as TotalDebit From tblJournalEntry " & _
          "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "'"
  IF ISTRUE slGetRow() THEN
    dTotalDebit = VAL(slFN("TotalDebit"))
  END IF
  slCloseSet

  GOSUB PrintTotalsDetailLine

  'Revenue
  slSel "Select AccountName, AccountType, Date, SUM(Credit) as TotalRevenue From tblJournalEntry " & _
          "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Revenue'"
  IF ISTRUE slGetRow() THEN
    dTotalRevenue = VAL(slFN("TotalRevenue"))
  END IF
  slCloseSet
  'GOSUB PrintHeading

  'Expenses
  slSel "Select AccountName, AccountType, Date, SUM(Debit) as TotalExpenses From tblJournalEntry " & _
          "WHERE Date BETWEEN '" + mySQLiteDate(sFrom) + "' AND '" + mySQLiteDate(sTo) + "' AND AccountType = 'Expenses'"
  IF ISTRUE slGetRow() THEN
    dTotalExpenses = VAL(slFN("TotalExpenses"))
  END IF
  slCloseSet
  'GOSUB PrintHeading

  dNetProfit = dTotalRevenue - dTotalExpenses

  GOSUB PrintNetProfitDetailLine

  endCode = %DDOC_END_VIEW + %DDOC_END_DELETE
  dpEndDoc iHandle, EndCode

EXIT FUNCTION

PrintHeading:
  dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 13, %vbGreen, "Arial"
  dpText ihandle, 4.25, 1.1, %DDOC_CENTER, "" & sName
  dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbRed, "Arial"
  dpText IHandle, 4.25, 1.3, %DDOC_CENTER, "" & sAddress
  dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 12, %vbBlack, "Arial"
  dpText IHandle, 4.25, 1.5, %DDOC_CENTER, "General Ledger Summary Report"
  dpText IHandle, 4.25, 1.8, %DDOC_CENTER, "Date Range: " & sFrom  & " To  " & sTo
  dpLine IHandle, .5, 10.3, 8, 10.3, 1, %vbBlack
  'dpText IHandle, .5, 10.6, %DDOC_LEFT, "Total Debit: " & FORMAT$(dTotalDebit, "#,.00")
  'dpText IHandle, 3.5, 10.6, %DDOC_LEFT, "Total Credit: " & FORMAT$(dTotalCredit, "#,.00")
  dpLine IHandle, .5, 11.0, 8, 11.0, 1, %vbBlack
  dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 11, %vbBlack, "Arial"
  RevenueHeight = 2.5
  dpText IHandle, .5, RevenueHeight, %DDOC_LEFT, "Revenue: "
  ExpensesHeight = 3.5
  dpText IHandle, .5, ExpensesHeight, %DDOC_LEFT, "Expenses: "
  NetProfitHeight = 6.5
  dpText IHandle, .5, NetProfitHeight, %DDOC_LEFT, "Net Profit: "
  AssetsHeight = 7.5
  dpText IHandle, .5, AssetsHeight, %DDOC_LEFT, "Assets: "
  EquityHeight = 9.0
  dpText IHandle, .5, EquityHeight, %DDOC_LEFT, "Equity: "
  RevenueHeight+= lineheight +.05 'looks a bit better
  ExpensesHeight+= lineheight +.05 'looks a bit better
  AssetsHeight+= lineheight +.05 'looks a bit better
  EquityHeight+= lineheight +.05 'looks a bit better
  'NetProfitHeight+= lineheight +.05 'looks a bit better
RETURN

PrintRevenueDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,LeftMargin, RevenueHeight,%ddoc_left,szRevenueAccount
  dpText iHandle,7.0, RevenueHeight,%DDOC_DECIMAL,szRevenueAmount
  RevenueHeight+= lineheight
RETURN

PrintExpensesDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,LeftMargin, ExpensesHeight,%ddoc_left,szExpensesAccount
  dpText iHandle,5.0, ExpensesHeight,%ddoc_decimal,szExpensesAmount
  ExpensesHeight+= lineheight
RETURN

PrintNetProfitDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,4.5, NetProfitHeight,%ddoc_left,FORMAT$(dNetProfit, "#,.00")
  NetProfitHeight+= lineheight
RETURN

PrintAssetsDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,LeftMargin, AssetsHeight,%ddoc_left, szAssetsAccount
  dpText iHandle,5.0, AssetsHeight,%DDOC_DECIMAL, szAssetsAmount
  AssetsHeight+= lineheight
RETURN

PrintEquityDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,LeftMargin, EquityHeight,%ddoc_left, szEquityAccount
  dpText iHandle,7.0, EquityHeight,%DDOC_DECIMAL, szEquityAmount
  EquityHeight+= lineheight
RETURN

PrintTotalsDetailLine:
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  'dpText iHandle,LeftMargin, AssetsHeight,%ddoc_left,szAssets
  dpText IHandle, 0.5, 10.6, %DDOC_LEFT, "Total: "
  dpText IHandle, 5.0, 10.6, %DDOC_DECIMAL, FORMAT$(dTotalDebit, "#,.00") & " Dr"
  'dpText IHandle, 5.7, 10.6, %DDOC_LEFT, "Total Credit: "
  dpText IHandle, 7.0, 10.6, %DDOC_DECIMAL, FORMAT$(dTotalCredit, "#,.00") & " Cr"

  AssetsHeight+= lineheight
RETURN
END FUNCTION

FUNCTION mySQLiteDate(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

SUB InsertData
  LOCAL x        AS LONG
  LOCAL acctnum  AS LONG
  LOCAL numaccts  AS LONG
  LOCAL numrecs  AS LONG
  LOCAL counter  AS LONG
  LOCAL bytes    AS LONG
  'gbytes = 0
  numaccts  = 99
  numrecs    = 100
  DIM s(1 TO numaccts * numrecs) AS STRING
  slexe "begin exclusive"
  FOR acctnum = 1 TO numaccts  'account
    FOR x =1 TO numrecs        'insert this many into account
      INCR counter
      s(counter) = bindt(USING$("Acct #",acctnum)) + bindt("Assets") + bindt("2019-05-28") + bindi(STR$(acctnum)) + bindi(STR$(acctnum))
      gbytes+=LEN(s(counter))
    NEXT
  NEXT
  slexebind "insert into tblJournalEntry values(?,?,?,?,?)",JOIN$(s(),""),"V5"
  slexe "end"

  counter = 0
  REDIM s(1 TO numaccts * numrecs) AS STRING
  slexe "begin exclusive"
  FOR acctnum = 1 TO numaccts  'account
    FOR x =1 TO numrecs        'insert this many into account
      INCR counter
      s(counter) = bindt(USING$("Acct #",acctnum)) + bindt("Revenue") + bindt("2019-05-28") + bindi(STR$(acctnum)) + bindi(STR$(acctnum))
      gbytes+=LEN(s(counter))
    NEXT
  NEXT
  slexebind "insert into tblJournalEntry values(?,?,?,?,?)",JOIN$(s(),""),"V5"
  slexe "end"

END SUB
4
Aligning non-fixed length fonts is in the ddoc demo in Fancy text.
The TestP32.Bas demo needs a slight change in WinMain to compile with PbWin10.

BYVAL lpCmdLine    AS ASCIIZ PTR, _

Here is the line that should do what you want:
dpText hFile%, 4.25, 4.15, %DDOC_DECIMAL, "Finally, decimal aligned  $9.99"
5
In your previous code you correctly checked the line counter after each print.

GOSUB PrintAssetsDetailLine
IF AssetsHeight => PageHeightMaximum THEN

Might do that again:

GOSUB PrintDetailLine1
If linecounter => PageHeightMaximum THEN

GOSUB PrintDetailLine2
If linecounter => PageHeightMaximum THEN

6
I don't see the line counter Row& being reset after a new page.
Never mind, I see row = pagesize

The previous code used Gosub when a new page and gosub to detail line, which was nice.
Totally different code.

It may be that the Row is not incremented correctly after each print?
I also saw a $CRLF in the code (don't know what ddoc would do with that?)
 dpTabText iHandle%, Row + 1.6, DetailLine1
 Row = Row + LineSpacing                    'increment line counter
 dpText IHandle%, .5, 3.3, %DDOC_LEFT, "Expenses: "
7
%DropTable = 0

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

MACRO WRAP(p1)   = WRAP$(p1,$SQ,$SQ)
MACRO UNWRAP(p1) = UNWRAP$(p1,$SQ,$SQ)
MACRO BindT(p1)  = slBuildBindDat((p1),"T")
%vbBlack=&H0&:%vbRed=&HFF&:%vbGreen=&HFF00&:%vbYellow=&HFFFF&:%vbBlue=&HFF0000
%vbMagenta=&HFF00FF:%vbCyan=&HFFFF00:%vbWhite=&HFFFFFF:%vbGrey=&HC0C0C0:%LIGHT_SHADE=&HE0E0E0
GLOBAL gName,gAddress AS STRING
GLOBAL gbytes AS LONG

FUNCTION PBMAIN () AS LONG  'fredrick11.bas

  LOCAL sIpAddress AS STRING, portnumber AS LONG

  'sIpAddress = "" 'unremark to test server
  'portnumber =    'unremark to test server

  IF LEN(sIpAddress) THEN
    IF slConnect(sIpAddress,portnumber,"E0") THEN
      ? slGetError,,USING$("&  port #",sIpaddress,portnumber)
      EXIT FUNCTION
    END IF
  END IF

  slOpen "junk1.db3","C"
  IF %DropTable THEN
    slexe  "drop table if exists tblJournalEntry"
  END IF
  slexe  "create table if not exists tblJournalEntry(AccountName,AccountType,Date,Credit INTEGER)"
  InsertData

  gName="Green line":gAddress = "Red line"
  LOCAL rs(),sFrom,sTo,sAccountType AS STRING
  LOCAL cols,rows,tabstops() AS LONG
  LOCAL sb AS ISTRINGBUILDERA
  sb = CLASS "STRINGBUILDERA"

  sFrom = "01-01-2019"
  sTo   = "28-05-2019"
  sAccountType = WRAP("Assets")

  sb.clear
  sb.add "SELECT AccountName, printf('%.2f',SUM(Credit)*.01) as CreditSUM"
  sb.add " FROM tblJournalEntry"
  sb.add " WHERE Date BETWEEN " + SQLiteDate(sFrom)  + " AND " + SQLiteDate(sTo)
  sb.add " and AccountType ="   + sAccountType       + " GROUP BY AccountName"

  slselary(sb.string,rs(),"Q9cE0")
  IF slGetErrorNumber THEN
    ? sb.string+$CR+$CR+slGetError,%MB_ICONERROR,EXE.NAME$
    EXIT FUNCTION
  END IF
  IF UBOUND(rs) < 1 THEN
  ? "Data not found",%MB_SYSTEMMODAL,EXE.NAME$
  EXIT FUNCTION
 END IF

 PrintReport rs(),sFrom,sTo

 IF LEN(sIpAddress) THEN slDisconnect
 ? USING$("Bytes #,",gbytes),,"Done"

END FUNCTION

FUNCTION PrintReport(rsAssets() AS STRING,sFrom AS STRING, sTo AS STRING) AS LONG

  LOCAL PageHeightMaximum AS SINGLE
  LOCAL iHandle,EndCode,TheBin,TheStyle,DefaultZoom,NoDialog,x,pagenum,fontsize,fontstyle,defaultcolor AS LONG
  LOCAL zOutputFile,zDocTitle,zDefaultFont AS ASCIIZ * 64
  LOCAL lineheight,LeftMargin AS SINGLE  'height deleted
  LOCAL AssetsHeight AS SINGLE
  LOCAL szAssets AS ASCIIZ * 300

  PageHeightMaximum = 10.1  'inches

  defaultzoom = %DDOC_ZOOM75
  zdefaultFont = "Courier New"
  TheStyle = %ddoc_Portrait
  fontsize = 8
  TheBin = %DDOC_Bin_Auto '7
  'topmargin= 1.6    'not in use
  lineheight = .1666 '1/6  'each line height
  LeftMargin = 0.5
  AssetsHeight = 1.0

  zdoctitle = "Mega-Net ERP - General Ledger Summary Report"
  DefaultZoom=%DDOC_ZOOMFIT + %DDOC_VIEWBUILD
  'DefaultZoom=%DDOC_ZOOM75  + %DDOC_VIEWBUILD
  'DefaultZoom=%DDOC_ZOOM100 + %DDOC_VIEWBUILD

  ihandle = dpStartDoc(0,_
                       zDocTitle, _
                       zoutputfile,_
                       %DDOC_INCH,_
                       %DDOC_PAPER_LETTER,_
                       %DDOC_SYSTEM_DEFAULT, _
                       TheBin,_
                       DefaultZoom&)
  IF ihandle < 1 THEN
    SELECT CASE ihandle
      CASE %DDOC_NOHANDLES  : ? "no file handles",,"dpStartDoc"
      CASE %DDOC_BADFILE    : ? "bad file name",,  "dpStartDoc"
      CASE %DDOC_FILEEXISTS
        ?  zoutputfile + " already exists so will delete document",,"dpStartDoc"
        KILL zoutputfile
        IF ERR THEN ? "Could not delete file",,EXE.NAME$
      CASE ELSE:      ?  "Error code" + STR$(iHandle),,"dpStartDoc"
    END SELECT
    EXIT FUNCTION
  END IF

  GOSUB PrintHeading
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Courier"
  LOCAL s AS STRING
  szAssets = SPACE$(80)
  FOR x = 1 TO UBOUND(rsAssets)
    LSET szAssets =      PARSE$(rsAssets(x),$TAB,1)
    MID$(szAssets,20) =  PARSE$(rsAssets(x),$TAB,2)
    MID$(szAssets,40) = "current height" + STR$(AssetsHeight)
    GOSUB PrintAssetsDetailLine
    'IF height => PageHeightMaximum THEN
    IF AssetsHeight => PageHeightMaximum THEN
      INCR pagenum
      dpNewPage iHandle,%ddoc_PAPER_LETTER, TheStyle, TheBin
      GOSUB PrintHeading
      dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Courier"
    END IF
  NEXT
  SLEEP 200
  endCode = %DDOC_END_VIEW + %DDOC_END_DELETE
  dpEndDoc iHandle, EndCode

EXIT FUNCTION

PrintHeading:
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 13, %vbGreen, "Arial"
   dpText ihandle, 4.25, 1.1, %DDOC_CENTER, "" & gName
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbRed, "Arial"
   dpText IHandle, 4.25, 1.3, %DDOC_CENTER, "" & gAddress
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 12, %vbBlack, "Arial"
   dpText IHandle, 4.25, 1.5, %DDOC_CENTER, "General Ledger Summary Report"
   dpText IHandle, 4.25, 1.8, %DDOC_CENTER, "Date Range: " & sFrom  & " To  " & sTo
   dpLine IHandle, .5, 10.3, 8, 10.3, 1, %vbBlack
   dpLine IHandle, .5, 10.8, 8, 10.8, 1, %vbBlack
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 11, %vbBlack, "Arial"
   AssetsHeight = 2.5
   dpText IHandle, .5, AssetsHeight, %DDOC_LEFT, "Assets: "
   AssetsHeight+= lineheight +.05 'looks a bit better
RETURN

PrintAssetsDetailLine:
  'dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,LeftMargin, AssetsHeight,%ddoc_left,szAssets
  AssetsHeight+= lineheight
RETURN
END FUNCTION

FUNCTION SQLiteDate(sDate AS STRING) AS STRING
 'From dd-MM-yyyy To yyyy-MM-dd
 FUNCTION = WRAP$(MID$(sDate,7,4) & "-" & MID$(sDate,4,2) & "-" & MID$(sDate,1,2),$SQ,$SQ)
END FUNCTION

SUB InsertData
  LOCAL x         AS LONG
  LOCAL acctnum   AS LONG
  LOCAL numaccts  AS LONG
  LOCAL numrecs   AS LONG
  LOCAL changes   AS LONG
  LOCAL counter   AS LONG
  LOCAL bytes     AS LONG
  gbytes = 0
  numaccts   = 99
  numrecs    = 100
  DIM s(1 TO numaccts * numrecs) AS STRING
  slexe "begin exclusive"
  changes = slgetchangecount("T")
  FOR acctnum = 1 TO numaccts  'account
    FOR x =1 TO numrecs        'insert this many into account
      INCR counter
      s(counter) = bindt(USING$("Acct #",acctnum)) + bindt("Assets") + bindt("2019-05-28") + bindi(STR$(acctnum))
      gbytes+=LEN(s(counter))
    NEXT
  NEXT
  slexebind "insert into tblJournalEntry values(?,?,?,?)",JOIN$(s(),""),"V4"
  slexe "end"

END SUB
8
Hope you get the power working!

I hard-coded parse$, but that should be modified to accept a variable number of columns.
PrintReport() could be modified to print many different reports.
It could be done in different ways including using a 2-dimensional array.
I like it.
This is what I see (click image)
fredrick10.png

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

MACRO WRAP(p1)   = WRAP$(p1,$SQ,$SQ)
MACRO UNWRAP(p1) = UNWRAP$(p1,$SQ,$SQ)
MACRO BindT(p1)  = slBuildBindDat((p1),"T")
%vbBlack=&H0&:%vbRed=&HFF&:%vbGreen=&HFF00&:%vbYellow=&HFFFF&:%vbBlue=&HFF0000
%vbMagenta=&HFF00FF:%vbCyan=&HFFFF00:%vbWhite=&HFFFFFF:%vbGrey=&HC0C0C0:%LIGHT_SHADE=&HE0E0E0
GLOBAL gName,gAddress AS STRING

FUNCTION PBMAIN () AS LONG 'fredrick10.bas
 gName="Green line":gAddress = "Red line"
 LOCAL rs(),sFrom,sTo,sAccountType AS STRING
 LOCAL cols,rows,tabstops() AS LONG
 LOCAL sb AS ISTRINGBUILDERA
 sb = CLASS "STRINGBUILDERA"

 CreateTestData

 sFrom = "01-01-2019"
 sTo   = "28-05-2019"
 sAccountType = WRAP("Assets")

 sb.clear
 sb.add "SELECT AccountName, printf('%.2f',SUM(Credit)*.01) as CreditSUM"
 sb.add " FROM tblJournalEntry"
 sb.add " WHERE Date BETWEEN " + SQLiteDate(sFrom)  + " AND " + SQLiteDate(sTo)
 sb.add " and AccountType ="   + sAccountType       + " GROUP BY AccountName"

 slselary(sb.string,rs(),"Q9cE0")
 IF slGetErrorNumber THEN
  ? sb.string+$CR+$CR+slGetError,%MB_ICONERROR,EXE.NAME$
  EXIT FUNCTION
 END IF
 IF UBOUND(rs) < 1 THEN
  ? "Data not found",%MB_SYSTEMMODAL,EXE.NAME$
  EXIT FUNCTION
 END IF

 PrintReport rs(),sFrom,sTo

END FUNCTION

FUNCTION PrintReport(rsAssets() AS STRING,sFrom AS STRING, sTo AS STRING) AS LONG

  LOCAL PageHeightMaximum AS SINGLE
  LOCAL iHandle,EndCode,TheBin,TheStyle,DefaultZoom,NoDialog,x,pagenum,fontsize,fontstyle,defaultcolor AS LONG
  LOCAL zOutputFile,zDocTitle,zDefaultFont AS ASCIIZ * 64
  LOCAL lineheight,LeftMargin AS SINGLE  'height deleted
  LOCAL AssetsHeight AS SINGLE
  LOCAL szAssets AS ASCIIZ * 300

  PageHeightMaximum = 10.1  'inches

  defaultzoom = %DDOC_ZOOM75
  zdefaultFont = "Courier New"
  TheStyle = %ddoc_Portrait
  fontsize = 8
  TheBin = %DDOC_Bin_Auto '7
  'topmargin= 1.6    'not in use
  lineheight = .1666 '1/6  'each line height
  LeftMargin = 0.5
  AssetsHeight = 1.0

  zdoctitle = "Mega-Net ERP - General Ledger Summary Report"
  DefaultZoom=%DDOC_ZOOMFIT + %DDOC_VIEWBUILD
  'DefaultZoom=%DDOC_ZOOM75  + %DDOC_VIEWBUILD
  'DefaultZoom=%DDOC_ZOOM100 + %DDOC_VIEWBUILD

  ihandle = dpStartDoc(0,_
                       zDocTitle, _
                       zoutputfile,_
                       %DDOC_INCH,_
                       %DDOC_PAPER_LETTER,_
                       %DDOC_SYSTEM_DEFAULT, _
                       TheBin,_
                       DefaultZoom&)
  IF ihandle < 1 THEN
    SELECT CASE ihandle
      CASE %DDOC_NOHANDLES  : ? "no file handles",,"dpStartDoc"
      CASE %DDOC_BADFILE    : ? "bad file name",,  "dpStartDoc"
      CASE %DDOC_FILEEXISTS
        ?  zoutputfile + " already exists so will delete document",,"dpStartDoc"
        KILL zoutputfile
        IF ERR THEN ? "Could not delete file",,EXE.NAME$
      CASE ELSE:      ?  "Error code" + STR$(iHandle),,"dpStartDoc"
    END SELECT
    EXIT FUNCTION
  END IF

  GOSUB PrintHeading
  dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Courier"
  LOCAL s AS STRING
  szAssets = SPACE$(80)
  FOR x = 1 TO UBOUND(rsAssets)
    LSET szAssets =      PARSE$(rsAssets(x),$TAB,1)
    MID$(szAssets,20) =  PARSE$(rsAssets(x),$TAB,2)
    MID$(szAssets,40) = "current height" + STR$(AssetsHeight)
    GOSUB PrintAssetsDetailLine
    'IF height => PageHeightMaximum THEN
    IF AssetsHeight => PageHeightMaximum THEN
      INCR pagenum
      dpNewPage iHandle,%ddoc_PAPER_LETTER, TheStyle, TheBin
      GOSUB PrintHeading
      dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Courier"
    END IF
  NEXT
  SLEEP 200
  endCode = %DDOC_END_VIEW + %DDOC_END_DELETE
  dpEndDoc iHandle, EndCode

EXIT FUNCTION

PrintHeading:
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 13, %vbGreen, "Arial"
   dpText ihandle, 4.25, 1.1, %DDOC_CENTER, "" & gName
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTBOLD, 12, %vbRed, "Arial"
   dpText IHandle, 4.25, 1.3, %DDOC_CENTER, "" & gAddress
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 12, %vbBlack, "Arial"
   dpText IHandle, 4.25, 1.5, %DDOC_CENTER, "General Ledger Summary Report"
   dpText IHandle, 4.25, 1.8, %DDOC_CENTER, "Date Range: " & sFrom  & " To  " & sTo
   dpLine IHandle, .5, 10.3, 8, 10.3, 1, %vbBlack
   dpLine IHandle, .5, 10.8, 8, 10.8, 1, %vbBlack
   dpFont ihandle, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 11, %vbBlack, "Arial"
   AssetsHeight = 2.5
   dpText IHandle, .5, AssetsHeight, %DDOC_LEFT, "Assets: "
   AssetsHeight+= lineheight +.05 'looks a bit better
RETURN

PrintAssetsDetailLine:
  'dpFont ihandle, %DDOC_FONTNORMAL, 11, %vbBlack, "Arial"
  dpText iHandle,LeftMargin, AssetsHeight,%ddoc_left,szAssets
  AssetsHeight+= lineheight
RETURN
END FUNCTION

FUNCTION SQLiteDate(sDate AS STRING) AS STRING
 'From dd-MM-yyyy To yyyy-MM-dd
 FUNCTION = WRAP$(MID$(sDate,7,4) & "-" & MID$(sDate,4,2) & "-" & MID$(sDate,1,2),$SQ,$SQ)
END FUNCTION

SUB CreateTestData
 LOCAL acctnum AS LONG
 LOCAL counter AS LONG
 LOCAL s AS STRING
 slOpen "junk1.db3","C"
 'slexe  "drop table if exists tblJournalEntry"
 slexe  "create table if not exists tblJournalEntry(AccountName,AccountType,Date,Credit INTEGER)"

 slexe "begin exclusive"
 FOR counter = 1 TO 1    'insert this many records into acctnum
  FOR acctnum = 1 TO 100 'number of accounts
   s =  bindt("account"+FORMAT$(acctnum)) + bindt("Assets") + bindt("2019-05-28") + bindi(STR$(acctnum))
   slexebind "insert into tblJournalEntry values(?,?,?,?)",s
  NEXT
 NEXT
 slexe "end"
END SUB
9
This might work with ddoc

#INCLUDE "sqlitening.inc"

MACRO WRAP(p1)   = WRAP$(p1,$SQ,$SQ)
MACRO UNWRAP(p1) = UNWRAP$(p1,$SQ,$SQ)
MACRO BindT(p1)  = slBuildBindDat((p1),"T")

FUNCTION PBMAIN () AS LONG

 LOCAL rs(),sFrom,sTo,sAccountType AS STRING
 LOCAL cols,rows,tabstops() AS LONG
 LOCAL sb AS ISTRINGBUILDERA
 sb = CLASS "STRINGBUILDERA"

 CreateTestData

 'AccountType Report
 sFrom = "01-01-2019"
 sTo   = "28-05-2019"
 sAccountType = WRAP("Assets")

 sb.clear
 sb.add "SELECT AccountName, printf('%.2f',SUM(Credit)*.01) as CreditSUM"
 sb.add " FROM tblJournalEntry"
 sb.add " WHERE Date BETWEEN " + SQLiteDate(sFrom)  + " AND " + SQLiteDate(sTo)
 sb.add " and AccountType ="   + sAccountType       + " GROUP BY AccountName"

 slselary(sb.string,rs(),"E0")
 IF slGetErrorNumber THEN
  ? sb.string+$CR+$CR+slGetError,%MB_ICONERROR,EXE.NAME$
  EXIT FUNCTION
 END IF

 cols = UBOUND(rs,1)
 rows = UBOUND(rs,2)
 IF rows < 1 THEN
  ? "Data not found",%MB_SYSTEMMODAL,EXE.NAME$
  EXIT FUNCTION
 END IF

 REDIM tabstops(1 TO 99) 'avoid assigning too many
 ARRAY ASSIGN tabstops() = 1,40
 PrintReport rs(),tabStops()

END FUNCTION

FUNCTION PrintReport(rs() AS STRING,tabStops() AS LONG) AS LONG

 LOCAL c,r,linecounter AS LONG
 LOCAL sLine AS STRING

 FOR r = 1 TO  UBOUND(rs,2)
  sLine = SPACE$(80)
  FOR c = 1 TO UBOUND(rs,1)
   MID$(sLine,tabstops(c)) = rs(c,r)
  NEXT
  INCR LineCounter
  ? sLine,,USING$("LineCounter #",LineCounter)
 NEXT
END FUNCTION

FUNCTION SQLiteDate(sDate AS STRING) AS STRING
 'From dd-MM-yyyy To yyyy-MM-dd
 FUNCTION = WRAP$(MID$(sDate,7,4) & "-" & MID$(sDate,4,2) & "-" & MID$(sDate,1,2),$SQ,$SQ)
END FUNCTION

SUB CreateTestData

 LOCAL x,y AS LONG
 LOCAL s AS STRING

 slOpen "junk1.db3","C"
 slexe  "drop table if exists tblJournalEntry"
 slexe  "create table tblJournalEntry(AccountName,AccountType,Date,Credit INTEGER)"
 FOR x = 1 TO 3
  FOR y = 1 TO 3
   s =  bindt("account"+FORMAT$(y)) + bindt("Assets") + bindt("2019-05-28") + bindi(STR$(y))
   slexebind "insert into tblJournalEntry values(?,?,?,?)",s
  NEXT
 NEXT
END SUB
10
Here is some code to eliminate rounding errors.
Other people and myself have seen it happen so we use INTEGERs for money.
This code also eliminates concatenation using CHR$, JOIN$ and REDIM PRESERVE
Did not modify sql in first results so the amounts are 100 times larger than the penny method in the second results.
Also uses printf('%.2f',SUM(Credit*.01)) as CreditSUM  in the second results instead of FORMAT$ or USING$.

There is nothing wrong with your code, just putting out alternatives.

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
 LOCAL s AS STRING,x,y AS LONG
 slOpen "junk.db3","C"
 slexe "drop table if exists tblJournalEntry"
 slexe "create table if not exists tblJournalEntry(AccountName,AccountType,Date,CreditINTEGER)"

 slexe "begin exclusive" 'lock database for dramatic speed increase
 FOR x = 1 TO 3          'need sample data to sum and group so inserting here using binding
  FOR y = 1 TO 3
   s=CHR$(    slBuildBindDat("klume"+FORMAT$(y),"T"),_ 'accountname
              slBuildBindDat("cash","T"),            _ 'accounttype
              slBuildBindDat("2019-05-28","T"),      _ 'date
              slBuildbindDat(STR$(y),"i")   )          'credit in pennies
   slexebind "insert into tblJournalEntry values(?,?,?,?)",s
  NEXT
 NEXT
 slexe "end"

 slsel "Select AccountName, AccountType, Date, SUM(Credit) as CreditSUM From tblJournalEntry GROUP BY AccountName"

 REDIM sArray(1 TO 99999) AS STRING
 RESET x
 DO WHILE slGetRow()
  INCR x
  sArray(x) = slFN("AccountName") + $TAB + FORMAT$(VAL(slFN("CreditSUM")), "#,.00")    'original
  'sArray(x)=CHR$(slFN("AccountName"),$TAB,USING$("#,.##",VAL(slFN("CreditSUM"))*.01)) 'using pennies
 LOOP
 REDIM PRESERVE sArray(1 TO x)
 ? CHR$( JOIN$(sArray(),$CR),$CR,$CR +_ 'slGetRow results followed by slSelAry results with printf
   viewall("select accountname as acct,printf('%.2f',SUM(Credit*.01)) as CreditSUM from tblJournalEntry group by accountname"))
END FUNCTION

FUNCTION viewall(sql AS STRING) AS STRING
 LOCAL sarray() AS STRING
 slSelAry sql,sarray(),"Q9c"
 FUNCTION = JOIN$(sarray(),$CR)
END FUNCTION
11
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
 slopen "junk.db3","C"
 slexe "create table if not exists t1(Acct,Amt)"
 slexe "insert into  t1 values ('Rec',3000)"
 slexe "insert into  t1 values ('Cash',2500)"
 slexe "insert into  t1 values ('Interest',4500)"
 slexe "insert into  t1 values ('Rec',2000)"
 slexe "insert into  t1 values ('Cash',1500)"
 viewall "select acct,sum(amt) from t1 group by acct"
END FUNCTION[

FUNCTION Viewall(sql AS STRING) AS STRING
 LOCAL sarray() AS STRING
 slselary sql,sarray(),"Q9"
 ? JOIN$(sarray(),$CR),,"Totals
END FUNCTION
12
Quote from: undefinedslExe "Begin"
  slExe slBuildInsertOrUpdate("tblJournalEntry", SQLiteDate(m_sDate) & $Nul & m_sReference _
  & $Nul & m_sNarration & $Nul & m_sAccount1 & $Nul & m_sDescription1 & $Nul & m_sDebit1 _
  & $Nul & m_sCredit1)
slExe "End"
     
slExe "Begin"
  slExe slBuildInsertOrUpdate("tblJournalEntry", SQLiteDate(m_sDate) & $Nul & m_sReference _
  & $Nul & m_sNarration & $Nul & m_sAccount2 & $Nul & m_sDescription2 & $Nul & m_sDebit2 _
  & $Nul & m_sCredit2)
slExe "End"
slExe "Begin Exclusive"
 slExe slBuildInsertOrUpdate("tblJournalEntry",(CHR$(SQLiteDate(Date),0,Ref,0,Narr,0,Acct1,0,Desc1,0,Debit1,0,Credit1)))
 slExe slBuildInsertOrUpdate("tblJournalEntry",(CHR$(SQLiteDate(Date),0,Ref,0,Narr,0,Acct2,0,Desc2,0,Debit2,0,Credit2)))
slexe "End"           
13
General Board / Money no leading zero using CASE
April 18, 2019, 09:39:27 pm
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN AS LONG

 LOCAL sql,sArray() AS STRING

 slopen "junk.db3","C"

 slexe "drop table if exists t1"
 slexe "create table if not exists t1(c1 integer)"

 slexe "insert into t1 values(-1000),(-10),(-9),(-1),(0),(1),(9),(10),(100),(1000)"

 sql = "select rowid,"  +_
       " case" +_
       " when c1 > 0 then       ltrim(printf('%.2f',c1*.01),'0')"  +_
       " when c1 < 0 then '-'|| ltrim(printf('%.2f',c1*.01),'-0')" +_
       " else '.00'" +_
       " end  AS TheValues"+_
       " from t1"

 slSelAry sql,sArray(),"Q9"
 ? JOIN$(sArray(),$CR),,"No leading zero"

END FUNCTION
 
14
#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
15
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