We have a user input field of a company name that can retrieve multiple records. The source database is case sensitive and the name could be in all caps or proper in the source database. I have not been successful in finding a solution on the forum for this.
In this example the input field name is &CSTNAME. I would like to change whatever the user enters to uppercase and search the database but also change whatever the user enters to proper and search the database. Typically the customer name will be between 5 and 20 char long.

Company name examples: (4)
Chair Company
Big Chair Co
Walt's Chair Repair
MIKES SOFA AND CHAIR MANUFACTURING

The user would type the word Chair and expect to retrieve 4 results.

What is the best way to do this?
Thank you,

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

You can use the UPCASE function to change both user input and database values to upper case, then make the comparison. For example, if your company name field is defined as A35:

-SET &CSTNAMEX = UPCASE(&CSTNAME.LENGTH,&CSTNAME,'A&CSTNAME.LENGTH');

TABLE FILE xxxx
 PRINT COMPANY_NAME
 WHERE UPCASE(35,COMPANY_NAME,'A35') CONTAINS '&CSTNAMEX';
END

This can be done thru Javascript.

quote:

This can be done thru Javascript.

True, but only the part related with converting the user's entry.

If the actual values in the database are also represented in mixed-case, then this is off Javascript' realm. Dan's approach is the way to go in this case.

Geri, the iWay adapter may not be able to translate UPCASE to your particular database "SQL version" so you'll most likely end up querying the whole table when running such a query and WebFOCUS will do the upper case conversion upon reading and comparing to your amper variable.

Even if it did translate to SQL, unless you have some sort of function-based index on UPPER() in your database (such as those available in Oracle) there will be no difference and the database will still need to fully scan the table to find those results.

If your table is "small" this may have no significance but if not your business users may report some "performance issues" which are obviously related to your data layout and not really with WebFOCUS directly.

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

---

Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.

There's also LOCASE2() which converts to proper mixed case. Although my suggestion would be to convert the input into upper case before it's inserted into the database, this is the safest way to insure you don't miss records when retrieving them out of the database.

All,
Currently the report searches several fields for the customer name aside from the basic 'name' field. This includes the 4 Dun and Bradstreet name assigend to each customer and the 2 additional name fields assigend to each customer for longer customer names. This can make the report quite long to use the .length search. As an example, here is what I created for searching only 2 of the 7 name fields. The example of a company named Chair is simple but imagine any company name. Users do not type anything consistently which is the reason for this search report. We pull this from SAP and SAP is case sensitive.

TABLE FILE ZCUSMAST
PRINT
TABLENAMECUSTNMBR
TABLENAMECUSTNAME1
TABLENAMECUSTNAME2
TABLENAMESTREET
TABLENAMECITY
WHERE TABLENAMECUSTNAME1 CONTAINS '&CSTNAME'
OR TABLENAMECUSTNAME2 CONTAINS '&CSTNAME';
ON TABLE HOLD AS CSTZCN01
END

-IF &CSTNAME.LENGTH EQ 3 GOTO NEXT03;
-IF &CSTNAME.LENGTH EQ 4 GOTO NEXT04;
-IF &CSTNAME.LENGTH EQ 5 GOTO NEXT05;
-IF &CSTNAME.LENGTH EQ 6 GOTO NEXT06;
-IF &CSTNAME.LENGTH EQ 7 GOTO NEXT07;
-IF &CSTNAME.LENGTH EQ 8 GOTO NEXT08;
-IF &CSTNAME.LENGTH EQ 9 GOTO NEXT09;
-IF &CSTNAME.LENGTH EQ 10 GOTO NEXT10;
-IF &CSTNAME.LENGTH EQ 11 GOTO NEXT11;
-IF &CSTNAME.LENGTH EQ 12 GOTO NEXT12;
-IF &CSTNAME.LENGTH EQ 13 GOTO NEXT13;
-IF &CSTNAME.LENGTH EQ 14 GOTO NEXT14;
-IF &CSTNAME.LENGTH EQ 15 GOTO NEXT15;
-IF &CSTNAME.LENGTH EQ 16 GOTO NEXT16;
-IF &CSTNAME.LENGTH EQ 17 GOTO NEXT17;
-IF &CSTNAME.LENGTH EQ 18 GOTO NEXT18;
-IF &CSTNAME.LENGTH EQ 19 GOTO NEXT18;
-IF &CSTNAME.LENGTH EQ 20 GOTO NEXT18;
-IF &CSTNAME.LENGTH EQ 21 GOTO NEXT18;
-IF &CSTNAME.LENGTH EQ 22 GOTO NEXT18;
-IF &CSTNAME.LENGTH EQ 23 GOTO NEXT18;
-IF &CSTNAME.LENGTH EQ 24 GOTO NEXT18;
-IF &CSTNAME.LENGTH EQ 25 GOTO NEXT18;

