I am trying to create a DYNAMIC WHERE statement but having a little difficulty with the syntax.

Can anyone help me?

 -SET &QT = '''';
 -SET &FSALES = 'ENGLAND';
 -*SET &COUNTRY = 'FRANCE';
 -SET &FCNTRY = IF ENGLAND EQ 'ENGLAND'
 -  THEN
 - ('WHERE COUNTRY EQ '| ' || 'ENGLAND'   || ' | ' OR ' | ' || 'FRANCE' || ' | ' OR ' | ' || 'ITALY' || ' ||';')
 -ELSE IF ENGLAND EQ 'JAPAN'
 -  THEN
 - ('WHERE COUNTRY EQ '| ' || 'W GERMANY' || ' | ' OR ' | ' || 'JAPAN'  || ' ||';')
 -ELSE ' ';
 0 ERROR AT OR NEAR LINE     11  IN PROCEDURE dynam_where.fex
 (FOC305) SPECIFIED LABEL NOT FOUND:

This message has been edited. Last edited by: <Kathryn Henning>,

First off, I would not include WHERE inside of the variable so that the code is GUI compatible. i.e.:

 
TABLE FILE CAR
...
WHERE &FCNTRY
 

Second off, the error has to do with spacing or something similar with your extended dialogue manager command.

Try putting the set command on one line and get it working. Then you can extend it to multiple lines for better reading and you can see exactly what is causing the error.

Try this Tom:

SET PAGE = NOLEAD
-SET &ECHO = ALL ;
-SET &QT = '''';
-SET &FSALES = 'ENGLAND';
-*-SET &FSALES = 'JAPAN';
-SET &COUNTRY = 'FRANCE';
-SET &FCNTRY = IF &FSALES EQ 'ENGLAND'
-  THEN
- 'WHERE COUNTRY EQ '| '''' || 'ENGLAND'   || '''' | ' OR ' | '''' || 'FRANCE' || '''' | ' OR ' | '''' || 'ITALY' || '''' ||';'
- ELSE IF &FSALES EQ 'JAPAN'
-  THEN
- 'WHERE COUNTRY EQ '| '''' || 'W GERMANY' || '''' | ' OR ' | '''' || 'ENGLAND'  || '''' ||';'
- ELSE ' ';
TABLE FILE CAR
HEADING
"&FCNTRY.EVAL"
SUM DCOST BY COUNTRY
&FCNTRY.EVAL
END

To get this:

Those Pesky Single-Quotes get in the way so many times...

Oh how nice it would be to live inside the GUI and not outgrow it...

quote:

First off, I would not include WHERE inside of the variable so that the code is GUI compatible.

Smart / Dynamic Code is So Good To Use.

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

quote:

Smart / Dynamic Code is So Good Use Smiler

Indeed!

Yes, and smart dynamic code that is mostly GUI compatible is even better. So many new features to discover and so many smoother upgrades.

Little tricks like

 
WHERE &WHERE;
INSTEAD of just
&WHERE;
 

make all the difference.


While we're at it:

 

TABLE FILE CAR
PRINT
-IF &USE_CAR EQ 'NO' THEN GOTO SKIP_CAR;
     CAR
-SKIP_CAR
...


Why not use:

-SET &CAR_TITLE = IF &PRINT_CAR EQ 'NO' THEN 'FOC_NONE' ELSE 'Car';
...
TABLE FILE CAR
PRINT
     CAR AS '&CAR_TITLE'
...
 

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

Hey Doug, Long time no see....

Thank you for the improvement! The single QUOTES are like gnats in your eye.

After reading the posts I want to thank everyone for their input. I did some research
and I could not find any documentation on WHERE &WHERE.

LOL : "like gnats in your eye"

You can use

WHERE COUNTRY EQ &DYNAMIC_WHERE

just don't leave the parameter empty. E.g. use '_FOC_NULL'.



______
And for the best of both worlds... ( coding & gui ).

We have a ground rule:
Seperate all query-logic from display-logic.

Use (handcoded ) code to retrieve the data, add filters, make computes, everything... ...everything!
Put it in a HOLD file.

Use the GUI to make 'dumb' report using that HOLD file, but try to add as little logic as possible.


G'luck,
Dave

I would like to add 2 things:
1. When continuing a DM phrase on a following line, leave at least 1 space between the hyphen and the continuation.
2. The .EVAL suffix is only necessary in DM phrase when you want WF to "evaluate" the &variable before executing the phrase. For example:

-SET &LEN=&VAR.LENGTH;
-SET &NVAR=RJUST(&LEN, &VAR, 'A&LEN.EVAL');  

