Solved Update:
The query that was complaining had no "Having" statement which is what confused me. But it was dependent on a query that did. I think (but don't know) that the problem was created when updating from hsqldb 1.8 to 2.3. it appears that the expression
Code: Select all
HAVING ( ( "Inventory"."clio_id" IN ( "FILTER"."clio_id" ) ) )
Code: Select all
WHERE "Inventory"."clio_id" = "FILTER"."clio_id"
Code: Select all
SQL Status: 42573
Error code: -5573
invalid HAVING expression in statement [SELECT "Base-info"."Dec_Name", "Base-info"."Dec_addr1", "Base-info"."Dec_addr2", "Base-info"."Dec_CityStZip", "Base-info"."Dec_SSN", "Base-info"."Tax_ID", "Base-info"."Dec_Spouse", "Base-info"."Place_death", "Base-info"."d-date" "Date_death", "Base-info"."Domicile", "Base-info"."w-date" "will_date", COALESCE ( ( "heirs"."First" || ' ' ), '' ) || COALESCE ( ( "heirs"."Middle" || ' ' ), '' ) || "heirs"."Last" || COALESCE ( ( ' ' || "heirs"."Suffix" ), '' ) "PR_name", COALESCE ( ( "heirs"."First" || ' ' ), '' ) "PR_FName", "heirs"."Last" "PR_LName", "heirs"."Addr1" "PR_Addr1", "heirs"."Addr2" "PR_Addr2", "heirs"."CityStZip" "PR CityStZip", "heirs"."phone" "PR_Phone", "heirs"."HD Relationship" "PR_Relation", "Base-info"."Est_value", "Base-info"."Dec_BirthD", "Base-info"."case_no", "Base-info"."case_county", "Courts"."Addr1" "Ct_Addr1", "Courts"."Addr2" "Ct_Addr2", "Courts"."City" "Ct_City", "Courts"."State" "Ct_State", "Courts"."Zip" "Ct_Zip", "Base-info"."Dec_FirstName", COALESCE ( ( "Base-info"."Dec_MidName" || ' ' ), '' ) "Dec_MidName", "Base-info"."Date-Appoint", "Base-info"."Bond", ( CASE WHEN "Base-info"."Restricted" = 1 THEN 1 ELSE NULL END ) "Restricted", "Fee_Authority"."Stat-auth", "Fee_Authority"."amount", "Fee_Authority"."val-low", "Fee_Authority"."val-high", COALESCE ( "Query_Inv_Total"."Total_Inv", 0 ) "Total_Inv" FROM ( SELECT "Inventory"."clio_id" "clio_id", SUM( "Inventory"."value" ) "Total_Inv" FROM "FILTER", "Inventory" GROUP BY "Inventory"."clio_id" HAVING ( ( "Inventory"."clio_id" IN ( "FILTER"."clio_id" ) ) ) ) AS "Query_Inv_Total", "Base-info", "FILTER", "heirs", "Courts" JOIN "Fee_Authority" ON "Base-info"."Est_value" > "Fee_Authority"."val-low" AND "Base-info"."Est_value" < "Fee_Authority"."val-high" AND "Fee_Authority"."type" = 'pet' WHERE "Base-info"."clio_id" IN ( "FILTER"."clio_id" ) AND "heirs"."pers-id" = "Base-info"."pers-id" AND "Courts"."County" = "Base-info"."case_county"]
Code: Select all
SELECT "Base-info"."Dec_Name", "Base-info"."Dec_addr1", "Base-info"."Dec_addr2", "Base-info"."Dec_CityStZip", "Base-info"."Dec_SSN", "Base-info"."Tax_ID", "Base-info"."Dec_Spouse", "Base-info"."Place_death", "Base-info"."d-date" "Date_death", "Base-info"."Domicile", "Base-info"."w-date" "will_date", COALESCE ( ( "heirs"."First" || ' ' ), '' ) || COALESCE ( ( "heirs"."Middle" || ' ' ), '' ) || "heirs"."Last" || COALESCE ( ( ' ' || "heirs"."Suffix" ), '' ) "PR_name", COALESCE ( ( "heirs"."First" || ' ' ), '' ) "PR_FName", "heirs"."Last" "PR_LName", "heirs"."Addr1" "PR_Addr1", "heirs"."Addr2" "PR_Addr2", "heirs"."CityStZip" "PR CityStZip", "heirs"."phone" "PR_Phone", "heirs"."HD Relationship" "PR_Relation", "Base-info"."Est_value", "Base-info"."Dec_BirthD", "Base-info"."case_no", "Base-info"."case_county", "Courts"."Addr1" "Ct_Addr1", "Courts"."Addr2" "Ct_Addr2", "Courts"."City" "Ct_City", "Courts"."State" "Ct_State", "Courts"."Zip" "Ct_Zip", "Base-info"."Dec_FirstName", COALESCE ( ( "Base-info"."Dec_MidName" || ' ' ), '' ) "Dec_MidName", "Base-info"."Date-Appoint", "Base-info"."Bond", ( CASE WHEN "Base-info"."Restricted" = 1 THEN 1 ELSE NULL END ) "Restricted", "Fee_Authority"."Stat-auth", "Fee_Authority"."amount", "Fee_Authority"."val-low", "Fee_Authority"."val-high", COALESCE ( "Query_Inv_Total"."Total_Inv", 0 ) "Total_Inv" FROM "Query_Inv_Total", "Base-info", "FILTER", "heirs", "Courts" JOIN "Fee_Authority" ON "Base-info"."Est_value" > "Fee_Authority"."val-low" AND "Base-info"."Est_value" < "Fee_Authority"."val-high" AND "Fee_Authority"."type" = 'pet' WHERE "Base-info"."clio_id" IN ( "FILTER"."clio_id" ) AND "heirs"."pers-id" = "Base-info"."pers-id" AND "Courts"."County" = "Base-info"."case_county"
Appreciate any thoughts