[Solved] Query error invalid HAVING expression in statement
Posted: Mon Feb 20, 2017 9:31 pm
I have a query that is broken. When run, I get an error as follows:
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
no longer works while the same thing is accolplished with
I have several of these in queries and will fix them all.
The actual query does not have a "HAVING" statement in it. The query reads:
This query used to work. I was attempting to fix date problems, in the table "base-info" were two fields "Date_death" and "will_date" Both were text fields, not date fields. I added two additional fields to the table d-date and w-date. The only intended changes were to be to use those fields and have the query report as the original field names. It seems I must have done something else, but cant figure it out.
Appreciate any thoughts
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