Re: Using view returns different results for same query

 Please disregard this thread.  I found my view was not getting dropped then recreated in script as I had assumed.  Now view is working as expected.Cheers,Andrew
    On Thursday, August 29, 2019, 12:33:26 PM EDT, Andrew Richardson via Hsqldb-user <[email protected]> wrote:  
 
  Sorry for the spam.  I upgraded to HSQLDB v2.5.0, same results.
    On Thursday, August 29, 2019, 11:57:42 AM EDT, Andrew Richardson <[email protected]> wrote:  
 
 Hello AllI have a campground application using HSQL Database Engine v. 2.4.1 in client/server configuration.  In SQLToool, I crafted the SQL query below and it return 263 rows as expected.  I then created a view using syntax CREATE VIEW PUBLIC."Transactions" AS  <same query> , which was successful.  However, when I query the view I get 2900+ rows.  Seems like the joins are not executed correctly when view is used.Can someone shred light on why this might be?  Possible bug?Thanks,Andrew Richardson  

SELECT 'Waterfront' "ResType", 
 "W".ID "ID", "P"."TransactionDate" "Date", "C"."FullName" "Name", "P"."PaymentType" "PaymentType", "P"."CashierInitials" "Initials", "P"."Amount" "Amount", "P"."CCInvNumber" "Inv#", "P"."CCApprovalCode" "Approval" FROM PUBLIC."WaterfrontReservations" "W",     PUBLIC."Payments" "P",   PUBLIC."Customers" "C" WHERE "W"."CustomerID" = "C"."ID"  AND ("W"."ID" = "P"."ResID" AND 'W' = "P"."ResType")UNION SELECT 'Camping',   "M".ID, "P"."TransactionDate", "C"."FullName", "P"."PaymentType", "P"."CashierInitials", "P"."Amount", "P"."CCInvNumber", "P"."CCApprovalCode" FROM PUBLIC."CampingReservations" "M",     PUBLIC."Payments" "P",   PUBLIC."Customers" "C" WHERE "M"."CustomerID" = "C"."ID"  AND ("M"."ID" = "P"."ResID" AND 'C' = "P"."ResType")ORDER BY 3,4;
  _______________________________________________
Hsqldb-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

_______________________________________________
Hsqldb-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user