Page 1 of 1

[Solved] Query error invalid HAVING expression in statement

Posted: Mon Feb 20, 2017 9:31 pm
by MPEcho
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

Code: Select all

HAVING ( ( "Inventory"."clio_id" IN ( "FILTER"."clio_id" ) ) )
no longer works while the same thing is accolplished with

Code: Select all

WHERE "Inventory"."clio_id" = "FILTER"."clio_id"
I have several of these in queries and will fix them all.

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"]
The actual query does not have a "HAVING" statement in it. The query reads:

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"
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

Re: Query error invalid HAVING expression in statement

Posted: Mon Feb 20, 2017 11:03 pm
by RusselB
While I'm not a Base expert by any means, I did notice that your queries seem to be lengthy, so my first thought might be that your query was too long and you were getting (for some unknown reason) an incorrect error message.
However. after doing a comparison between the two codes you posted (after removing the "invalid HAVING expression in statement [" portion of the first code, I noticed that the remainder of the code with the error report, comes back as 1935 characters, as the post of your actual query comes back as 1728 characters. A discrepancy of 207 characters.
Thus my first suggestion would be to do a careful, character by character, comparison between the two.
A quick look shows a discrepancy at
0 ) "Total_Inv" FROM ( SELECT "Inventory"."clio_id" "clio_id"
from the first code, which corresponds with
0 ) "Total_Inv" FROM "Query_Inv_Total"
in the second.
Thus the query you posted as being the actual query is not the same as the query being reported as having the error.
While this doesn't explain the error message you are receiving, (I did check and the term "having" does not exist in either query), it might lead to the reason for an error report (even if the report is inaccurate)

Re: [Solved] Query error invalid HAVING expression in statem

Posted: Tue Feb 21, 2017 3:22 am
by MPEcho
Thanks RusselB,
It looks to me like the error message expands the SQL relied on. So if I do a "Query2" that relies on "Query1" has SELECT "Table1"."field1", "Query1"."field2", etc. Then the error message will expand the SQL of "Query1" In my case, Query1 = "Query_Inv_Total" Once I fixed that query, then the query that relied on it was fixed too.