Table has a numeric column called COLA that can range from any negative number to any positve number. On the HTML if the user leaves COLA blank then count the total number of records else if COLA is >= 0 then count just those records that meet this condition.

I have the second part working WHERE COLA GE &HTML_VAR

How do I incoporate the first condtion into this?

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

Introduce an other amper value.

-SET &WHERESTRING = IF &HTML_VAR EQ 0 THEN '' ELSE 'WHERE COLA GE &HTML_VAR ;'
...
...
TABLE FILE ....
PRINT *
..
&WHERESTRING
END

The disadvantage is that in this case the GUI does not work anymore.

Is there a line size limitaion? Here is the actual line of code I use. It worked with just OFS but when I added DATE_STAMP I get an error

-SET &WHERESTRING = IF &OFS EQ 0 THEN 'WHERE DATE_STAMP EQ DT(&DATE_STAMP);' ELSE 'WHERE ( DAYSOFS GE &OFS ) AND ( DATE_STAMP EQ DT(&DATE_STAMP) );'


I get this error message
0 ERROR AT OR NEAR LINE 13 IN PROCEDURE fdp FOCEXEC *
(FOC177) INVALID DATE CONSTANT: DATE_STAMP
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

Any thoughts?

What is the format of DATE_STAMP?

What does &DATE_STAMP look like?

If the real field format starts with an H then the amper variable value must look like a date/timestamp.

Code was working when all I had was

WHERE ( DAYSOFS GE &OFS ) AND ( DATE_STAMP EQ DT(&DATE_STAMP) );

humor me and put a space after the &DATE_STAMP and before the first ).
and split your code into 2 lines


-SET &WHERESTRING = IF &OFS EQ 0 THEN 'WHERE DATE_STAMP EQ DT(&DATE_STAMP ) ' ELSE
-'WHERE ( DAYSOFS GE &OFS ) AND ( DATE_STAMP EQ DT(&DATE_STAMP ) ) '

and those ; at the end of a WHERE statement are just gui artefacts and make me (and other hand-coders) nuts. well, ok, more nuts.

You can also do WHERE statements like this
-SET &cmt = IF &OFS EQ 0 THEN '-*' ELSE '';
and in your code
TABLE FILE whatever
..
WHERE ( DATE_STAMP EQ DT(&DATE_STAMP ) )
&cmt.EVAL AND (DAYSOFF GE &OFS )

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

quote:

I get this error message
0 ERROR AT OR NEAR LINE 13 IN PROCEDURE fdp FOCEXEC *
(FOC177) INVALID DATE CONSTANT: DATE_STAMP
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

Any thoughts?

The error message dropped a &, should say:
(FOC177) INVALID DATE CONSTANT: &DATE_STAMP

It's elementary dialog manager: You need to add .eval to force evaluation of amper variables appearing within a quote in the RHS expression of a -SET. As it stands, the "&DATE_STAMP" within the quoted WHERE phrases is treated as a literal.

Run this to see the difference:

-SET &ECHO=ON;
-SET &DATE_STAMP='20091231 11:59PM';
-SET &OFS=7;

-SET &WHERESTRING = 
- IF &OFS EQ 0 
-  THEN 'WHERE ( DATE_STAMP EQ DT(&DATE_STAMP) );' 
-  ELSE 'WHERE ( DAYSOFS GE &OFS ) AND ( DATE_STAMP EQ DT(&DATE_STAMP) );';

* &WHERESTRING


-SET &WHERESTRING = 
- IF &OFS EQ 0 
-  THEN 'WHERE ( DATE_STAMP EQ DT(&DATE_STAMP.EVAL) );' 
-  ELSE 'WHERE ( DAYSOFS GE &OFS.EVAL ) AND ( DATE_STAMP EQ DT(&DATE_STAMP.EVAL) );';

* &WHERESTRING

The starred lines are echoed as:

* WHERE ( DAYSOFS GE &OFS ) AND ( DATE_STAMP EQ DT(&DATE_STAMP) ); < -- without .eval, no replacement
* WHERE ( DAYSOFS GE 7 ) AND ( DATE_STAMP EQ DT(20091231 11:59PM) ); < -- with .eval

Additional Thoughts:

* This would have been easier to spot if you turn &ECHO on, and posted the echoed code in the vicinity of "LINE 13": the & in the middle of the echoed code is a dead giveaway.

* Part of the confusion is the similarity of DATE_STAMP and &DATE_STAMP. Now that you know WF is prone to such scribal errors in its messages, try to keep your amper variable names distinct from Focus (mfd, define) variable names.

This message has been edited. Last edited by: j.gross,

Thanks Jack. I have changed all my screen variables to have a v in front of them. The EVAL worked. One question about the screen varaibles. The results I get depend on the screen variable &vOFS. I should get different results depending on if &vOFS is a number (including 0) or it is not there at all. I looked at the HTML when nothing is sent a the value is " ". I test with IF &vOFS EQ ' '. It doesn'r work. Any ideas?

-IF &vOFS EQ ' '
- THEN 'WHERE ( DATE_STAMP EQ DT(&vDATE_STAMP.EVAL) );'
- ELSE 'WHERE ( DAYSOFS GE &vOFS.EVAL ) AND ( DATE_STAMP EQ DT(&vDATE_STAMP.EVAL) );'

See my notes to '.' and EVAL earlier this month.

Two solutions:

asis():
-IF ASIS(&vOFS) EQ ' '

catenation:
-IF X|&vOFS EQ X

That also worked. Good Stuff. After running the report turns out my numbers were off. If DAYSOFS is set to zero then all values that are negative have to be counted. In oracle I use the greatest function. So that if the report wants DAYSOFS >= 0 then you take the greater value (neg number or 0) Of course it is 0. Is there a way to do that in Webfosus other than the road I will explain now? I used a SQL statement to access the data directly. The only problem I have is formating the sql statement with a dynamic where clause. How do I get the WHERESTRING into the SELECT statement?

SET &WHERESTRING =
-IF ASIS(&vOFS) EQ ''
- THEN 'WHERE DATE_STAMP = &vDATE_STAMP.QUOTEDSTRING '
- ELSE 'WHERE GREATEST(DAYsOFS,0) >= &vOFS.EVAL ) AND DATE_STAMP = &vDATE_STAMP.QUOTEDSTRING ';

SQL SQLORA SET SERVER servername
SQL SQLORA
SELECT
state,
SUM(addrcnt),
SUM(validcnt),
SUM(cnt),
FROM
table_name
WHERE
date_stamp = '2009-01-31'
and greatest(daysofs,0) >= 0
GROUP BY
ROLLUP(state)
ORDER BY
state NULLS FIRST;

Change the WHEREs to ANDs and include the Dialogue Manager variable in the SELECT statement:

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

SQL SQLORA SET SERVER servername
SQL SQLORA
SELECT
state,
SUM(addrcnt),
SUM(validcnt),
SUM(cnt),
FROM
table_name
WHERE
date_stamp = '2009-01-31'
and greatest(daysofs,0) >= 0
&WHERESTRING
GROUP BY
ROLLUP(state)
ORDER BY
state NULLS FIRST;