I am trying to perform a search function and I want to be able to return all matching records regardless of the case. I have read posts that suggest the only way to do this is to create a define field that is in upper case and convert the input to match.

I have performed this function in previous reports and it has worked well. My issue is that now this process is significantly slowing up the retrieval process making it too slow to do.

I have also noticed that executing a query within my sql database that matches the one that WebFOCUS generates does return all records ignoring case. However when simply executing the code it is stripping out any that do not match case.

I have tried multiple statements including using the "LIKE", "CONTAINS" and "EQ" comparisons.

Any thoughts as to how I can get the WHERE Statement to pull back all results that SQL returns outside of using a passthrough query, which I know will work, but is not ideal in my particular situation.

Thanks

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

What database?

SQL 2000

That sounds a little bizzare. One of the things I like about MSSQL is that it will ignore case when doing a CONTAINS in WebFOCUS.

Is the field you are testing against defined as an Axx or AxxV? If the V is there, remove it and try again.

I am still receiving the same results...

Getting the same results here against our SQL database.

Found this ref on techsupport:
http://techsupport.informationbuilders.com/sps/31512531.html

Basically it says where tests in TABLE FILE are and have always been case sensitive and that your alternatives are to use SQL Passthru or the UPCASE or LOCASE functions.

One other suggestion might be to do something on the SQL side to get that column (or a variation of that column) into all uppercase or lowercase and then adjust your where to do the same? Basically if you can avoid the function calls to be run for every row, your performance will be much better. Otherwise it sounds like you're stuck using a define or those functions.


It would be really really nice if WF allowed for a little better passthru of common functions -- or better yet, actually send the SQL through like it says it's going to. The reason I say this is that in my test, I generated the SQL trace and looked at it. The sql was fine -- if I ran it directly, it still worked. So something is happening in the handling of the SQL at the reporting server level that is not obvious...

Good luck.

---

Production: 7.6.6 WF Server  <=>  7.6.6 WF Client  <=>  7.6.6 Dev Studio
Testing: <none>
Using MRE & BID.  Connected to MS SQL Server 2005
Output Types: HTML, Excel, PDF

Thanks Trav, that is what I was afraid of...

I have one more hook in the water and if anything comes of it I will let you know..

Thanks

gweller,

I am sure that there are MS SQL settings that ignore the case of data within a fetch but can't recall what they might be at present. I used them at a previous client for exactly the reason you have. Worked like a charm.

If I remember or locate them I will let you know.

T

Tony...

Just to clarify, the SQL selection is not the problem. SQL is passing back the requested result set. It does seem as though upon retrieval of the data to the reporting server that the "WHERE surname EQ 'Smith'" actually reexecutes and strips out based on case.

Just as an FYI to those interested, it seems as though this is standard functionality within WebFocus. I did receive somewhat of a workaround that I hadnt previously thought of which is this...

  -DEFAULT &LASTNAME='Smith'
-SET &SN_LENGTH=&LASTNAME.LENGTH;
-SET &LASTNAME_LC = IF &LASTNAME EQ 'FOC_NONE' THEN 'FOC_NONE' ELSE LOCASE(&SN_LENGTH,&LASTNAME,'A&SN_LENGTH.EVAL');
-SET &LASTNAME_UC=UPCASE(&SN_LENGTH,&LASTNAME,'A&SN_LENGTH.EVAL');

TABLE FILE NAMES
PRINT * 
WHERE LASTNAME GE '&LASTNAME_UC';
WHERE LASTNAME LE '&LASTNAME_LC';
END

This actually works quite well and will use the table indexes that are defined on your table. The other woraround was to avoid the WHERE clause alltogether and simply use a passthrough query, which was not optimal for my purposes.

Thanks for your help.

gweller

Sorry to disappoint but if you have data

Jones
Smith
SNIDE
Umberto

Your query will return Smith, SNIDE and Umberto.

The only way to make the query work is to UPCASE both operands of the relational operators EQ or LIKE.

eg WHERE UPCASE(200,LASTNAME,'A200') EQ '&LASTNAME_UC'

Unfortunately since UPCASE is not a function translated into SQL by wf this could be very time consuming since the where would have to be done by webfocus.

A speedy solution could be found to this on the database side in defining a view of the data where the appropriate uppercase function was applied in the new column of the view. For static data a wf xfocus version of the data could be constructed with the names uppercased.

IBI could improve wf.

1. Translate UPCASE to the dbms equivalent and pass the query - not hard.

2. Have a suffix for alpha operators NCS - not case sensitive so that your query would be

WHERE LASTNAME EQ_NCS '&LASTNAME'

and issue the appropriate SQL translation. Harder but neater!

hammo1j,

Thanks for the feedback, however after doing some initial testing it seems as though I have no issues with returning incorrect data. Perhaps it is my combination of firstname and lastname that I must search by that is helping to eliminate any false positives.

I will do some further testing and report back..

Thanks

Use of the LCWORD function may also help to eliminate false positives. You could say GE uppercase value and LE mixed case value

OK so to wrap this up....

We have placed a request with the fine folks at Information Builders to look at a future enhancement that would make this whole mess a lot easier. They have responded by saying that a fix/enhancement is currently in the works, so that is some good news.

After doing some more investigation I can confidently say that the above example works like a charm. No issues. I am only receiving back the data that I am expecting.

The issue with specifying UPCASE, LOCASE and/or LCWORD in the select or where statements is that the data manipulation occurs within the reporting server and not on the DBMS as hammo1j mentions.

The next suggestion of a view being defined again would be a good choice if we had that level of control. Unfortunatly creation and rollout of a view within our business unit could take some time.

Therefore the WHERE clause selecting data based on a range from lower case to upper case makes the most sense as it is executed on the DBMS level and utilizes indexes that have already been set up.

Again thanks for all your help and input. When the case with IBI is resolved I will update this forum.

Just an update,

I received word from the folks at IBI that they are working on a fix for this that will be implemented within the 7.7 release.

Good News!!!

gweller,

I think I would hold off until you've beta tested it for IB

T

I've discovered the same limitation... there really should be a SET command that turns off web focus re-evaluating the WHERE in a case sensitive manner.

gweller - you mentioned that 7.7 is going to address this? Do you know if they did, how it was implemented?

Byrnsy,

I haven't had the opportunity to upgrade to 7.7, so I can't comment on the current status. (Stuck on 767)

quote:

there really should be a SET command that turns off web focus re-evaluating the WHERE in a case sensitive manner

I can see your point, and it would be handy sometimes. But the fact that WF can access multiple databases and DB types (in a single request) would still convince me that it should be handled individually by the DBMS, not as a blanket setting from WF.