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.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Parsing a user input string into a WHERE statement

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Parsing a user input string into a WHERE statement
 Login/Join
 
manglum
Platinum Member
posted
I'm trying to figure out how to give my end user the ability to enter a list of values in an input box and have those parsed out into the elements of a WHERE statement. So the example I'm thinking of would look something like this:

HTML user input box values: VALUE1,VALUE2,VALUE3,VALUE4,etc
The values dont have to be comma separated if thats an issue. They can be separated just by a blank space if need be: HTML user input box values: VALUE1 VALUE2 VALUE3 VALUE4 etc

I would probably think there should be a cap on the number of unique values at say, 20 just so things didnt get out of hand with string length.

The procedure using the values would look something like:

Two thing to note (at least in my current use case) is that the values entered would need to be integers and the other is that I'm building using the OR operator only. So no complex OR/AND mixes.


  TABLE FILE CAR
SUM
     SALES
BY  LOWEST COUNTRY
BY  LOWEST SEATS
WHERE SEATS EQ VALUE1 OR VALUE2 OR VALUE3 OR VALUE4 ;
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


Or perhaps you can use the IN operator? Like this.....
  TABLE FILE CAR
SUM
     SALES
BY  LOWEST COUNTRY
BY  LOWEST SEATS
WHERE SEATS IN(VALUE1, VALUE2, VALUE3, VALUE4) ;
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



So as stated above, my question is how to build the WHERE statement using the input values?

Thank in advance for your help!

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


WebFOCUS 8
Windows, All Outputs
 
Posts: 111 | Registered: July 23, 2015Report This Post
MartinY
Virtuoso
posted Hide Post
Just question for you (not meaning that what you're requesting is not possible) : Why not selecting from a double list box ?

Is this because the user input value are unknown or have too many ?

Reason why I'm asking it's just because you may then have to play around with what the user will have entered :
- Special characters : coma, semicolon or none ?
- Spaces : one or multiple spaces ?
- The length : as you mentioned, you may have to limit the user entry. Meaning that you will need to count the number of value.

Then you will have to program proper function to analyze and format the input string to then build a valid WHERE clause.
Maybe too much work if you can have a double list box per example.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
manglum
Platinum Member
posted Hide Post
Martin, you are correct about too many values. There are 15,000+ possible values. No way to manage that conveniently through a multi-select dropbox.


WebFOCUS 8
Windows, All Outputs
 
Posts: 111 | Registered: July 23, 2015Report This Post
BabakNYC
Virtuoso
posted Hide Post
This might be a good starting point but I'm with MartinY on this one.

This code will substitute , for OR. Try it with 2,4
 
-SET &VALUE=STRREP(&RESP.LENGTH,&RESP,1,',',4,' OR ',50,'A50');
TABLE FILE CAR
SUM
SALES
BY COUNTRY
BY CAR 
BY SEATS
WHERE SEATS EQ &VALUE;
END


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
pav
Platinum Member
posted Hide Post
From my understanding,

This is what are you looking for?

-DEFAULT &FILTER='ENGLAND,JAPAN';

-SET &FILTER=STRREP(&FILTER.LENGTH,&FILTER,1,',',5,''',''',1000,'A1000');
-SET &FILTER=TRUNCATE(&FILTER);
-TYPE &FILTER

TABLE FILE CAR
SUM
SALES
BY LOWEST COUNTRY
BY LOWEST SEATS
WHERE COUNTRY IN ('&FILTER.EVAL')
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
-EXIT
 
Posts: 109 | Registered: February 02, 2016Report This Post
Dave
Master
posted Hide Post
Hey !

out-of-the-box functionality.

Just use a TEXTAREA as input ( not inputbox ).

The user can either use , or ; or linefeeds to seperate the data.

HTML will return the entered values like
1 OR 2 OR 5 OR 6

or, if needed ( quotes on )

'1' OR '2' OR '5' OR '6'.

Good luck.


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
manglum
Platinum Member
posted Hide Post
thank you all for the help! I ended up using the IN() operator in my where statement so the user just has to enter a comma separated list into the input box and it works great.


WebFOCUS 8
Windows, All Outputs
 
Posts: 111 | Registered: July 23, 2015Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Parsing a user input string into a WHERE statement

Copyright © 1996-2020 Information Builders