When the &variable just has to be substituted, like in Doug's

TABLE FILE CAR
HEADING
"&FCNTRY.EVAL"
SUM DCOST BY COUNTRY
&FCNTRY.EVAL
END

it is not necessary.

Yea... But if you hand code until the final report you are missing out on things like this:

 
-*TBL_MORE_DEFINES_START
-* The code in this section bracketed by -*TBL_MORE_DEFINES_START and -*TBL_MORE_DEFINES_END is auto generated and
-* managed by the Universal Concatenation tool in DevStudio's Report Painter. The code is generated from the comment
-* in the report starting with -*FFW.MORE and is required for the MORE statements in the report.
DEFINE FILE CAR ADD
CAR_DEFINE/A16=CAR.COMP.CAR;
END
-*TBL_MORE_DEFINES_END
TABLE FILE CAR
-*FFW.MORE - Managed by DevStudio's Report Painter for MORE statements.  Do not edit!
-*FFW.MORE <CAR=DEFINE FILE CAR ADDCAR_DEFINE/A16=CAR.COMP.CAR;END>
PRINT 
     CAR.ORIGIN.COUNTRY
     CAR_DEFINE
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE HOLD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
MORE
FILE CAR
END


TABLE FILE HOLD
PRINT 
     HOLD.HOLD.COUNTRY
     HOLD.HOLD.CAR_DEFINE
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END
 

I don't know if I would want to memorize generated comments like that.

BTW there are benefits to editing in both. The text editor can be much faster when you know the syntax and for things like copying or commenting. It just depends. But don't block out the GUI or you will be missing on a lot of cool things you can do

 
TABLE FILE CAR
PRINT 
     &RP_FL04P01_Detail4.(<BODYTYPE,CAR.BODY.BODYTYPE>,<SEATS,CAR.BODY.SEATS>,<DEALER_COST,CAR.BODY.DEALER_COST>,<RETAIL_COST,CAR.BODY.RETAIL_COST>,<SALES,CAR.BODY.SALES>,<None,_FOC_NULL>).Please select detail field(s).
     &RP_FL05P02_Detail5.(<BODYTYPE,CAR.BODY.BODYTYPE>,<SEATS,CAR.BODY.SEATS>,<DEALER_COST,CAR.BODY.DEALER_COST>,<RETAIL_COST,CAR.BODY.RETAIL_COST>,<SALES,CAR.BODY.SALES>,<None,_FOC_NULL>).Please select detail field(s).
     &RP_FL06P03_Detail6.(<BODYTYPE,CAR.BODY.BODYTYPE>,<SEATS,CAR.BODY.SEATS>,<DEALER_COST,CAR.BODY.DEALER_COST>,<RETAIL_COST,CAR.BODY.RETAIL_COST>,<SALES,CAR.BODY.SALES>,<None,_FOC_NULL>).Please select detail field(s).
     &RP_FL07P04_Detail7.(<BODYTYPE,CAR.BODY.BODYTYPE>,<SEATS,CAR.BODY.SEATS>,<DEALER_COST,CAR.BODY.DEALER_COST>,<RETAIL_COST,CAR.BODY.RETAIL_COST>,<SALES,CAR.BODY.SALES>,<None,_FOC_NULL>).Please select detail field(s).
     &RP_FL08P05_Detail8.(<BODYTYPE,CAR.BODY.BODYTYPE>,<SEATS,CAR.BODY.SEATS>,<DEALER_COST,CAR.BODY.DEALER_COST>,<RETAIL_COST,CAR.BODY.RETAIL_COST>,<SALES,CAR.BODY.SALES>,<None,_FOC_NULL>).Please select detail field(s).
BY  &RP_FN01B_Sort1.(BY(<COUNTRY,CAR.ORIGIN.COUNTRY>,<CAR,CAR.COMP.CAR>,<MODEL,CAR.CARREC.MODEL>)).Please select sort field(s).
ON TABLE SET ONFIELD IGNORE
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END
 

TOM, the where &where isn't documented as far as I can tell, but I do know that doing it that way allows me to edit via the GUI. I don't have to worry about the next guy or my coworkers as much this way.

J, how do you use the same reports in reportcaster when you want to omit the filter condition? I thought _FOC_NONE and caster were incompatible.

That's why we use a parameter to either write the entire where condition OR to just toss in an empty string.

Report Caster just wipes out the FOC_NONE statement if I am remembering correctly. Then you can use a -DEFAULT statement at the beginning.

