Apache OpenOffice Community Forum

Posted: **Mon Feb 21, 2022 10:27 am**

My question is in continuation with my previous post.
Let me clarify that I have upgraded my hsqldb to 2.5.1.
I have modified the query to filter the remaining stock which is less than or equal to zero for understanding entry errors. Following is the code:

Code: Select all

select
   a."Material_ID", a."Material_Name", a."Received_Qty", coalesce(b."Qty_Utilized", 0) "Qty_Utilized",
   a."Received_Qty" - coalesce(b."Qty_Utilized", 0) "RemainingStock"
from
(
   select  "Material_ID", "Material_Name", sum("Received_Qty") as "Received_Qty"
   from "Table1"
   group by "Material_ID", "Material_Name"
) a
left join
(
   select  "Material_ID", sum("Qty_Utilized") as "Qty_Utilized"
   from "Table2"
   group by "Material_ID"
) b
   on a."Material_ID" = b."Material_ID" where "RemainingStock" <= 0, Order by "Material_ID"

I am getting following error:

The data content could not be loaded. user lacks privilege or object not found: RemainingStock in statement

Thanking you in advance.

Posted: **Mon Feb 21, 2022 2:41 pm**

Wrap all the names in double-quotes including alias names such as "a".

Posted: **Mon Feb 21, 2022 4:27 pm**

this is one of the conflicts between hsqldb 1.x and hsqldb 2.x.

"RemainingStock" is the alias for a calculated value generated for final output i.e. not within a sub-query.
it cannot be referenced by the WHERE clause.

use:

Code: Select all

where a."Received_Qty" - coalesce(b."Qty_Utilized", 0) <= 0

Edit: just noticed the comma before the ORDER BY clause, DELETE IT.

Posted: **Tue Feb 22, 2022 12:12 pm**

Thanks a lot.. It worked

Apache OpenOffice Community Forum

[Solved] Error in query using where clause of split database

[Solved] Error in query using where clause of split database

Re: Error in query for using where clause in split database

Re: Error in query for using where clause in split database

Re: Error in query for using where clause in split database