Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
Where Clause
 Login/Join
 
Silver Member
posted
I have a report requirment that will allow the user to key in company, customer and customer name and all of them are not required field. When the user selected a company then all customers under the selected company will be included in the report and when the user select or key in a customer number then that particular customer will be in the report and when the use keys in a part of the customer name(ex. WEB) then all customer with "WEB" in the customer name will be included in the report.

I tried the solution below that I found here in focal point but it doesnt work.

1. Solution 1
-IF &ID EQ '' GOTO :NO_ID
WHERE ITEM_ID EQ &ID
:NO_ID

-IF &COLOR EQ '' GOTO :NO_COLOR
WHERE COLOR EQ &COLOR
:NO_COLOR

-IF &SIZE EQ '' GOTO :NO_SIZE
WHERE SIZE EQ &SIZE
:NO_SIZE

2. Solution2
You will need to write some DM code for this.

It could look something like this:
-IF &ID EQ '' GOTO :NO_ID
WHERE ITEM_ID EQ &ID
:NO_ID

-IF &COLOR EQ '' GOTO :NO_COLOR
WHERE COLOR EQ &COLOR
:NO_COLOR

-IF &SIZE EQ '' GOTO :NO_SIZE
WHERE SIZE EQ &SIZE
:NO_SIZE


thanks a lot.
Noel


WebFocus 762 AS400 / DB2
 
Posts: 34 | Location: Winnipeg, MB | Registered: January 23, 2008Report This Post
Virtuoso
posted Hide Post
When you say it doesn't work...what doesnt work?


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Expert
posted Hide Post
In addition to what Prarie said, what do you get when you -TYPE your variables after using the launch page to run your fex?

For the partial search, you are going to have to append a wildcard to the string selected by the user in order to find everything that starts with WEB.

LIKE field EQ 'WEB%'


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Guru
posted Hide Post
Are you wanting to allow the user to limit the output by choosing all some or none of the selection criteria? If so, here is one way I do it.

-SET &COUNTRY  = 'ALL';
-*  ALL
-*  ENGLAND 
-*  FRANCE 
-*  ITALY 
-*  JAPAN 
-*  W GERMANY 
-SET &CAR      = 'ALL';
-*  ALL
-*  ALFA ROMEO 
-*  AUDI 
-*  BMW 
-*  DATSUN 
-*  JAGUAR 
-*  JENSEN 
-*  MASERATI 
-*  PEUGEOT 
-*  TOYOTA 
-*  TRIUMPH 
-SET &BODYTYPE = 'CONVERTIBLE';
-*  ALL
-*  CONVERTIBLE 
-*  COUPE 
-*  HARDTOP 
-*  ROADSTER 
-*  SEDAN 
-SET &COST     = 10000;
-*  0 => ALL
-* VARIABLE
-*
-SET &CNTRY      = IF &COUNTRY = ALL THEN 'A'' OR ''E'' OR ''I'' OR ''O'' OR ''U' ELSE &COUNTRY;
-SET &CCAR       = IF &CAR = ALL THEN 'A'' OR ''E'' OR ''I'' OR ''O'' OR ''U' ELSE &CAR;
-SET &CBODYTYPE  = IF &BODYTYPE = ALL THEN 'A'' OR ''E'' OR ''I'' OR ''O'' OR ''U' ELSE &BODYTYPE;
-SET &CCOST      = IF &COST = 0 THEN 999999999 ELSE &COST;
-*
SET BYDISPLAY = ON
-*
TABLE FILE CAR
PRINT RETAIL_COST
BY COUNTRY
BY CAR
BY BODYTYPE
WHERE COUNTRY CONTAINS '&CNTRY'
WHERE CAR CONTAINS '&CCAR'
WHERE BODYTYPE CONTAINS '&CBODYTYPE'
WHERE RETAIL_COST LE &CCOST
END


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
Expert
posted Hide Post
quote:
I have a report requirment that will allow the user to key in company, customer and customer name and all of them are not required field. When the user selected a company then all customers under the selected company will be included in the report and when the user select or key in a customer number then that particular customer will be in the report and when the use keys in a part of the customer name(ex. WEB) then all customer with "WEB" in the customer name will be included in the report.


This may help:


