Source language: Translate to:

SQL Command Question

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

SQL Command Question

Postby Wrangler » Tue Jul 22, 2008 12:19 pm

I'm currently using this to sum up a large number of currency fields in an Access db:

dbpSum "mytrip" "[tripname]" "user6" "[totaluser6]"
Math "[totalfields]+[totaluser6]" "2" "[totalfields]"
dbpSum "mytrip" "[tripname]" "user5" "[totaluser5]"
Math "[totalfields]+[totaluser5]" "2" "[totalfields]"
dbpSum "mytrip" "[tripname]" "user4" "[totaluser4]"
Math "[totalfields]+[totaluser4]" "2" "[totalfields]"
dbpSum "mytrip" "[tripname]" "user3" "[totaluser3]"
Math "[totalfields]+[totaluser3]" "2" "[totalfields]"
dbpSum "mytrip" "[tripname]" "user2" "[totaluser2]"
Math "[totalfields]+[totaluser2]" "2" "[totalfields]"
dbpSum "mytrip" "[tripname]" "user1" "[totaluser1]"
Math "[totalfields]+[totaluser1]" "2" "[totalfields]"

and so on........

This is REALLY slow. Is there an sql statement that would speed things up?

I googled it, but could only find the statement to sum one field, not all the fields.

Thanks!
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Gaev » Tue Jul 22, 2008 12:47 pm

Wrangler:

Do you just need to know [totalfields] or also each of the individual [totaluserX] values as well ? ... for just a single total, you might try something like ...
Code: Select all
SELECT SUM(totaluser1 + totaluser2 + totaluser3 etc.) as "TotalFields" FROM [tripname];
Haven't tried it myself on an Access.mdb file ... but give it a go ... might even be able to end up with a temporary one row (summary) table ... otherwise, will require a second sweep to sum the field of "horizontal sum of fields" (TotalFields)
User avatar
Gaev
 
Posts: 3717
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Wrangler » Tue Jul 22, 2008 1:15 pm

Do you just need to know [totalfields] or also each of the individual [totaluserX] values as well ?


Actually, I could use both. The single total would be used as a running total, and the individual values would be nice before I generate a report.

Your example shows using the neobook variables [totaluserx] for what to sum. I want to sum the data located in the db fields userx etc. I tried this (different fieldnames, same db):

select SUM(CLOTHING + FOOD + GIFTS) as "TotalFuel" FROM [tripname];

but I get an error:

"Cannot perform this operation on a closed dataset."

DB is open, and table is open.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Gaev » Tue Jul 22, 2008 4:29 pm

Wrangler:

I created an Access Database called Wrangler1 ... with a Table called Trip1 ... containing fields ...

TripDesc (String(30))
CostCat1 (Currency)
CostCat2 (Currency)
CostCat3 (Currency)


... and populated it with 3 records (rows).

Here is code associated with first Button ...
Code: Select all
dbpOpenAccessDatabase "Wrangler1" "[PubDir]Wrangler1.mdb" ""
dbpOpenTable "Wrangler1" "Trip1" ""

dbpShowGrid "Wrangler1" "Trip1" "TripGrid"

... and code associated with second button ...
Code: Select all
dbpExecSQL "Wrangler1" "Select SUM(CostCat1) as CostSum1, SUM(CostCat2) as CostSum2, SUM(CostCat3) as CostSum3 from Trip1" ""

SetVar "[gkCostSum1]" "[Wrangler1.Trip1.CostSum1]"
AlertBox "gkCostSum1" "[gkCostSum1]"


When I click on second button, the Grid is replaced with one record/row ... with columns called CostSum1, CostSum2 and CostSum3 ... and (to confirm I am still in the same table ... AlertBox displays expected sum of values in CostCat1

Once you get the "sum of columns" ... you should be able to get the (horizontal) "sum of sums" without incuring any performance penalty.
User avatar
Gaev
 
Posts: 3717
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Gaev » Wed Jul 23, 2008 7:36 am

... update ...

Also tried this in second button ...
Code: Select all
dbpExecSQL "Wrangler1" "Select SUM(CostCat1) as CostSum1, SUM(CostCat2) as CostSum2, SUM(CostCat3) as CostSum3 from Trip1" "TripSum1"

dbpOpenTable "Wrangler1" "TripSum1" ""
dbpShowGrid "Wrangler1" "TripSum1" "SumGrid"

SetVar "[gkCostSum1]" "[Wrangler1.TripSum1.CostSum1]"
AlertBox "gkCostSum1" "[gkCostSum1]"
... and when I click on second button, the one row of Sums now show up in the (newly created) Grid/Rectangle called SumGrid ... and AlertBox gives expected value.

So, you can choose to have summary data automatically placed in another (temporary) Table ... and view it simultaneously with (original) detailed rows of data.
User avatar
Gaev
 
Posts: 3717
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Wrangler » Wed Jul 23, 2008 7:43 am

Thanks, Gaev. I haven't had time to implement your suggestions yet. Got called away. Will do so later today and report back.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Wrangler » Wed Jul 23, 2008 9:02 am

Can this be used to put the totals into neobook variables?

dbpExecSQL "mytrip" "Select SUM(CLOTHING) as [CostSum1], SUM(FOOD) as [CostSum2], SUM(GIFTS) as [CostSum3] from [tripname]" ""

AlertBox "" "[CostSum1]|[CostSum2]|[CostSum3]"

This doesn't work. I don't need the results to display in a grid. I'd like to use it in the textchange action in a textentry box to display a grand total on the fly, and also right before generating a report.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Gaev » Wed Jul 23, 2008 9:44 am

Wrangler:
Can this be used to put the totals into neobook variables?
Yes ... but not the way you are trying to do.

Code: Select all
dbpExecSQL "mytrip" "Select SUM(CLOTHING) as [CostSum1], SUM(FOOD) as [CostSum2], SUM(GIFTS) as [CostSum3] from [tripname]" ""

AlertBox "" "[CostSum1]|[CostSum2]|[CostSum3]"

a) in the SELECT statement of SQL, the word following the AS clause will be the name of a Column