-DEFAULT &CAR_TITLE = 'Car';
-SET &CAR_TITLE = IF &PRINT_CAR EQ 'NO' THEN 'FOC_NONE' ELSE &CAR_TITLE;
...
TABLE FILE CAR
PRINT
     CAR AS '&CAR_TITLE'
...
 

Doug I am surprised you didn't know about the send display value

this old post

I guess we are rediscovering one of the hidden WebFOCUS gems.

So far, I haven't found anything the GUI can do that I can't.

Perhaps the code generated by the Compound Document tool is one where I would use the GUI to generate, then I would hand code to my heart's content.

We have some pretty nasty elaborate reports that require A LOT of coding. ( in DM )

But come to think of it... ...that probably could all be done in the GUI. We just don't do that since we feel it's faster to just type it ( and copy / paste ).
100+ lines of 'processing' DM stuff happens...


What I'm trying to say / preach is:
Seperate the logic from the display/output part.

( Thus retracting my statement that the logic part should be hand coded, it can be done in the GUI also )



and J,

I don't get your 'missing out' part?

TABLE FILE CAR
	BY	COUNTRY
	BY	CAR
ON TABLE HOLD AS X
END

TABLE FILE X
	PRINT	COUNTRY CAR
ON TABLE PCHOLD FORMAT HTML
MORE
FILE X
END

Hand coded, works fine ?

Oh,

and on the subject of _FOC_NONE

You might want to use _FOC_NULL instead.

What is wrong with FOC_NONE?

When dealing with ignores, I use _FOC_NULL, but for this other stuff, FOC_NONE seems to work pretty well. I am curious what some of the FOC_NONE issues you guys have had. According to what I have seen, you just have to be careful how you handle FOC_NONE (as in the link).

Tom, I am sorry to divert from your topic.

J, Huh? Not sure what you mean by your June 24, 2013 09:33 AM post. Or, was that just pointing back to my olod post from Mon Feb 07 2011 15:53?

J,

The trick is that FOC_NONE has the effect of ignore the complete line of code. And you have to keep that in mind.

See sample 1:

-SET &IGNORE = 'FOC_NONE';

TABLE FILE CAR
	SUM SALES
	BY	COUNTRY
	BY	CAR
WHERE ( COUNTRY EQ '&IGNORE' ) AND ( CAR EQ 'TOYOTA' OR 'BMW' )
END

Because the parameter IGNORE is 'FOC_NONE' the entire line of code is ignored, including : ( CAR EQ 'TOYOTA' OR 'BMW' )
Output just shows the entire contents of the CAR database.


Now change FOC_NONE to _FOC_NULL. This will only effect the COUNTRY-where not the CAR-where.
Output will should only TOYOTA and BMW.


And this even works if you change the code to

WHERE ( COUNTRY EQ '&IGNORE' ); WHERE ( CAR EQ 'TOYOTA' OR 'BMW' )

I find this very tricky... ...better to avoid it.


Greets,
Dave

That is exactly what I want in some cases, to wipe the entire line. However, in when doing an ignore like that, I would use _FOC_NULL.

If I wanted to get rid of a dynamic where statement, I would want to wipe out that line with FOC_NONE (WHERE &where). Caution is needed here, not fear. But each to his own...

I just tried J's code

TABLE FILE CAR
PRINT 
     &RP_FL04P01_Detail4.(<BODYTYPE,CAR.BODY.BODYTYPE>,<SEATS,CAR.BODY.SEATS>,<DEALER_COST,CAR.BODY.DEALER_COST>,<RETAIL_COST,CAR.BODY.RETAIL_COST>,<SALES,CAR.BODY.SALES>,<None,_FOC_NULL>).Please select detail field(s).
     &RP_FL05P02_Detail5.(<BODYTYPE,CAR.BODY.BODYTYPE>,<SEATS,CAR.BODY.SEATS>,<DEALER_COST,CAR.BODY.DEALER_COST>,<RETAIL_COST,CAR.BODY.RETAIL_COST>,<SALES,CAR.BODY.SALES>,<None,_FOC_NULL>).Please select detail field(s).
     &RP_FL06P03_Detail6.(<BODYTYPE,CAR.BODY.BODYTYPE>,<SEATS,CAR.BODY.SEATS>,<DEALER_COST,CAR.BODY.DEALER_COST>,<RETAIL_COST,CAR.BODY.RETAIL_COST>,<SALES,CAR.BODY.SALES>,<None,_FOC_NULL>).Please select detail field(s).
     &RP_FL07P04_Detail7.(<BODYTYPE,CAR.BODY.BODYTYPE>,<SEATS,CAR.BODY.SEATS>,<DEALER_COST,CAR.BODY.DEALER_COST>,<RETAIL_COST,CAR.BODY.RETAIL_COST>,<SALES,CAR.BODY.SALES>,<None,_FOC_NULL>).Please select detail field(s).
     &RP_FL08P05_Detail8.(<BODYTYPE,CAR.BODY.BODYTYPE>,<SEATS,CAR.BODY.SEATS>,<DEALER_COST,CAR.BODY.DEALER_COST>,<RETAIL_COST,CAR.BODY.RETAIL_COST>,<SALES,CAR.BODY.SALES>,<None,_FOC_NULL>).Please select detail field(s).
