Apache OpenOffice Community Forum

Posted: **Wed Nov 24, 2021 8:51 pm**

I have made Two Tables for inventory managment:
Table 1# having fields Material_ID, Material_Name, Received_Qty
Table 2# having fields Material_ID, Qty_Utilized

I wanted to have consolidated utilization of materials including the materials unutilized, hence made following query, :
Query1 # SELECT "Material_ID", SUM( "Quantity" ) AS "Quantity" FROM ( SELECT "Material_ID", SUM( "Qty_Utilized" ) AS "Quantity" FROM "Table2" GROUP BY "Material_ID" UNION SELECT "Material_ID", 0 AS "Quantity" FROM "Table1" GROUP BY "Material_ID" ) GROUP BY "Material_ID" ORDER BY "Material_ID"

Now I want to prepare report having all information of materials including utilization details as well, hence I made following query:
Query2 #SELECT "Table1"."Material_ID", "Table1"."Material_Name", "Table1"."Received_Qty", "Query1"."Quantity" FROM "Table1", "Query1" WHERE "Table1"."Material_ID" = "Query1"."Material_ID"

But it gives following error:
The data content could not be loaded.
Cannot be in ORDER BY clause in statement [SELECT "Table1"."Material_ID", "Table1"."Material_Name", "Table1"."Received_Qty", "Query1"."Quantity" FROM "Table1", ( SELECT "Inward No.", SUM( "Quantity" ) "Quantity" FROM ( SELECT "Material_ID", SUM( "Quantity" ) AS "Quantity" FROM ( SELECT "Material_ID", SUM( "Qty_Utilized" ) AS "Quantity" FROM "Table2" GROUP BY "Material_ID" UNION SELECT "Material_ID", 0 AS "Quantity" FROM "Table1" GROUP BY "Material_ID" ) GROUP BY "Material_ID" ORDER BY "Material_ID") "Query1" WHERE "Table1"."Material_ID" = "Query1"."Material_ID"]

Can anyone help?
Thanks in advance

Posted: **Wed Nov 24, 2021 9:04 pm**

Have a look at this inventory: download/file.php?id=28761

Posted: **Wed Nov 24, 2021 10:39 pm**

although your database is fairly simple it's clearly not normalised please take the time to study the thread posted by Villeroy.
not sure why you use UNION? there may be a good reason but i suspect that something like this may be more appropriate.

Edit: oops named "Received_Qty" as "Qty_Received" now fixed.

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"

Posted: **Thu Nov 25, 2021 5:00 pm**

Dear Chirb,

Thanks for the post. That was really helpful. Yeah my data is not normalized because one material comes from different supplier that too with different lots and hence given unique material ID.

Thanks for the help. That really helped.

Posted: **Thu Nov 25, 2021 5:03 pm**

@ Villeroy,
Thanks for the help. That database was great help to me for better understanding.
Regards,
Shailesh

Posted: **Thu Nov 25, 2021 6:27 pm**

This is how you get automatic time stamps:
menu:Tools>SQL...

Code: Select all

ALTER TABLE "Table Name" ALTER COLUMN "Column Name" SET DEFAULT CURRENT_TIMESTAMP

[Execute]
and then menu:View>Refresh Tables.
The embedded HSQL database is documented here: http://www.hsqldb.org/doc/1.8/guide/ch09.html

Posted: **Wed Dec 01, 2021 1:03 pm**

@ villeroy
Thanks a lot

Posted: **Wed Dec 29, 2021 1:29 pm**

Hello Chris,

Can you please suggest what query I should use if I want to have details of specific material.
eg. If I have inventory of 5 different lot of oranges, two different lot of apples etc. Can we have query to see detail of only oranges incoming and outgoing and balance in a similar way?

Thank you in advance.

Best Regards,
Shailesh

Posted: **Wed Dec 29, 2021 6:49 pm**

i guess we did not answer the original question so the answer to this topic is:
when using embedded HSQLDB it is illegal to use the ORDER BY clause in a sub-query, it should only be used to sort the final result set.
when using HSQLDB 2.x, use of the ORDER BY clause in a sub-query is permitted.

now you are asking a completely different question totally unrelated to the title of this thread.
if i post a reply here then it is buried i.e. can not be found by other forum users searching for solutions to similar issues. this undermines the forum objectives.

please start a new topic, give it a meaningful title & anticipate a reply.

Edit: Topic locked to ensure this unrelated question goes into a new topic. I was going to do that earlier this morning, but had other matters to attend to. Thanks, chrisb for refusing to answer an unrelated question in a solved topic.
-- MrProgrammer, forum moderator

Apache OpenOffice Community Forum

[Solved] Error Message with Query execution - inventory data

[Solved] Error Message with Query execution - inventory data

Re: Error Message with Query execution

Re: Error Message with Query execution

Re: Error Message with Query execution

Re: [Solved] Error Message with Query execution - inventory

Re: [Solved] Error Message with Query execution - inventory

Re: [Solved] Error Message with Query execution - inventory

Re: [Solved] Error Message with Query execution - inventory

Re: [Solved] Error Message with Query execution - inventory