-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,

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???

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?

MAX(value1, value2, value3..)

Hey Susannah,

YEP!! You are right on, as always.
Thanks for reminding of that one, just don't use it enough! (just one !).

Tom