-NEXT03
-SET &CSTNAME = UPCASE(&CSTNAME.LENGTH, &CSTNAME, 'A3');
-GOTO NEXT20;
-NEXT04
-SET &CSTNAME = UPCASE(&CSTNAME.LENGTH, &CSTNAME, 'A4');
-GOTO NEXT20;
-NEXT05
-SET &CSTNAME = UPCASE(&CSTNAME.LENGTH, &CSTNAME, 'A5');
-GOTO NEXT20;
-NEXT06
-SET &CSTNAME = UPCASE(&CSTNAME.LENGTH, &CSTNAME, 'A6');
-GOTO NEXT20;
-NEXT07
-SET &CSTNAME = UPCASE(&CSTNAME.LENGTH, &CSTNAME, 'A7');
-GOTO NEXT20;
-NEXT08
-SET &CSTNAME = UPCASE(&CSTNAME.LENGTH, &CSTNAME, 'A8');
-GOTO NEXT20;
-NEXT09
-SET &CSTNAME = UPCASE(&CSTNAME.LENGTH, &CSTNAME, 'A9');
-GOTO NEXT20;
-NEXT10
-SET &CSTNAME = UPCASE(&CSTNAME.LENGTH, &CSTNAME, 'A10');
-GOTO NEXT20;
-NEXT11
-SET &CSTNAME = UPCASE(&CSTNAME.LENGTH, &CSTNAME, 'A11');
-GOTO NEXT20;
-NEXT12
-SET &CSTNAME = UPCASE(&CSTNAME.LENGTH, &CSTNAME, 'A12');
-GOTO NEXT20;
-NEXT13
-SET &CSTNAME = UPCASE(&CSTNAME.LENGTH, &CSTNAME, 'A13');
-GOTO NEXT20;
-NEXT14
-SET &CSTNAME = UPCASE(&CSTNAME.LENGTH, &CSTNAME, 'A14');
-GOTO NEXT20;
-NEXT15
-SET &CSTNAME = UPCASE(&CSTNAME.LENGTH, &CSTNAME, 'A15');
-GOTO NEXT20;
-NEXT16
-SET &CSTNAME = UPCASE(&CSTNAME.LENGTH, &CSTNAME, 'A16');
-GOTO NEXT20;
-NEXT17
-SET &CSTNAME = UPCASE(&CSTNAME.LENGTH, &CSTNAME, 'A17');
-GOTO NEXT20;
-NEXT18
-SET &CSTNAME = UPCASE(&CSTNAME.LENGTH, &CSTNAME, 'A18');
-GOTO NEXT20;

-NEXT20
TABLE FILE ZCUSMAST
PRINT
TABLENAMECUSTNMBR
TABLENAMECUSTNAME1
TABLENAMECUSTNAME2
TABLENAMESTREET
TABLENAMECITY
WHERE TABLENAMECUSTNAME1 CONTAINS '&CSTNAME'
OR TABLENAMECUSTNAME2 CONTAINS '&CSTNAME';
ON TABLE HOLD AS CSTZCN02
END

-IF &CSTNAME.LENGTH EQ 3 GOTO NEXT23;
-IF &CSTNAME.LENGTH EQ 4 GOTO NEXT24;
-IF &CSTNAME.LENGTH EQ 5 GOTO NEXT25;
-IF &CSTNAME.LENGTH EQ 6 GOTO NEXT26;
-IF &CSTNAME.LENGTH EQ 7 GOTO NEXT27;
-IF &CSTNAME.LENGTH EQ 8 GOTO NEXT28;
-IF &CSTNAME.LENGTH EQ 9 GOTO NEXT29;
-IF &CSTNAME.LENGTH EQ 10 GOTO NEXT30;
-IF &CSTNAME.LENGTH EQ 11 GOTO NEXT31;
-IF &CSTNAME.LENGTH EQ 12 GOTO NEXT32;
-IF &CSTNAME.LENGTH EQ 13 GOTO NEXT33;
-IF &CSTNAME.LENGTH EQ 14 GOTO NEXT34;
-IF &CSTNAME.LENGTH EQ 15 GOTO NEXT35;
-IF &CSTNAME.LENGTH EQ 16 GOTO NEXT36;
-IF &CSTNAME.LENGTH EQ 17 GOTO NEXT37;
-IF &CSTNAME.LENGTH EQ 18 GOTO NEXT38;
-IF &CSTNAME.LENGTH EQ 19 GOTO NEXT38;
-IF &CSTNAME.LENGTH EQ 20 GOTO NEXT38;
-IF &CSTNAME.LENGTH EQ 21 GOTO NEXT38;
-IF &CSTNAME.LENGTH EQ 22 GOTO NEXT38;
-IF &CSTNAME.LENGTH EQ 23 GOTO NEXT38;
-IF &CSTNAME.LENGTH EQ 24 GOTO NEXT38;
-IF &CSTNAME.LENGTH EQ 25 GOTO NEXT38;

-NEXT23
-SET &CSTNAME = LOCASE(&CSTNAME.LENGTH, &CSTNAME, 'A3');
-GOTO NEXT40;
-NEXT24
-SET &CSTNAME = LOCASE(&CSTNAME.LENGTH, &CSTNAME, 'A4');
-GOTO NEXT40;
-NEXT25
-SET &CSTNAME = LOCASE(&CSTNAME.LENGTH, &CSTNAME, 'A5');
-GOTO NEXT40;
-NEXT26
-SET &CSTNAME = LOCASE(&CSTNAME.LENGTH, &CSTNAME, 'A6');
-GOTO NEXT40;
-NEXT27
-SET &CSTNAME = LOCASE(&CSTNAME.LENGTH, &CSTNAME, 'A7');
-GOTO NEXT40;
-NEXT28
-SET &CSTNAME = LOCASE(&CSTNAME.LENGTH, &CSTNAME, 'A8');
-GOTO NEXT40;
-NEXT29
-SET &CSTNAME = LOCASE(&CSTNAME.LENGTH, &CSTNAME, 'A9');
-GOTO NEXT40;
-NEXT30
-SET &CSTNAME = LOCASE(&CSTNAME.LENGTH, &CSTNAME, 'A10');
-GOTO NEXT40;
-NEXT31
-SET &CSTNAME = LOCASE(&CSTNAME.LENGTH, &CSTNAME, 'A11');
-GOTO NEXT40;
-NEXT32
-SET &CSTNAME = LOCASE(&CSTNAME.LENGTH, &CSTNAME, 'A12');
-GOTO NEXT40;
-NEXT33
-SET &CSTNAME = LOCASE(&CSTNAME.LENGTH, &CSTNAME, 'A13');
-GOTO NEXT40;
-NEXT34
-SET &CSTNAME = LOCASE(&CSTNAME.LENGTH, &CSTNAME, 'A14');
-GOTO NEXT40;
-NEXT35
-SET &CSTNAME = LOCASE(&CSTNAME.LENGTH, &CSTNAME, 'A15');
-GOTO NEXT40;
-NEXT36
-SET &CSTNAME = LOCASE(&CSTNAME.LENGTH, &CSTNAME, 'A16');
-GOTO NEXT40;
-NEXT37
-SET &CSTNAME = LOCASE(&CSTNAME.LENGTH, &CSTNAME, 'A17');
-GOTO NEXT40;
-NEXT38
-SET &CSTNAME = LOCASE(&CSTNAME.LENGTH, &CSTNAME, 'A18');
-GOTO NEXT40;

-NEXT40