BY  &RP_FN01B_Sort1.(BY(<COUNTRY,CAR.ORIGIN.COUNTRY>,<CAR,CAR.COMP.CAR>,<MODEL,CAR.CARREC.MODEL>)).Please select sort field(s).
ON TABLE SET ONFIELD IGNORE
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END

Then I realized it was for a v7.+ version. We are on 7.6.11.
However, now that I had come that far, I persisted and came up with code that will work in 7.6.11

TABLE FILE CAR
PRINT 
     &RP_FL04P01_Detail4.(<BODYTYPE,CAR.BODY.BODYTYPE>,<SEATS,CAR.BODY.SEATS>,<DEALER_COST,CAR.BODY.DEALER_COST>,<RETAIL_COST,CAR.BODY.RETAIL_COST>,<SALES,CAR.BODY.SALES>,<None,CAR.BODY.RETAIL_COST NOPRINT>).Please select detail field(s).
     &RP_FL04P01_Detail5.(<BODYTYPE,CAR.BODY.BODYTYPE>,<SEATS,CAR.BODY.SEATS>,<DEALER_COST,CAR.BODY.DEALER_COST>,<RETAIL_COST,CAR.BODY.RETAIL_COST>,<SALES,CAR.BODY.SALES>,<None,CAR.BODY.RETAIL_COST NOPRINT>).Please select detail field(s).
     &RP_FL04P01_Detail6.(<BODYTYPE,CAR.BODY.BODYTYPE>,<SEATS,CAR.BODY.SEATS>,<DEALER_COST,CAR.BODY.DEALER_COST>,<RETAIL_COST,CAR.BODY.RETAIL_COST>,<SALES,CAR.BODY.SALES>,<None,CAR.BODY.RETAIL_COST NOPRINT>).Please select detail field(s).
     &RP_FL04P01_Detail7.(<BODYTYPE,CAR.BODY.BODYTYPE>,<SEATS,CAR.BODY.SEATS>,<DEALER_COST,CAR.BODY.DEALER_COST>,<RETAIL_COST,CAR.BODY.RETAIL_COST>,<SALES,CAR.BODY.SALES>,<None,CAR.BODY.RETAIL_COST NOPRINT>).Please select detail field(s).
     &RP_FL04P01_Detail8.(<BODYTYPE,CAR.BODY.BODYTYPE>,<SEATS,CAR.BODY.SEATS>,<DEALER_COST,CAR.BODY.DEALER_COST>,<RETAIL_COST,CAR.BODY.RETAIL_COST>,<SALES,CAR.BODY.SALES>,<None,CAR.BODY.RETAIL_COST NOPRINT>).Please select detail field(s).
BY   &RP_FN01B_Sort1.(<COUNTRY,CAR.ORIGIN.COUNTRY>,<CAR,CAR.COMP.CAR>,<MODEL,CAR.CARREC.MODEL>).Please select sort field(s).
-*AFTER 7.0   ON TABLE SET ONFIELD IGNORE
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END

All it does is print CAR NOPRINT. Same as 'None'! If you are creating a hold file just be sure to use the ON TABLE SET HOLDLIST PRINTONLY

Lol... I didn't post that code so you could copy and paste, but rather you could see how it is generated and what it does.

That code allows you to let the user select which columns they want from an html page and the order they want (if you choose the multiple columns per variable option). This is much faster via the GUI even if you were copying and pasting. I understand that the GUI isn't the "ultimate solution". It is not even a good practice to code only via the GUI... but it isn't a good practice to hand code exclusively either. I spend more time hand-coding than naught but that is probably because I do what I can via the GUI quickly and hand code other things that the GUI doesn't handle as well (copying and pasting, dialogue manager, etc.). If code isn't GUI compatible (or at least close) then I eliminate that possible all together and I have to hand code. I don't have a choice.