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

[syp1977]

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.

[Villeroy]

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

[chrisb]

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.

[syp1977]

Thanks a lot.. It worked