TABLE FILE ZCUSMAST
PRINT
TABLENAMECUSTNMBR
TABLENAMECUSTNAME1
TABLENAMECUSTNAME2
TABLENAMESTREET
TABLENAMECITY
WHERE TABLENAMECUSTNAME1 CONTAINS '&CSTNAME'
OR TABLENAMECUSTNAME2 CONTAINS '&CSTNAME';
ON TABLE HOLD AS CSTZCN03
END
-************
-IF &CSTNAME.LENGTH EQ 3 GOTO NEXT53;
-IF &CSTNAME.LENGTH EQ 4 GOTO NEXT54;
-IF &CSTNAME.LENGTH EQ 5 GOTO NEXT55;
-IF &CSTNAME.LENGTH EQ 6 GOTO NEXT56;
-IF &CSTNAME.LENGTH EQ 7 GOTO NEXT57;
-IF &CSTNAME.LENGTH EQ 8 GOTO NEXT58;
-IF &CSTNAME.LENGTH EQ 9 GOTO NEXT59;
-IF &CSTNAME.LENGTH EQ 10 GOTO NEXT60;
-IF &CSTNAME.LENGTH EQ 11 GOTO NEXT61;
-IF &CSTNAME.LENGTH EQ 12 GOTO NEXT62;
-IF &CSTNAME.LENGTH EQ 13 GOTO NEXT63;
-IF &CSTNAME.LENGTH EQ 14 GOTO NEXT64;
-IF &CSTNAME.LENGTH EQ 15 GOTO NEXT65;
-IF &CSTNAME.LENGTH EQ 16 GOTO NEXT66;
-IF &CSTNAME.LENGTH EQ 17 GOTO NEXT67;
-IF &CSTNAME.LENGTH EQ 18 GOTO NEXT68;
-IF &CSTNAME.LENGTH EQ 19 GOTO NEXT68;
-IF &CSTNAME.LENGTH EQ 20 GOTO NEXT68;
-IF &CSTNAME.LENGTH EQ 21 GOTO NEXT68;
-IF &CSTNAME.LENGTH EQ 22 GOTO NEXT68;
-IF &CSTNAME.LENGTH EQ 23 GOTO NEXT68;
-IF &CSTNAME.LENGTH EQ 24 GOTO NEXT68;
-IF &CSTNAME.LENGTH EQ 25 GOTO NEXT68;

-NEXT53
-SET &CSTNAME = LCWORD(&CSTNAME.LENGTH, &CSTNAME, 'A3');
-GOTO NEXT70;
-NEXT54
-SET &CSTNAME = LCWORD(&CSTNAME.LENGTH, &CSTNAME, 'A4');
-GOTO NEXT70;
-NEXT55
-SET &CSTNAME = LCWORD(&CSTNAME.LENGTH, &CSTNAME, 'A5');
-GOTO NEXT70;
-NEXT56
-SET &CSTNAME = LCWORD(&CSTNAME.LENGTH, &CSTNAME, 'A6');
-GOTO NEXT70;
-NEXT57
-SET &CSTNAME = LCWORD(&CSTNAME.LENGTH, &CSTNAME, 'A7');
-GOTO NEXT70;
-NEXT58
-SET &CSTNAME = LCWORD(&CSTNAME.LENGTH, &CSTNAME, 'A8');
-GOTO NEXT70;
-NEXT59
-SET &CSTNAME = LCWORD(&CSTNAME.LENGTH, &CSTNAME, 'A9');
-GOTO NEXT70;
-NEXT60
-SET &CSTNAME = LCWORD(&CSTNAME.LENGTH, &CSTNAME, 'A10');
-GOTO NEXT70;
-NEXT61
-SET &CSTNAME = LCWORD(&CSTNAME.LENGTH, &CSTNAME, 'A11');
-GOTO NEXT70;
-NEXT62
-SET &CSTNAME = LCWORD(&CSTNAME.LENGTH, &CSTNAME, 'A12');
-GOTO NEXT70;
-NEXT63
-SET &CSTNAME = LCWORD(&CSTNAME.LENGTH, &CSTNAME, 'A13');
-GOTO NEXT70;
-NEXT64
-SET &CSTNAME = LCWORD(&CSTNAME.LENGTH, &CSTNAME, 'A14');
-GOTO NEXT70;
-NEXT65
-SET &CSTNAME = LCWORD(&CSTNAME.LENGTH, &CSTNAME, 'A15');
-GOTO NEXT70;
-NEXT66
-SET &CSTNAME = LCWORD(&CSTNAME.LENGTH, &CSTNAME, 'A16');
-GOTO NEXT70;
-NEXT67
-SET &CSTNAME = LCWORD(&CSTNAME.LENGTH, &CSTNAME, 'A17');
-GOTO NEXT70;
-NEXT68
-SET &CSTNAME = LCWORD(&CSTNAME.LENGTH, &CSTNAME, 'A18');
-GOTO NEXT70;

