Active TopicsActive Topics  Display List of Forum MembersMemberlist  Search The ForumSearch  HelpHelp
  RegisterRegister  LoginLogin
PowerHome Programming
 PowerHome Messageboard : PowerHome Programming
Subject Topic: SQL "AVE" doesn’t Post ReplyPost New Topic
Author
Message << Prev Topic | Next Topic >>
GadgetGuy
Super User
Super User
Avatar

Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
Posted: December 17 2014 at 17:15 | IP Logged Quote GadgetGuy

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 View GadgetGuy's Profile Search for other posts by GadgetGuy
 
dhoward
Admin Group
Admin Group
Avatar

Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
Posted: December 17 2014 at 19:01 | IP Logged Quote dhoward

Ken,

The aggregate function is AVG...not AVE .

Dave.
Back to Top View dhoward's Profile Search for other posts by dhoward Visit dhoward's Homepage
 
GadgetGuy
Super User
Super User
Avatar

Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
Posted: December 18 2014 at 06:55 | IP Logged Quote GadgetGuy

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 View GadgetGuy's Profile Search for other posts by GadgetGuy
 
GadgetGuy
Super User
Super User
Avatar

Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
Posted: December 18 2014 at 07:16 | IP Logged Quote GadgetGuy

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 View GadgetGuy's Profile Search for other posts by GadgetGuy
 
dhoward
Admin Group
Admin Group
Avatar

Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
Posted: December 18 2014 at 10:45 | IP Logged Quote dhoward

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 View dhoward's Profile Search for other posts by dhoward Visit dhoward's Homepage
 
GadgetGuy
Super User
Super User
Avatar

Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
Posted: December 18 2014 at 10:54 | IP Logged Quote GadgetGuy

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 View GadgetGuy's Profile Search for other posts by GadgetGuy
 
dhoward
Admin Group
Admin Group
Avatar

Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
Posted: December 18 2014 at 11:06 | IP Logged Quote dhoward

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 View dhoward's Profile Search for other posts by dhoward Visit dhoward's Homepage
 
dhoward
Admin Group
Admin Group
Avatar

Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
Posted: December 18 2014 at 11:07 | IP Logged Quote dhoward

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 View dhoward's Profile Search for other posts by dhoward Visit dhoward's Homepage
 
GadgetGuy
Super User
Super User
Avatar

Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
Posted: December 18 2014 at 12:39 | IP Logged Quote GadgetGuy

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 View GadgetGuy's Profile Search for other posts by GadgetGuy
 
GadgetGuy
Super User
Super User
Avatar

Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
Posted: December 18 2014 at 12:47 | IP Logged Quote GadgetGuy

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 View GadgetGuy's Profile Search for other posts by GadgetGuy
 
dhoward
Admin Group
Admin Group
Avatar

Joined: June 29 2001
Location: United States
Online Status: Offline
Posts: 4447
Posted: December 18 2014 at 12:50 | IP Logged Quote dhoward

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 View dhoward's Profile Search for other posts by dhoward Visit dhoward's Homepage
 

If you wish to post a reply to this topic you must first login
If you are not already registered you must first register

  Post ReplyPost New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum