| Author |
|
GadgetGuy
Super User
Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
|
| Posted: December 17 2014 at 17:15 | IP Logged
|
|
|
According to the Sybase documentation this is a valid expression to find the AVG data value over a column...
Code:
| SELECT AVG ( salary ) FROM Employees |
|
|
yet this doesn't work in PH...
Why not?
__________________
Ken B - Live every day like it's your last. Eventually, you'll get it right!
|
| Back to Top |
|
| |
dhoward
Admin Group
Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
|
| Posted: December 17 2014 at 19:01 | IP Logged
|
|
|
Ken,
The aggregate function is AVG...not AVE  .
Dave.
|
| Back to Top |
|
| |
GadgetGuy
Super User
Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
|
| Posted: December 18 2014 at 06:55 | IP Logged
|
|
|
Duh! None are so blind as those that cannot see (or spell) ! All I had to do was look at the Sybase code sample !!! After fighting this for the better part of a day, apparently I was too tired to see straight.
THANKS.
OK. Corrected that but I seem to be getting zero as an answer every time.
Am I extracting the results from the SQL SELECT correctly? If not, that would explain my "0" contents in both temps. There are only 20 entries in each column, and the remainder of the column is all Null filled (or at least should be as I have written nothing into them), and nothing is visible when the table is viewed.
NOW the big question. This userdata2 table is proving to be very restrictive for what I am attempting to do, and I would rather not have to do multiple Table interactions to handle all the interrelated data I have. Can I define my own Table using CREATE TABLE? If so, is there anything else I need to be aware of?
__________________
Ken B - Live every day like it's your last. Eventually, you'll get it right!
|
| Back to Top |
|
| |
GadgetGuy
Super User
Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
|
| Posted: December 18 2014 at 07:16 | IP Logged
|
|
|
NEVER MIND. I just discovered the SQL Help section in PH. 
Using...
in a Macro Formula entry returned the result into LOCAL6 where I can use it.
I'm on a roll now!
But do answer the create a custom data table question.
Edited by GadgetGuy - December 18 2014 at 07:17
__________________
Ken B - Live every day like it's your last. Eventually, you'll get it right!
|
| Back to Top |
|
| |
dhoward
Admin Group
Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
|
| Posted: December 18 2014 at 10:45 | IP Logged
|
|
|
Ken,
The ph_directsql function would only be used for insert, update, and delete sql statements. The myriad ph_sql??? functions would be used for select.
Unfortunately no. The runtime version of SQL anywhere has serious limitations including no triggers, stored procedures, or DDL (Database Definition Language which includes things such as create table).
Dave.
|
| Back to Top |
|
| |
GadgetGuy
Super User
Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
|
| Posted: December 18 2014 at 10:54 | IP Logged
|
|
|
As Google would say "Oh snap, Jim!"
I had a really nice schema all worked out too. :-(
Any chance of you changing some of those userData Tables
'valnun' columns from Integer to Long for 2.1.5b?
Way too many Integer fields, now, to be useful.
__________________
Ken B - Live every day like it's your last. Eventually, you'll get it right!
|
| Back to Top |
|
| |
dhoward
Admin Group
Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
|
| Posted: December 18 2014 at 11:06 | IP Logged
|
|
|
Ken,
If you're wanting a long datatype, you should be good as an "integer" in SQL anywhere is 4 bytes (same as a long in PowerBuilder/PowerHome). A PowerBuilder integer is only two bytes (values -32768 thru 32767) and would be the equivalent of a SQL Anywhere smallint. See page 60 of the document I referenced above for explanation of integer.
I didnt include any long datatypes (8 bytes) in the userdata tables as they wouldnt be able to be directly used by PowerHome since we don't currently have any 8 byte datatypes.
If you were meaning double instead of long, then you should still be in good shape. The way we ususally handle this is to decide on how much precision you want to store and then multiply and store the integer. You can then divide in the SQL select and restore the original number.
Say you want to decimal digits of precision. We'll multiply by 100 and store that value 77.82 becomes 7782. In the SQL select, well use:
select valnum / 100 from userdata
Dave.
|
| Back to Top |
|
| |
dhoward
Admin Group
Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
|
| Posted: December 18 2014 at 11:07 | IP Logged
|
|
|
GadgetGuy wrote:
Any chance of you changing some of those userData Tables
'valnun' columns from Integer to Long for 2.1.5b?
|
|
|
Ken,
Not sure if you noticed, but 2.1.5b is already out.
Dave.
|
| Back to Top |
|
| |
GadgetGuy
Super User
Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
|
| Posted: December 18 2014 at 12:39 | IP Logged
|
|
|
OMG!!!!!
No. I had not noticed. I didn't expect it until next
week.
AWESOME !! 
[:-)}
__________________
Ken B - Live every day like it's your last. Eventually, you'll get it right!
|
| Back to Top |
|
| |
GadgetGuy
Super User
Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
|
| Posted: December 18 2014 at 12:47 | IP Logged
|
|
|
dhoward wrote:
Ken,
If you're wanting a long datatype, you should be good
as an "integer" in SQL anywhere is 4 bytes (same as a
long in PowerBuilder/PowerHome).
Dave. |
|
|
I should have said "double", my bad!
All I need is one decimal place so I can use Temps
such as "23.5" degrees. Sounds like a multiply by 10
(and divide back) will do it.
I think I'm good now.
This should complete all my data gathering so I can
then Chart it with the new charting tool.
Right now, I'm getting my 1-wire data courtesy of
LogTemp (my old charting effort).
Next step is to tear into your new 1-wire
functionality, and get 'er goin'.
Thanks again for your assistance in getting SQL and
Charting going. Hopefully you caught my posts about
both in the PowerHome Macros, Scripts, and Formulas
Repository.
__________________
Ken B - Live every day like it's your last. Eventually, you'll get it right!
|
| Back to Top |
|
| |
dhoward
Admin Group
Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
|
| Posted: December 18 2014 at 12:50 | IP Logged
|
|
|
Ken,
Happy to be of assistance. I think you'll like the new 1-wire capability. Its been working pretty well for me.
Dave.
|
| Back to Top |
|
| |
Active Topics 
Memberlist 
Search 
Help
Register 
Login
PowerHome Messageboard : PowerHome Programming
Topic: SQL "AVE" doesn’t