-NEXT70

TABLE FILE ZCUSMAST
PRINT
TABLENAMECUSTNMBR
TABLENAMECUSTNAME1
TABLENAMECUSTNAME2
TABLENAMESTREET
TABLENAMECITY
WHERE TABLENAMECUSTNAME1 CONTAINS '&CSTNAME'
OR TABLENAMECUSTNAME2 CONTAINS '&CSTNAME';
ON TABLE HOLD AS CSTZCN04
END

MATCH FILE CSTZCN01
PRINT TABLENAMESTREET AS 'JUNK1'
BY TABLENAMECUSTNMBR
BY TABLENAMECUSTNAME1
BY TABLENAMECUSTNAME2
BY TABLENAMESTREET
BY TABLENAMECITY
RUN
FILE CSTZCN02
PRINT TABLENAMESTREET AS 'JUNK1'
BY TABLENAMECUSTNMBR
BY TABLENAMECUSTNAME1
BY TABLENAMECUSTNAME2
BY TABLENAMESTREET
BY TABLENAMECITY
RUN
FILE CSTZCN03
PRINT TABLENAMESTREET AS 'JUNK1'
BY TABLENAMECUSTNMBR
BY TABLENAMECUSTNAME1
BY TABLENAMECUSTNAME2
BY TABLENAMESTREET
BY TABLENAMECITY
RUN
FILE CSTZCN04
PRINT TABLENAMESTREET AS 'JUNK1'
BY TABLENAMECUSTNMBR
BY TABLENAMECUSTNAME1
BY TABLENAMECUSTNAME2
BY TABLENAMESTREET
BY TABLENAMECITY
AFTER MATCH HOLD AS CSTZCN05 OLD-OR-NEW
END

TABLE FILE CSTZCN05
SUM JUNK1
PRINT TABLENAMESTREET AS 'JUNK1'
BY TABLENAMECUSTNMBR
BY TABLENAMECUSTNAME1
BY TABLENAMECUSTNAME2
BY TABLENAMESTREET
BY TABLENAMECITY
ON TABLE HOLD AS CSTZCN06
END

TABLE FILE CSTZCN06
PRINT
TABLENAMECUSTNAME1
TABLENAMECUSTNAME2
TABLENAMESTREET
TABLENAMECITY
BY TABLENAMECUSTNMBR
ON TABLE HOLD AS CSTZCN07
END

Thank you,
Geri

quote:

-IF &CSTNAME.LENGTH EQ 3 GOTO NEXT03;
...
-NEXT53
-SET &CSTNAME = LCWORD(&CSTNAME.LENGTH, &CSTNAME, 'A3');
-GOTO NEXT70;

Geri, you could significantly reduce the .LENGTH of your code by replacing all of those many -IF's, -NEXT's and -GOTO's just with this:

-SET &CSTNAME = LCWORD(&CSTNAME.LENGTH, &CSTNAME, 'A&CSTNAME.LENGTH');

Give it a try and see if it helps.

njsden,

Your suggestion was exactly what I need. Thank you!

I added the following and removed all the if, then goto's:

-SET &1NAME = LCWORD(&1NAME.LENGTH, &1NAME, 'A&1NAME.LENGTH');
-SET &2NAME = UPCASE(&2NAME.LENGTH, &2NAME, 'A&2NAME.LENGTH');
-SET &3NAME = LOCASE(&3NAME.LENGTH, &3NAME, 'A&3NAME.LENGTH');

Great! Glad to know it helped.

One tiny comment ... this may not be a problem for you (it obviously seems to be working) but I usually hesitate to name fields and variables starting with numbers.

It is probably a matter of personal preference but I would rather use &NAME1 instead of &1NAME in Dialog Manager or any other language/tool for that matter.

That's because you and me are old-fashioned type of guys!

Yep! I think we both are! and nothing wrong with that, I hope