[CLOSED] Oracles Greatest Aggregate

-SET &WHERESTRING =
-IF ASIS(&vOFS) EQ ''
- THEN ''
- ELSE 'AND GREATEST(DAYSOFS,0) >= &vOFS.EVAL';

ENGINE SQLORA SET DEFAULT_CONNECTION NSR
SQL SQLORA PREPARE SQLOUT FOR
SELECT
state,
SUM(acnt),
SUM(vcnt),
SUM(cnt)
FROM
tableA
WHERE
DATE_STAMP = '&vDATE_STAMP.EVAL'
&WHERESTRING
GROUP BY
state
ORDER BY
state;

Anybody know how to do a FOCUS work around of ORACLES GREATEST FUNCTION? This codes saya if no input variable is passed through &vOFS then sum all records that meet the date requirement. If the the vOFS is set to zero then count all those who have a numeric value. If DAYSOFS is a negative number then GREATEST returns a zero and those records are counted. If the &vOFS is greater than zero then the GREATEST function is not really include in the summing since it will return a zero for anything less than or equal to zero.

This message has been edited. Last edited by: Kerry,

Webfocus 7.6.6
Solaris 10 Sparc 64bit

Posts: 20 | Registered: January 21, 2009

Tom Flynn

Expert

posted

Hide Post

quote:

In Oracle/PLSQL, the greatest function returns the greatest value in a list of expressions.
The syntax for the greatest function is:

greatest( expr1, expr2, ... expr_n )

expr1, expr2, . expr_n are expressions that are evaluated by the greatest function.

If the datatypes of the expressions are different, all expressions will be converted to whatever datatype expr1 is.

If the comparison is based on a character comparison, one character is considered greater than another if it has a higher character set value.

Applies To:

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:

greatest(2, 5, 12, 3) would return 12
greatest('2', '5', '12', '3') would return '5'
greatest('apples', 'oranges', 'bananas') would return 'oranges'
greatest('apples', 'applis', 'applas') would return 'applis'

Seems you "only" want records LE 0:

  
-SET &WHERESTRING = IF ASIS(&vOFS) EQ '' OR &vOFS GT 0 THEN ''
- ELSE 'AND DAYSOFS <= 0';

Is that what you are looking to do???

Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe

Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006

Ignored post by Tom Flynn posted

Show Post

FrankDutch

Virtuoso

posted

Hide Post

quote:
a FOCUS work around

Are you familiar with Webfocus?

Do you have the example databases like CAR?
Do you have a oracle connector?
Can you create a MFD for the SQL database?

	*Frank*
	prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7