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     dollar sign in the WHERE clause

Read-Only Read-Only Topic
Go
Search
Notify
Tools
dollar sign in the WHERE clause
 Login/Join
 
GreenspanDan
Silver Member
posted
hello,

i need to filter on a value with a (literally) a dollar sign in it. however, when i put in the following:

WHERE field1 EQ '123$456'

it seems to interpret the dollar sign as a wildcard, and it changes the "EQ" to "LIKE" when it sends the SQL to the datasource. how can i get WebFOCUS to not treat the $ as a wildcard?
 
Posts: 41 | Registered: August 05, 2005Report This Post
Kamesh
Master
posted Hide Post
try this,

WHERE field1 EQ '123\$456' ESCAPE '$'


WFConsultant

WF 8105M on Win7/Tomcat
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
smiths
Platinum Member
posted Hide Post
Dan,

Can you use CONTAINS instead of EQ?

Sean


------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
 
Posts: 210 | Location: Ottawa | Registered: November 03, 2005Report This Post
GreenspanDan
Silver Member
posted Hide Post
sean,
no we can't use contains. our data source only allows EQ.

Kamesh,
thanks -- i'll try that.


---------------------
WebFOCUS 7.6
 
Posts: 41 | Registered: August 05, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
Very Interesting:

For a DB2 table, this is what happens:

WebFOCUS - WHERE PERIOD_TYPE EQ 'D$Y'
SQL translation - WHERE (T1."PERIOD_TYPE" LIKE 'D_Y ')

WebFOCUS - WHERE PERIOD_TYPE LIKE 'D$Y'
SQL translation - WHERE (T1."PERIOD_TYPE" = 'D$Y')

Depending on your RDBMS, you may be able to trick WebFOCUS into the correct SQL translation.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
I can confirm this happens for MS SQL Server as well, so it will most likely work for your RDBMS.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
GreenspanDan
Silver Member
posted Hide Post
soooo what you're saying is if i put "equals" in the FOCUS, it puts "like" in the SQL, and if i put a "like" in the FOCUS, it puts "equals" in the SQL.

well, that makes sense.


---------------------
WebFOCUS 7.6
 
Posts: 41 | Registered: August 05, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
Yup.

And it's happening in WF 5.3.3 and 7.6 and I'm guessing all versions in between.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Kamesh
Master
posted Hide Post
Sorry ESCAPE wont work with EQ statement. It works only with LIKE.


WFConsultant

WF 8105M on Win7/Tomcat
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
Brian Suter
Platinum Member
posted Hide Post
THis is the fall out of $ being the IF wildcard which then percolated into WHERE.
The syntax:
IF fld EXACTLY EQ 'D$Y'
Will make sure there is no wildcarding. I Verified on sql2005 that the SQL generated has:
WHERE (T1."Title" = 'M$$')


Brian Suter
VP WebFOCUS Product Development
 
Posts: 200 | Location: NYC | Registered: January 02, 2007Report This Post
hammo1j
Master
posted Hide Post
I think that EXACTLY must be an undocumented feature - I can't find it anywhere.

Thanks Brian


Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Brian Suter
Platinum Member
posted Hide Post
The doc is obscure... But I did search on EXECTLY EQ on techsupport and found that there is a public case that mentions it.


Brian Suter
VP WebFOCUS Product Development
 
Posts: 200 | Location: NYC | Registered: January 02, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
soooo if there's an EXACTLY, then there must be a SOMEWHAT.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
GreenspanDan
Silver Member
posted Hide Post
i can confirm that EXACTLY EQ syntax works. thanks everyone!


---------------------
WebFOCUS 7.6
 
Posts: 41 | Registered: August 05, 2005Report 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     dollar sign in the WHERE clause

Copyright © 1996-2020 Information Builders