[Solved] Error Message with Query execution - inventory data

[syp1977]

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

[Villeroy]

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

[chrisb]

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"

[syp1977]

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.

[syp1977]

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

[Villeroy]

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

[syp1977]

@ villeroy
Thanks a lot

[syp1977]

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

[chrisb]

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