-DEFAULT &COMPANY = ' ', &CUST_NAME = ' ', &CUST_NUMB = ' '
-SET &CUST_NAME = IF &CUST_NAME EQ ' ' THEN ' ' ELSE 'WHERE CUSTOMER_NAME LIKE %' || '&CUST_NAME.EVAL' || '%;';
-SET &COMPANY   = IF &COMPANY   EQ ' ' THEN ' ' ELSE 'WHERE COMPANY EQ ' | '&COMPANY.EVAL' || ';';
-SET &CUST_NUMB = IF &CUST_NUMB EQ ' ' THEN ' ' ELSE 'WHERE CUSTOMER_NO EQ ' | '&CUST_NUMB.EVAL' || ';';

TABLE FILE WHATEVER
SUM
   COLUMN_NAMES
  BY COLUMN_NAMES
&CUST_NAME
&COMPANY
&CUST_NUMB

ETC...



If the customer number is numeric, remove the quotes.

Have fun!

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Guru
posted Hide Post
Tom,

You're a peach!

I was in the midst of writing a report where the user could choose a state or no state (for all) when I read your reply.

I made a slight change to your example which simplified my code immensely.

Thank-you, Thank-you, Thank-you!!!

-SET &INPS  = 'ALL';
-SET &STATE = IF &INPS EQ 'ALL' THEN '' ELSE 'WHERE AGSTE EQ ' | ''&INPS.EVAL''';';
 


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
Expert
posted Hide Post
Hey Glenda,

Welcome! Welcome! Welcome!

A little modification:

I would use -DEFAULT instead of -SET; it will "ALWAYS" be ALL with the -SET.


That is what this forum is all about!!

Have a great weekend...Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Silver Member
posted Hide Post
Tom,

I tried your solution but now I cannot edit my procedure using developer studio tool. The error message is "Error parsing report request".

Thank you all for the help!


WebFocus 762 AS400 / DB2
 
Posts: 34 | Location: Winnipeg, MB | Registered: January 23, 2008Report This Post
Expert
posted Hide Post
Noel,

No, once you add Dialogue Manager outside of the GUI, it balks. That is why I don't use it, ever.

You can edit the code by right-clicking on the fex and select EDIT IN TEXT EDITOR...

I'm not a GUI person, so, I can't really assist competently, BUT, I know the GUI allows DM commands:

Left-click the diamond and DM is at the bottom; maybe you can place them in the proper order so the GUI likes it (doubt it!)

Good Luck

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Virtuoso
posted Hide Post
I would use the code FOC_NONE

you get this
-DEFAULT &CNTRY = FOC_NONE; 
-DEFAULT &CAR = FOC_NONE; 
-DEFAULT &BODYTYPE = FOC_NONE; 
-* SET BYDISPLAY = ON 
-* 
TABLE FILE CAR 
PRINT RETAIL_COST 
BY COUNTRY 
BY CAR 
BY BODYTYPE 
WHERE COUNTRY EQ '&CNTRY' 
WHERE CAR EQ '&CAR' 
WHERE BODYTYPE EQ '&BODYTYPE' 
END


Now the user is prompted for the country, car and bodytype.
If he leaves the FOC_NONE the WHERE statement is omitted and the result will show all the countries.




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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
quote:
once you add Dialogue Manager outside of the GUI, it balks


You can add DM code OUTSIDE of a table request and DevStudio will handle it OK (for the most part.) However, by placing DM command somwhere between the TABLE FILE and END, DevStudio will no longer parse your request. You're on your own, which is where you were to start with, so I would forget about the DevStudio from this point forward.

Frank's suggestion works great AS LONG AS each WHERE statement it on its OWN line. Otherwise things get ignored that you don't want. Additionally, it would not handle the LIKE functionality requested by Noel.

I frequently use Tom's technique of pre-manufacturing all of my WHERE statements based on parameters used and then use the &parmname.EVAL in place of the WHERE.

In the original question of the posted code "not working" remember that any DM -IF statements need a ; at the end.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Expert
posted Hide Post
All,

FOC_NONE is NOT the end-all of WHERE;
there are many, many nuances/caveats where it is not a functional option.

Additionally, I try to guide people with under 30 posts the benefits of the core language and Dialogue Manager. I am of the opinion that Dialogue Manager is the "MOST" powerful/efficient piece of the core language.

Additionally, if the code is ever tightened, -DEFAULT "NEVER" ends with a semi-colon; WHERE statements "ALWAYS" end with a semi-colon.

It's documented...

One more thang!: the AUTOPROMPTER is not used as much as HTML forms/JavaScript, which is much more efficient, IMO...



Tom

This message has been edited. Last edited by: Tom Flynn,


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Virtuoso
posted Hide Post
Thanks Tom, but your solution does not work with the GUI and that is what many beginning users prefer and Information Builders predicts.

There is no course where users learn the coding.
My code with FOC_NONE works great and without the ";" I sometimes get error warnings.




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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Expert
posted Hide Post
Frank,

There are courses where developers can learn the coding syntax, Basic Reporting (102), Dialogue Manager (125), Intermediate Reporting (126), MATCH File (213), Advanced Reporting (210).

Maybe these courses are not available in the Netherlands but they are available and listed in the course catalog from IBI.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Master
posted Hide Post
quote:
-SET &STATE = IF &INPS EQ 'ALL' THEN '' ELSE 'WHERE AGSTE EQ ' | ''&INPS.EVAL''';';


