[Solved] Query error invalid HAVING expression in statement

Discuss the database features

[Solved] Query error invalid HAVING expression in statement

Postby MPEcho » 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
Code: Select all   Expand viewCollapse view
HAVING ( ( "Inventory"."clio_id" IN ( "FILTER"."clio_id" ) ) )

no longer works while the same thing is accolplished with
Code: Select all   Expand viewCollapse view
WHERE "Inventory"."clio_id" = "FILTER"."clio_id"


I have several of these in queries and will fix them all.

Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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
Last edited by floris v on Mon Feb 20, 2017 11:34 pm, edited 1 time in total.
Reason: Edited Solved tag and added green Solved icon, floris v, moderator
Libre Office 5.1 Ubuntu 16.04
MPEcho
 
Posts: 92
Joined: Wed Sep 07, 2016 11:30 pm

Re: Query error invalid HAVING expression in statement

Postby RusselB » Mon Feb 20, 2017 11:03 pm

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)
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5669
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

Postby MPEcho » Tue Feb 21, 2017 3:22 am

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.
Libre Office 5.1 Ubuntu 16.04
MPEcho
 
Posts: 92
Joined: Wed Sep 07, 2016 11:30 pm


Return to Base

Who is online

Users browsing this forum: No registered users and 3 guests