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     WF - How Do They Do It? Case-sensitive WHERE statement on SQL Server table

Read-Only Read-Only Topic
Go
Search
Notify
Tools
WF - How Do They Do It? Case-sensitive WHERE statement on SQL Server table
 Login/Join
 
Francis Mariani
Expert
posted
Fex:

TABLE FILE MSEXT_CONTACTVIEW2
PRINT
ADDRESS1_STATEORPROVINCE
BY HIGHEST ADDRESS1_STATEORPROVINCE NOPRINT
WHERE 
ADDRESS1_STATEORPROVINCE EQ 'Bc'
END

returns one row:

Bc


This SQL:

SELECT  Address1_StateOrProvince
FROM     ContactView2 T1
WHERE  (Address1_StateOrProvince = 'Bc')
ORDER BY Address1_StateOrProvince DESC

Returns these values:

BC
BC
BC


From what I've read on the Interwebs, by default, SQL tables in MS SQL Server are case insensitive - the data is stored in mixed case, but sorting and selection ignores the case. To do a case sensitive search, several convoluted solutions are available, including the use of the COLLATE function and the use of CAST to convert the column to varbinary.

When reading the same table with WebFOCUS, a case-sensitive search works correctly.

How does WebFOCUS do this?


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
Anonymouse
Silver Member
posted Hide Post
Maybe it's magic!

Have you tried activating the TRACE facility and looking at the SQL that WebFOCUS generates?

Might there be parameters in the data adapter that default to case-sensitive?

I have no answers here. Just possible things to investigate. Good luck!


Jeff
WebFOCUS 8.0.09, Unix-Win-z/OS
FOCUS 7.3.1 on z/OS
 
Posts: 34 | Location: Minneapolis, MN | Registered: June 10, 2003Report This Post
hammo1j
Master
posted Hide Post
Francis

Probably method 2 of this

http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm

It does not show on the trace though. Obviously, like any magician, Gerry want to keep this one secret!

Goes to show how much SQL dialects vary tho


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
Tony A
Expert
posted Hide Post
quote:
Maybe it's magic
No, no, Jeff, Magic is a completely different language Wink

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
Francis Mariani
Expert
posted Hide Post
I am the Duke of Tracing so I definitely tried the SQL traces - nothing in the trace other than the regular SELECT statement.

That was precisely the link I was referring to - pretty convoluted methods to perform a case sensitive search.

Magic uses iBOLTs to connect things together.


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
cburtt
Platinum Member
posted Hide Post
To those ignorant of the technology, the technology IS magic!


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
Tony A
Expert
posted Hide Post
.. and there I was thinking that humour was completely lost on some Razzer

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
Leah
Virtuoso
posted Hide Post
I'd venture a guess it is your by highest as in ASCII upper case collates differently than lower case.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Francis Mariani
Expert
posted Hide Post
The results of the SQL trace when running the WebFOCUS request:

 
(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
(FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
SELECT T1."Address1_StateOrProvince" FROM
GGOF_CRMEXT.dbo.ContactView2 T1 WHERE
(T1."Address1_StateOrProvince" = 'BC') ORDER BY
T1."Address1_StateOrProvince" DESC ;


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
Leah
Virtuoso
posted Hide Post
On the SQL have you tried adding a GROUP BY


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report 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     WF - How Do They Do It? Case-sensitive WHERE statement on SQL Server table

Copyright © 1996-2020 Information Builders