FYI,
If you are using amphere variables in a computed WHERE statement, it is not necessary to concatenate the pieces together. The above can become:
  
-SET &STATE = IF &INPS EQ 'ALL' THEN '' ELSE 'WHERE AGSTE EQ  ''&INPS.EVAL'';';

It is a whole lot easier to code than trying to match all of the quotes especially if you are comparing to multiple values.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Guru
posted Hide Post
Thanks Pat. Every time I shorten the code, I reduce my error percentage. Every little bit of knowledge helps.


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
Platinum Member
posted Hide Post
This thread reminds me of an issue we had with the AutoPrompt facility: i.e. control of the sequence of the prompts. By default the fields are prompted for in the sequence in which the parsing routines determine that no value is supplied. This is usually the top-to-bottom sequence in which &vars are encountered in the procedure.

We found a 'neat' way around this by mentioning them in "dummy" -SET statements at the start of the procedure. An extension of this practice leads to a natural inclusion of the old prompting syntax that defines the content of an AutoPrompt select list.

The only problem sometimes found with this approach is that a long selection list can exceed the allowable line length of older WF releases.

Combining this with the capability of 'FOC_NONE' we get a method by which one can control the order of Autoprompting, the selectable values in the prompts, and still allow for 'ALL':

(I know the use of lower/mixed case &vars is frowned upon, but it makes the presentation look nice.)

  
APP PATH IBISAMP
 
-SET &X = '&Country.(ALL,ENGLAND,FRANCE,ITALY).';
-SET &X = '&Car.(ALL,JAGUAR,TOYOTA,DATSUN).';
-SET &X = '&BodyType.(ALL,SEDAN,COUPE).';

-SET &Country =IF &Country  EQ 'ALL' THEN 'FOC_NONE' ELSE &Country;
-SET &Car     =IF &Car      EQ 'ALL' THEN 'FOC_NONE' ELSE &Car;
-SET &BodyType=IF &BodyType EQ 'ALL' THEN 'FOC_NONE' ELSE &BodyType;

TABLE FILE CAR 
PRINT RETAIL_COST 
BY COUNTRY 
BY CAR 
BY BODYTYPE 
WHERE COUNTRY  EQ '&Country' 
WHERE CAR      EQ '&Car' 
WHERE BODYTYPE EQ '&BodyType' 
END


WIN/2K running WF 7.6.4
Development via DevStudio 7.6.4, MRE, TextEditor.
Data is Oracle, MS-SQL.
 
Posts: 154 | Location: NY | Registered: October 27, 2005Report This Post
Guru
posted Hide Post
..and back to your original post...

quote:
-IF &COLOR EQ '' GOTO :NO_COLOR
WHERE COLOR EQ &COLOR
:NO_COLOR


...should be ...

-IF &COLOR EQ '' GOTO :NO_COLOR;
WHERE COLOR EQ &COLOR
-:NO_COLOR  


Dialog Manager labels need a dash in front of them, as well as the semi-colon after the -IF.


ttfn, kp


Access to most releases from R52x, on multiple platforms.
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Report This Post
Expert
posted Hide Post
quote:
the use of lower/mixed case &vars is frowned upon

Really? Depends upon whose standards you follow.
Myself, I'd prefer readable, maintainable code and always use mixed case variable names for my own code, and whatever my "Client at the time" prefers for their code Wink

And Noel, take note of what Karen "the Piipster" has mentioned regarding DM syntax.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders