6 replies to this topic

[priya_thomas]

Hi,
How will we use aggregate sql function inside proiv.
Say for eg:, I need SUM() of 1 column. Can I use SUM function inside SQL --- ENDSQL. If so, how?
Rgds Priya

[mglaeser]

Include columns leading up to the one you want to SUM.
If they are ALPHA just COUNT(ALPHACOLUMN) them.

HTH
MARKIV

[Mike Schoen]

You can use a pro-iv filedef defined as a 10-character alpha, and then on the before read logic of the file, do something like this:
SQL
SELECT TO_CHAR( SUM( COLNAME),'9999999999') AS RESULT
WHERE .....
ENDSQL
And then do a CONV(RESULT) on the no error logic

[andykay]

You can also place the result of the SUM directly into a variable.

SQL
BEGIN
SELECT SUM(Column_Name) INTO :#SUM FROM Table_Name WHERE...
;
ENDSQL

Good Luck,

AK

[Mike Schoen]

SQL
BEGIN
SELECT SUM(Column_Name) INTO :#SUM FROM Table_Name WHERE...
;
ENDSQL

Wish I had known that trick earlier. (Or read the manual mabye? )
Thats what I get for finding the first solution to a problem and then not looking further.

[tclulow]

We've had problems using INTO for numeric variables - they get truncated to integers.

Even this:
SQL
select numeric_column into :#VALUE where another_column = :#ANOTHER_VALUE
ENDSQL

Results in #VALUE and #ANOTHER_VALUE being truncated to integer.


I've a feeling that INTO isn't officially supported.


Tony.