[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
rasat
General User

Joined: 08 Aug 2006
Posts: 12

 Posted: Sun Aug 13, 2006 6:27 am    Post subject: Queries for total in, out and balance. I have tried few queries suggested here, but cannot catch the idea how to do. In the below screenshot, there are three tables: (1) Stock, (2) In, and (3) Out. Stock sums the total quantity received in In table (TotIn), Out table (TotOut), and shows the balance (TotQty) of each item. Screenshot (click to enlarge):
audionuma
OOo Enthusiast

Joined: 23 Feb 2006
Posts: 126
Location: France

Posted: Sun Aug 13, 2006 7:48 am    Post subject:

hello,
something like :
 Code: SELECT "ParNo", SUM("InQty") AS "TotIn", SUM("OutQty") AS "TotOut", ("InQty" - "OutQty") AS "TotQty" FROM "In", "Out" GROUP BY "ParNo"

might work.
(you have to try it in direct sql mode).
rasat
General User

Joined: 08 Aug 2006
Posts: 12

Posted: Sun Aug 13, 2006 9:04 am    Post subject:

I managed to run with the below changes....
 Code: SELECT "Stock"."PartNo", SUM("In"."InQty") AS "TotIn", SUM("Out"."OutQty") AS "TotOut", (SUM("In"."InQty") - SUM("Out"."OutQty")) AS "TotQty" FROM "Stock", "In", "Out" GROUP BY  "Stock"."PartNo"

but the quantities become same for each item.
PartNo | TotIn |TotOut | TotQty |
-----------------------------------------
P1 |360 |185 |175 |
P2 |360 |185 |175 |
P3 |360 |185 |175 |

EDIT
I added the relations, got the quoantities but calculates wrongly. Example, item P2 the total InQty is 16+15=31, not 124.
Screenshot:
http://user-contributions.org/projects/amhcc/screenshots/query-result2.jpg
DrewJensen
Super User

Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

Posted: Sun Aug 13, 2006 12:04 pm    Post subject:

Try this

 Code: SELECT    "Stock"."PartNo",    "IN"."TotalIn",    "OUT"."TotalOut",    "IN"."TotalIn" - "OUT"."TotalOut" AS "Current Stock" FROM    "Stock",    (   SELECT          `PartNo`, SUM( `InQty` )  as `TotalIn`          FROM `In` AS `In` GROUP BY `PartNo`    ) AS "IN" ,    (   SELECT       `PartNo`, SUM( `OutQty` ) AS `TotalOut`       FROM `Out` AS `Out` GROUP BY `PartNo`    ) AS "OUT" WHERE "Stock"."PartNo" = "OUT"."PartNo" AND "Stock"."PartNo" = "IN"."PartNo" GROUP BY "Stock"."PartNo"

I created tables to match yours under MySQL 5.0, using JDBC to connect and OOo Dev Build 180 ( 2.0.4 ) with the new Query in Query functions. Under this newer build the query does not require Run SQL direct to be turned on.

Drew
audionuma
OOo Enthusiast

Joined: 23 Feb 2006
Posts: 126
Location: France

Posted: Sun Aug 13, 2006 10:04 pm    Post subject:

I tried drew's suggestion and it works fine under integrated hsqldb base too, with the condition that you have to add all the displayed fields to the last GROUP BY instruction :
 Code: SELECT    "Stock"."PartNo",    "IN"."TotalIn",    "OUT"."TotalOut",    "IN"."TotalIn" - "OUT"."TotalOut" AS "Current Stock" FROM    "Stock",    (   SELECT          `PartNo`, SUM( `InQty` )  as `TotalIn`          FROM `In` AS `In` GROUP BY `PartNo`    ) AS "IN" ,    (   SELECT       `PartNo`, SUM( `OutQty` ) AS `TotalOut`       FROM `Out` AS `Out` GROUP BY `PartNo`    ) AS "OUT" WHERE "Stock"."PartNo" = "OUT"."PartNo" AND "Stock"."PartNo" = "IN"."PartNo" GROUP BY "Stock"."PartNo", "IN"."TotalIn", "OUT"."TotalOut", "Current Stock"
rasat
General User

Joined: 08 Aug 2006
Posts: 12

Posted: Mon Aug 14, 2006 2:47 am    Post subject:

I am using MySQL 5.0.24 with ODBC connection, Linux OOo version 2.0.3, getting parse error when trying to save or run.
 Quote: "parse error, expecting `BETWEEN' or `IN' or `SQL_TOKEN_LIKE'

I have been trying to fix for one hour but the "( )" for the SELECT line, not accepted. In Linux its case sensitive, but that I fixed. I added all the table prefixes in case I missing something. Instead of In table I made it InStock
 Code: SELECT    `StockReg`.`PartNo`,    `InStock`.`TotalIn`,    `OutStock`.`TotalOut`,    `InStock`.`TotalIn` - `OutStock`.`TotalOut` AS `Current Stock` FROM    StockReg,    (   SELECT          `InStock`.`PartNo`, SUM( `InQty` )  as `TotalIn`          FROM InStock AS InStock GROUP BY PartNo    ) AS InStock,    (   SELECT       PartNo, SUM( `OutQty` ) AS `TotalOut`       FROM OutStock AS OutStock GROUP BY PartNo    ) AS OutStock WHERE `StockReg`.`PartNo` = `OutStock`.`PartNo` AND `StockReg`.`PartNo` = `InStock`.`PartNo` GROUP BY `StockReg`.`PartNo`

Last edited by rasat on Mon Aug 14, 2006 5:41 am; edited 1 time in total
DrewJensen
Super User

Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

 Posted: Mon Aug 14, 2006 5:19 am    Post subject: I just went back and tried this query under 2.0.3. it will not run. In my case underer WinXP and using SQL direct I get a different error then yours, but no result set at all. Went back under Dev Build 180, it runs fine. ( however I have found an issue there also, seems this build is mangeling the query, tweaking perhaps a better term, when it is saved - if I don't have Run SQL Direct on ). Also, I have no idea about difference between ODBC/ Linux and JDBC/WinXP..sorry
rasat
General User

Joined: 08 Aug 2006
Posts: 12

Posted: Mon Aug 14, 2006 5:45 am    Post subject:

 DrewJensen wrote: I just went back and tried this query under 2.0.3. it will not run.

Thanks for the information and also for the solution. I will check if I can upgrade to 2.0.4.
For Linux, I edited the correct MySQL syntax (not ODBC) in my previous post. (replaced " with ` ).
DrewJensen
Super User

Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

 Posted: Mon Aug 14, 2006 5:54 am    Post subject: I would not recommend that in a production environment, the developers builds tend to be a bit dicy at times..
rasat
General User

Joined: 08 Aug 2006
Posts: 12

 Posted: Mon Aug 14, 2006 6:44 am    Post subject: Then, what's the solution for 2.0.3? I can change later but for now continue my work. I am converting one FileMaker Pro database to OOoBase. Its for one clinic in Nairobi (Kenya , East Africa). Changing the clinic's six computers from Windows to Linux. I developed the FM Pro databse but in OOoBase I am new.
DrewJensen
Super User

Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

 Posted: Mon Aug 14, 2006 7:29 am    Post subject: well, since you are using MySQL 5.x then you have VIEWS available. I suppose once could construct a view for each of the sub selects and then join these together in a Base query definition for the answer. Drew
DrewJensen
Super User

Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

 Posted: Mon Aug 14, 2006 9:02 am    Post subject: A followup on this. I now see that I can not run ANY query under 2.0.3 that pulls data from the three tables I created using the Base table definition UI for this thread. I can do so for the other tables in this MySQL database, but all of these where created using MySQL administrator, not Base. This includes running the views I created to help with a work around. The views return data fine under the developers Build of Base, they work fine under MySQL tools...under Base 2.0.3 the generate an error stating that the first column is of an unkown data type? I am going to try a few things here. Will drop these tables that I created the other day and recreate them using the MySQL tool. Then see if the query(s) will run.
rasat
General User

Joined: 08 Aug 2006
Posts: 12

 Posted: Wed Aug 16, 2006 3:05 am    Post subject: The suggested query works in MySQL, the parse error is caused by the ODBC connection. Where do I find for ODBC a similar query with two SELECT lines. Want to study how to do the parsing.
rasat
General User

Joined: 08 Aug 2006
Posts: 12

 Posted: Thu Aug 17, 2006 7:39 pm    Post subject: I installed the OOo Dev Build 180, still doesn't work with ODBC. The parse error doesn't appear but simply skips the second SELECT line. I want to try with JDBC connection. I download... http://dev.mysql.com/downloads/connector/j/3.1.html but I am a bit lost where and how to install to get the MySQL JDBC Driver Class: com.mysql.jdbc.Driver. Couldn't find simple instruction in the forum... JDBC search criteria is too common. EDIT This thread tells how to do... http://www.oooforum.org/forum/viewtopic.phtml?t=33199
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum