| View previous topic :: View next topic |
| 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):
 |
|
| Back to top |
|
 |
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). |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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" |
|
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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 ` ). |
|
| Back to top |
|
|
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.. |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
|
