• Welcome, Guest. Please login.
 
July 14, 2020, 08:20:21 am

News:

Welcome to the SQLitening support forums!


Query

Started by Fredrick Ughimi, April 09, 2015, 10:33:52 pm

Previous topic - Next topic

Fredrick Ughimi

Hello,

Been scatching my head on this query. When I run it in my report I get no result. It comes out blank.


slSel "SELECT tblRegistration.DateTaken as RegistrationDateTaken," & _
            "   tblRegistration.BillNo as RegistrationBillNo," & _
            "   tblRegistration.Surname as RegistrationSurname," & _
            "   tblRegistration.Othernames as RegistrationOthernames," & _
            "   tblRegistration.LoyaltyID as RegistrationLoyaltyID," & _
            "   tblRegistration.RoomNo as RegistrationRoomNo," & _
            "   tblBarSales.Date as BarSalesDate," & _   
            "   tblBarSales.BillNo as BarSalesBillNo," & _ 
            "   tblBarSales.DocketNo as BarSalesDocketNo," & _
            "   tblBarSales.RoomNo as BarSalesRoomNo," & _
            "   tblBarSales.Description as BarSalesDescription," & _ 
            "   tblBarSales.SellingPrice as BarSalesSellingPrice," & _
            "   tblBarSales.Quantity as BarSalesQuantity," & _   
            "   tblMiniBarSales.Date as MiniBarSalesDate," & _
            "   tblMiniBarSales.BillNo as MiniBarSalesBillNo," & _
            "   tblMiniBarSales.DocketNo as MiniBarSalesDocketNo," & _
            "   tblMiniBarSales.RoomNo as MiniBarSalesRoomNo," & _
            "   tblMiniBarSales.Description as MiniBarSalesDescription," & _ 
            "   tblMiniBarSales.SellingPrice as MiniBarSalesSellingPrice," & _
            "   tblMiniBarSales.Quantity as MiniBarSalesQuantity" & _
            "   FROM" & _
            "   tblRegistration" & _
            "   INNER JOIN tblBarSales ON (RegistrationBillNo=BarSalesBillNo)" & _
            "   INNER JOIN tblMiniBarSales On (RegistrationBillNo=MiniBarSalesBillNo)" & _
            "   WHERE tblRegistration.DateTaken BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND tblRegistration.BillNo = '" + sBillNo + "'" & _
   


Maybe there is something I am doing wrong?

Thanks in advance for any pointers.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Bern Ertl

You are using INNER JOINs.  You are only going to get a result set when a record exists in both tblBarSales and tblMiniBarSales related to a given tblRegistration record.  If you change the INNER JOIN to a LEFT OUTER JOIN, you will get all tblRegistration records and any related tblBarSales or tblMiniBarSales.  You might get multiple rows for a given tblRegistration record if there are more than one related tblBarSales / tblMiniBarSales records.

See here for more info on JOINS:  http://sqlite.awardspace.info/syntax/sqlitepg06.htm

cj

April 12, 2015, 02:38:32 pm #2 Last Edit: April 21, 2015, 02:50:10 am by cj
If you could give us a little example with a single record in each table that should
match the search criteria then we can see why it is not working.
We cannot see any syntax errors or type mismatch, etc ...
create table1
create table2
create table3
insert into table1;insert into table2;insert into table 3;
sql statement


SEE IT IS RESOLVED ON THE POWERBASIC BBS.


COMPILE EXE
#DIM ALL
#INCLUDE "win32api.inc
#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
%vbOrange = &HFF3118
FUNCTION PBMAIN () AS LONG
  slOpen ("GuestsProDB.db3","C")
  ReportGuestsAccountStatus()
END FUNCTION
FUNCTION NigeriaDate(sDate AS STRING) AS STRING
     'From 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 PBDate(sDate AS STRING) AS STRING
     '02-05-2010
   FUNCTION = MID$(sDate,4,2) & "-" & MID$(sDate,1,2) & "-" & MID$(sDate,7,4)
END FUNCTION

FUNCTION ReportGuestsAccountStatus() AS LONG
      LOCAL dbHandle&, LineSpacing!,iHandle%,Row!,TopMargin!,PageSize!,PageNumber&
      LOCAL detailline AS ASCIIZ * 200
      LOCAL detailline2 AS ASCIIZ * 200
      LOCAL SNo AS LONG
      LOCAL lTotalRecords&
      LOCAL sFrom AS STRING
      LOCAL sTo AS STRING
      LOCAL dTotalVATAmount AS DOUBLE
      LOCAL dGrossTotal AS DOUBLE
      LOCAL dVAT AS DOUBLE
      LOCAL dGross AS DOUBLE
      LOCAL dTotalServiceAmount AS DOUBLE
      LOCAL dTotalBill AS DOUBLE
      LOCAL dTotalDeposit AS DOUBLE
      LOCAL sBillNo AS STRING
      LOCAL dBalance AS DOUBLE
      LOCAL dTotalAmount AS DOUBLE
      LOCAL dTotalDiscount AS DOUBLE
      LOCAL dAccommodation AS DOUBLE
      LOCAL sSurname AS STRING
      LOCAL sOthernames AS STRING
      LOCAL dNETTOTAL AS DOUBLE
      LOCAL dSERVICECHARGE AS DOUBLE
      LOCAL dTotalDockets AS DOUBLE
      LOCAL dAccommodationLessDiscount AS DOUBLE
      LOCAL dDiscount AS DOUBLE
      LOCAL dTotalAdditionalPayments AS DOUBLE
      LOCAL dTotalPayments AS DOUBLE
      LOCAL Discount AS DOUBLE
      LOCAL dBarSalesAmount AS DOUBLE
      LOCAL dMiniBarSalesAmount AS DOUBLE
      LOCAL lTotalRooms&
      LOCAL Str AS STRING
      LOCAL sLoyaltyID AS STRING
      LOCAL sRoomNo AS STRING
      LOCAL dTotalBarSalesDockets AS DOUBLE
      LOCAL dTotalMiniBarSalesDockets AS DOUBLE

      LOCAL hjpg AS LONG
       LineSpacing = .2
      TopMargin   = .5
      PAGESIZE    = 7
       'note: If ddoc.exe does not exist endless loop here
      ihandle% = dpStartDoc(0,"Mega-Net GuestsPro - Guests Account Status Report","",%DDOC_INCH, %DDOC_PAPER_A4, %DDOC_PORTRAIT, %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 L1W1 L2W1 L3.2W2 L4.1W3 L7W1"
       ROW = PAGESIZE    '7 above                        'force new heading

      sFrom = "01-03-2015"
      sTo = "11-04-2015"
      sBillNo = "BN2035320151"
          'ReportsInformation.Reports
          slSEL "SELECT COUNT(*) As RowCount FROM tblBarSales WHERE Date BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND BillNo = '" + sBillNo + "' "
         IF ISTRUE slGetRow() THEN
            lTotalRecords& = VAL(slFN("RowCount"))
         END IF
         slCloseSet
          slSEL "SELECT COUNT(*) As RowCount FROM tblRegistration WHERE DateTaken BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND BillNo = '" + sBillNo + "' "
         IF ISTRUE slGetRow() THEN
            lTotalRooms& = VAL(slFN("RowCount"))
         END IF
         slCloseSet

          slSel "SELECT SUM(SellingPrice * Quantity) As TotalBarSalesDockets, * From tblBarSales WHERE Date BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND BillNo = '" + sBillNo + "' "
          IF ISTRUE slGetRow() THEN
            dTotalBarSalesDockets = VAL(slFN("TotalBarSalesDockets"))
          END IF
          slCloseSet()
           slSel "SELECT SUM(SellingPrice * Quantity) As TotalMiniBarSalesDockets, * From tblMiniBarSales WHERE Date BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND BillNo = '" + sBillNo + "' "
          IF ISTRUE slGetRow() THEN
            dTotalMiniBarSalesDockets = VAL(slFN("TotalMiniBarSalesDockets"))
          END IF
          slCloseSet()
           dTotalDockets = dTotalBarSalesDockets + dTotalMiniBarSalesDockets
           slSel "SELECT SUM(Amount) As TotalAdditionalPayments, * From tblAdditionalPayments WHERE Date BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND BillNo = '" + sBillNo + "' "
          IF ISTRUE slGetRow() THEN
            dTotalAdditionalPayments = VAL(slFN("TotalAdditionalPayments"))
          END IF
          slCloseSet()
           slSEL "SELECT SUM(Deposit) as TotalDeposit, * FROM tblRegistration WHERE DateTaken BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND BillNo = '" + sBillNo + "' "
          IF ISTRUE slGetRow() THEN
             dTotalDeposit = VAL(slFN("TotalDeposit"))
          END IF
          slCloseSet()
           slSEL "SELECT SUM(Discount) as TotalDiscount, * FROM tblRegistration WHERE DateTaken BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND BillNo = '" + sBillNo + "' "
          IF ISTRUE slGetRow() THEN
             dTotalDiscount = VAL(slFN("TotalDiscount"))
          END IF
          slCloseSet()
           slSEL "SELECT SUM(NoOfNights * RoomRate) as Accommodation, * FROM tblRegistration WHERE DateTaken BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND BillNo = '" + sBillNo + "' "
          IF ISTRUE slGetRow() THEN
             dAccommodation = VAL(slFN("Accommodation"))
             dVAT = VAL(slFN("VAT"))
             dServiceCharge = VAL(slFN("ServiceCharge"))
          END IF
          slCloseSet
           dAccommodationLessDiscount =  dAccommodation -  dTotalDiscount
           dNETTOTAL = dAccommodationLessDiscount + dVAT + dSERVICECHARGE

            slSel "SELECT tblRegistration.DateTaken as RegistrationDateTaken," & _
            "   tblRegistration.BillNo as RegistrationBillNo," & _
            "   tblRegistration.Surname as RegistrationSurname," & _
            "   tblRegistration.Othernames as RegistrationOthernames," & _
            "   tblRegistration.LoyaltyID as RegistrationLoyaltyID," & _
            "   tblRegistration.RoomNo as RegistrationRoomNo," & _
            "   tblBarSales.Date as BarSalesDate," & _
            "   tblBarSales.BillNo as BarSalesBillNo," & _
            "   tblBarSales.DocketNo as BarSalesDocketNo," & _
            "   tblBarSales.RoomNo as BarSalesRoomNo," & _
            "   tblBarSales.Description as BarSalesDescription," & _
            "   tblBarSales.SellingPrice as BarSalesSellingPrice," & _
            "   tblBarSales.Quantity as BarSalesQuantity," & _
            "   tblBarSales.Name as BarSalesName" & _
            "   FROM" & _
            "   tblRegistration" & _
            "   INNER JOIN tblBarSales ON (RegistrationBillNo=BarSalesBillNo)" & _
            "   WHERE tblRegistration.DateTaken BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND tblRegistration.BillNo = '" + sBillNo + "'"
             DO WHILE slGetRow()
              GOSUB CheckPageSize
               dBarSalesAmount = VAL(slFN("BarSalesSellingPrice")) * VAL(slFN("BarSalesQuantity"))
              INCR SNo
               DetailLine$ =  _
                STR$(SNo) + $TAB + _
                NigeriaDate(slFN("BarSalesDate")) + $TAB + _
                slFN("BarSalesDocketNo") + $TAB + _
                slFN("BarSalesRoomNo") + $TAB + _
                slFN("BarSalesDescription") + $TAB + _
                FORMAT$(dBarSalesAmount)
                dpTabText iHandle%, ROW + 3.2, DetailLine
                ROW = ROW + LineSpacing                    'increment line counter
           LOOP

         '? "MiniBarSales starts with" + str$(sNo)
          slSel "SELECT DISTINCT tblRegistration.DateTaken as RegistrationDateTaken," & _
            "   tblRegistration.BillNo as RegistrationBillNo," & _
            "   tblRegistration.Surname as RegistrationSurname," & _
            "   tblRegistration.Othernames as RegistrationOthernames," & _
            "   tblRegistration.LoyaltyID as RegistrationLoyaltyID," & _
            "   tblRegistration.RoomNo as RegistrationRoomNo," & _
            "   tblMiniBarSales.Date as MiniBarSalesDate," & _
            "   tblMiniBarSales.BillNo as MiniBarSalesBillNo," & _
            "   tblMiniBarSales.DocketNo as MiniBarSalesDocketNo," & _
            "   tblMiniBarSales.RoomNo as MiniBarSalesRoomNo," & _
            "   tblMiniBarSales.Description as MiniBarSalesDescription," & _
            "   tblMiniBarSales.SellingPrice as MiniBarSalesSellingPrice," & _
            "   tblMiniBarSales.Quantity as MiniBarSalesQuantity" & _
            "   FROM" & _
            "   tblRegistration" & _
            "   INNER JOIN tblMiniBarSales On (RegistrationBillNo=MiniBarSalesBillNo)" & _
            "   WHERE tblRegistration.DateTaken BETWEEN '" + SQLiteDate(sFrom) + "' AND '" + SQLiteDate(sTo) + "' AND tblRegistration.BillNo = '" + sBillNo + "'"

          DO WHILE slGetRow()
             GOSUB CheckPageSize
             'MiniBarSales
            dMiniBarSalesAmount = VAL(slFN("MiniBarSalesSellingPrice")) * VAL(slFN("MiniBarSalesQuantity"))
            INCR SNo
             DetailLine$ =  _
              STR$(SNo) + $TAB + _
              NigeriaDate(slFN("MiniBarSalesDate")) + $TAB + _
              slFN("MiniBarSalesDocketNo") + $TAB + _
              slFN("MiniBarSalesRoomNo") + $TAB + _
              slFN("MiniBarSalesDescription") + $TAB + _
              FORMAT$(dMiniBarSalesAmount)
              dpTabText iHandle%, ROW + 3.2, DetailLine
              ROW = ROW + LineSpacing                     'increment line counter
          LOOP
'
          dpEndDoc iHandle%, %DDOC_END_VIEW + %DDOC_END_PRINT
        EndProgram:
EXIT FUNCTION
CheckPageSize:
  IF ROW => PAGESIZE THEN
                IF PageNumber THEN      'If first page don't need a new page
                  dpNewPage iHandle%,  %ddoc_PAPER_A4, %DDOC_PORTRAIT, %DDOC_BIN_AUTO
               END IF
               INCR PageNumber
                  hjpg = dpAddGraphic(ihandle%, EXE.PATH$ & "Logo.jpg")
                 IF hjpg THEN
                    dpDrawGraphic ihandle%, hjpg,3.8,.2,2,1
                 END IF
                dpText iHandle%, .5,TopMargin,  %DDOC_Left,"Page: "+ STR$(PageNumber)+""
               dpText iHandle%, 6,TopMargin,  %DDOC_Left,"Date: "+ PBDate(DATE$)+""
               dpText iHandle%, 6,TopMargin +.2,  %DDOC_Left,"Time: "+ TIME$+""
               ROW =TopMargin + LineSpacing * 4
               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, %vbBlack, "Arial"
               dpText IHandle%, 4.25, 1.4, %DDOC_CENTER, "" & "gAddress"
               dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 12, %vbBlack, "Arial"
               dpRect IHandle%, .4, 1.7, 8.0, 2.6, 3, %LIGHT_SHADE, 0
               dpText IHandle%, 4.25, 1.7, %DDOC_CENTER, "Guests Account Status Report"
               dpFont ihandle%, %DDOC_FONTNORMAL, 12, %vbBlack, "Arial"
               dpText IHandle%, 0.5, 2.0, %DDOC_LEFT, "Date: " &  sFrom  & " To  " & sTo
               dpText IHandle%, 3.7, 2.0, %DDOC_LEFT, "No Of Rooms: " + FORMAT$(lTotalRooms)
               dpText IHandle%, 6.2, 2.0, %DDOC_LEFT, "LoyaltyID: " & slFN("RegistrationLoyaltyID")
               dpText IHandle%, 6.2, 2.3, %DDOC_LEFT, "Room No: " & slFN("RegistrationRoomNo")
               dpText IHandle%, 0.5, 2.3, %DDOC_LEFT, "Check-In Bill No: " + slFN("RegistrationBillNo")
               dpText IHandle%, 3.7, 2.3, %DDOC_LEFT, "Name: " & slFN("RegistrationSurname") & " " & slFN("RegistrationOthernames")
               dpText IHandle%, .5, 2.9, %DDOC_LEFT, "Deposit Paid: "
               dpText IHandle%, 3.0, 2.9, %DDOC_RIGHT,  FORMAT$(dTotalDeposit, "#,.00")
               dpText IHandle%, .5, 3.2, %DDOC_LEFT, "Additional Payments: "
               dpText IHandle%, 3.0, 3.2, %DDOC_RIGHT,  FORMAT$(dTotalAdditionalPayments, "#,.00")
               dTotalPayments = dTotalDeposit + dTotalAdditionalPayments
               dpText IHandle%, .5, 3.5, %DDOC_LEFT, "Total Payments: "
               dpText IHandle%, 3.0, 3.5, %DDOC_RIGHT,  FORMAT$(dTotalPayments, "#,.00")
               dpLine IHandle%, .5, 3.7, 3, 3.7, 0, %vbBlue
               dpText IHandle%, 4.0, 2.9, %DDOC_LEFT, "Accommodation: "
               dpText IHandle%, 7.0, 2.9, %DDOC_RIGHT,  FORMAT$(dAccommodation, "#,.00")
               dpText IHandle%, 4.0, 3.1, %DDOC_LEFT, "Discount: "
               dpText IHandle%, 7.0, 3.1, %DDOC_RIGHT,  FORMAT$(dTotalDiscount, "#,.00")
               dpText IHandle%, 4.0, 3.3, %DDOC_LEFT, "VAT: "
               dpText IHandle%, 7.0, 3.3, %DDOC_RIGHT,  FORMAT$(dVAT, "#,.00")
               dpText IHandle%, 4.0, 3.5, %DDOC_LEFT, "Service Charge: "
               dpText IHandle%, 7.0, 3.5, %DDOC_RIGHT,  FORMAT$(dServiceCharge, "#,.00")
               dpText IHandle%, 4.0, 3.7, %DDOC_LEFT, "Accommodation Net:"
               dpText IHandle%, 7.0, 3.7, %DDOC_RIGHT,  FORMAT$(dNETTOTAL, "#,.00")
               dpText IHandle%, 4.0, 3.9, %DDOC_LEFT, "Dockets(Total):"
               dpText IHandle%, 7.0, 3.9, %DDOC_RIGHT,  FORMAT$(dTotalDockets, "#,.00")
               dTotalAmount = dNETTOTAL +  dTotalDockets
               dBalance =  dTotalAmount - dTotalPayments
               dpText IHandle%, 4.0, 4.1, %DDOC_LEFT, "Total Amount Payable:"
               dpText IHandle%, 7.0, 4.1, %DDOC_RIGHT,  FORMAT$(dTotalAmount, "#,.00")
               dpText IHandle%, 4.0, 4.3, %DDOC_LEFT, "Balance:"
               dpText IHandle%, 7.0, 4.3, %DDOC_RIGHT,  FORMAT$(dBalance, "#,.00")
               dpLine IHandle%, 4.0, 4.5, 7, 4.5, 0, %vbBlue
               dpText IHandle%, .5, 10.7, %DDOC_LEFT, "___________________________"
               dpText IHandle%, 1.1, 10.9, %DDOC_LEFT, "Guest's Sign: "
               dpText IHandle%, 5, 10.7, %DDOC_LEFT, "___________________________"
               dpText IHandle%, 5.6, 10.9, %DDOC_LEFT, "Manager's Sign: "
               dpFont ihandle%, %DDOC_FONTNORMAL + %DDOC_FONTUNDERLINE + %DDOC_FONTBOLD, 9, %vbBlack, "Arial"
               dpTabText iHandle%, ROW + 3.4, "S/No" + $TAB + "Date" + $TAB + "DocketNo" + $TAB + "RoomNo" _
               + $TAB + "Description" + $TAB + "Amount"
               dpFont ihandle%, %DDOC_FONTNORMAL, 8, %vbBlack, "San Serif"
               ROW =  ROW + LineSpacing * 2.1
             END IF
RETURN
END FUNCTION


Fredrick Ughimi

Hello,

Sorry I have not responded to this thread because when I tried to access the SQLitening from my computer I get blocked. I am currently using my android phone to type this message. This blocking happens every now and then.

How can this be fixed?
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

Hello,

Please check:

www.powerbasic.com/support/pbforums/showthread.php?p=476310#post476310

where I posted a source code you could compile and an attached database.

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