b) If you specify something like "AS [CostSum1]" ... NeoBook will replace [CostSum1] with its current value (most likely null) before passing the "replaced text" to the Access/SQL engine.

This doesn't work. I don't need the results to display in a grid. I'd like to use it in the textchange action in a textentry box to display a grand total on the fly, and also right before generating a report.

a) Asking the database engine to traverse the entire database between keystrokes in a TextEntry Box is going to cause problems ... until Intel comes out with a 100 GHz machine ... and your database fits in memory !!!

b) However, you can have a button for user to request a Refresh of the Sum values.

c) You don't need to display anything in a Grid ... but you need to open the (temporary) Table and copy the contents of the columns to NeoBook variables ...
Code: Select all
dbpExecSQL "Wrangler1" "Select SUM(CostCat1) as CostSum1, SUM(CostCat2) as CostSum2, SUM(CostCat3) as CostSum3 from Trip1" "TripSum1"

dbpOpenTable "Wrangler1" "TripSum1" ""

SetVar "[gkCostSum1]" "[Wrangler1.TripSum1.CostSum1]"
SetVar "[gkCostSum2]" "[Wrangler1.TripSum1.CostSum2]"
SetVar "[gkCostSum3]" "[Wrangler1.TripSum1.CostSum3]"
... and might consider dbpDropTable after you have extracted desired values.
User avatar
Gaev
 
Posts: 3717
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Wrangler » Wed Jul 23, 2008 10:59 am

You da man, Gaev! I'm starting to get the picture now.

I agree with you about totaling on the fly, even though I was doing it with the dbpsum routine, which was slow, but worked. But my end users may not have the ram I have, so I dropped that feature. Instead they can get a breakdown when they generate a report.

I'm beginning to see the value of temporary tables, something I've not played with in the past.

I'm gonna play with this and adapt it to my needs. Thanks much for the help. I'll get back to you if I hit any snags. :)
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Wrangler » Wed Jul 23, 2008 11:50 am

For some reason I can't get this to work:

dbpExecSQL "mytrip" "Select SUM(clothing) as CostSum1, SUM(food) as CostSum2, SUM(gifts) as CostSum3 from [tripname]" "TripSum1"

dbpOpenTable "mytrip" "TripSum1" ""

SetVar "[gkCostSum1]" "[mytrip.TripSum1.CostSum1]"
SetVar "[gkCostSum2]" "[mytrip.TripSum1.CostSum2]"
SetVar "[gkCostSum3]" "[mytrip.TripSum1.CostSum3]"
AlertBox "" "[gkCostSum1]|[gkCostSum2]|[gkCostSum3]"

without getting the error "cannot perform this operation on a closed dataset". I googled that, but there were too many different scenarios out there, none of which seems to apply to my situation.

The database (mytrip) shows connected, the table ([tripname]) exists. I tried replacing [tripname] with hard copy table name (Baker to Reno), and it still didn't work.

Can you see anything I'm doing wrong?

Added: I have the above code in a button, executing it after the database loads and displays properly.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby smartmedia » Wed Jul 23, 2008 12:22 pm

Hi...

Try to use the ; at the end of your command... This sould make it work... ; say that query is over...

Example:
dbpExecSQL "Rapidshare" "SELECT Points As TPoints from Users where Username = '[Users.Username]';" "Users"
Last edited by smartmedia on Wed Jul 23, 2008 12:34 pm, edited 1 time in total.
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Wrangler » Wed Jul 23, 2008 12:30 pm

Hi Smartmedia,

No, it's not empty. The data in [tripname] comes from a selection in a listbox, and is used to open the database table. All the variables associated with this show the correct data in the debugger.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Wrangler » Wed Jul 23, 2008 12:36 pm

Changed it to this:

dbpExecSQL "mytrip" "Select SUM(clothing) as CostSum1, SUM(food) as CostSum2, SUM(gifts) as CostSum3 from [tripname];" "TripSum1"

Still get the closed dataset error.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby smartmedia » Wed Jul 23, 2008 12:48 pm

Hi...

Try this:

dbpExecSQL "mytrip" "Select SUM(clothing) as CostSum1, SUM(food) as CostSum2, SUM(gifts) as CostSum3 from [tripname];" ""
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Wrangler » Wed Jul 23, 2008 12:51 pm

Same error. :(
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Next

Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest

cron