Still testing reports in a pre-production version of webFOCUS before we go-live!

I created a report that allows users to search for email addresses in the database. However, the results are coming out case sensitive. For example, if a user does a search for "r.fernandez", they will get results containing the string "r.fernandez" BUT they will not get the results containing the string "R.fernandez" because the R is uppercase.

I want the search results to display the actual case of the email address that is in the database and not all results uppercase and not all results lowercase.

Am I using an incorrect logical relation or incorrect set?


Here is the text view of my fex:

SET DBACSENSITIV = OFF
JOIN
LEFT_OUTER USF_AS_GOREMAL.USF_AS_GOREMAL.GOREMAL_PIDM IN USF_AS_GOREMAL
TO MULTIPLE USF_AA_ADDRESS.USF_AA_ADDRESS.PIDM IN USF_AA_ADDRESS TAG J0 AS J0
END
DEFINE FILE USF_AS_GOREMAL
Status/A20=
IF USF_AS_GOREMAL.USF_AS_GOREMAL.GOREMAL_STATUS_IND = 'A' THEN 'Active' ELSE
IF USF_AS_GOREMAL.USF_AS_GOREMAL.GOREMAL_STATUS_IND = 'I' THEN 'Inactive';
END
TABLE FILE USF_AS_GOREMAL
PRINT
J0.USF_AA_ADDRESS.ID AS 'CWID'
J0.USF_AA_ADDRESS.PREF_FIRST_NAME AS 'First Name'
J0.USF_AA_ADDRESS.PREF_MID_NAME AS 'Middle Name'
J0.USF_AA_ADDRESS.PREF_LAST_NAME AS 'Last Name'
USF_AS_GOREMAL.USF_AS_GOREMAL.GOREMAL_EMAIL_ADDRESS AS 'Email Address'
USF_AS_GOREMAL.USF_AS_GOREMAL.Status AS 'Status Indicator'
BY USF_AS_GOREMAL.USF_AS_GOREMAL.GOREMAL_EMAIL_ADDRESS NOPRINT
WHERE ( USF_AS_GOREMAL.USF_AS_GOREMAL.GOREMAL_EMAIL_ADDRESS EQ '&Email' ) OR ( USF_AS_GOREMAL.USF_AS_GOREMAL.GOREMAL_EMAIL_ADDRESS CONTAINS '&Email' );
WHERE J0.USF_AA_ADDRESS.ID NE MISSING;
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,
$
TYPE=REPORT,
COLUMN=N1,
SQUEEZE=1.513889,
$
TYPE=REPORT,
COLUMN=N2,
SQUEEZE=1.402778,
$
TYPE=REPORT,
COLUMN=N6,
SQUEEZE=2.916667,
$
TYPE=REPORT,
COLUMN=N5,
SQUEEZE=2.500000,
$
TYPE=REPORT,
COLUMN=N4,
SQUEEZE=2.055556,
$
TYPE=REPORT,
COLUMN=N3,
SQUEEZE=2.430556,
$
ENDSTYLE
END

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

You are on the right ttrack with the idea of either using Uppercase or Lowercase of the 2 fields that you are matching, however once they match just print the field from the database.

You could build a Drop Down List and have the user select. Then there would be no need for case conversion.

Case sensitive searched will not find "webFOCUS" when searching for the correct capitalization of it, which is "WebFOCUS".

The simple and less efficient method is to UPPER or LOWER case everything in the test.

e.g.

&EMAIL = UPCASE(&Email.LENGTH,&EMAIL,'A&Email.LENGTH');
.
.
.
TABLE ...
WHERE UPCASE({length},GOREMAL_EMAIL_ADDRESS,'A{length}') EQ '&EMAIL'
END

@Doug: A drop down list to select case sensitivity? Some emails (updated by the user online which updates the email field in the DB) include emails LiKeThiS@some-email.com or LIKEthisEXAMPLEtoo@some-email.com, so it will be difficult to give the person running the report to choose upper or lower specifically.

@Waz: I think I'm inserting those in the wrong location. I tried it in the FEX using TABLE USF_AA_GOREMAL WHERE UPCASE... however, I now get two fields when running the report but doesn't work. I also tried a separate FEX and inserting -SET EMAIL = UPCASE(&Email.LENGTH,&EMAIL,'A&Email.LENGTH'); but I can't get it to work. Where exactly do I put that? Sorry I'm really new at this. I tried searching the forum and people give the same suggestions but not exactly where I should be putting it...

Thanks!

You will need to change {length} to the length of your email address field.

-SET &EMAIL = UPCASE(&Email.LENGTH,&EMAIL,'A&Email.LENGTH');

SET DBACSENSITIV = OFF
JOIN
LEFT_OUTER USF_AS_GOREMAL.USF_AS_GOREMAL.GOREMAL_PIDM IN USF_AS_GOREMAL
TO MULTIPLE USF_AA_ADDRESS.USF_AA_ADDRESS.PIDM IN USF_AA_ADDRESS TAG J0 AS J0
END
DEFINE FILE USF_AS_GOREMAL
Status/A20=
IF USF_AS_GOREMAL.USF_AS_GOREMAL.GOREMAL_STATUS_IND = 'A' THEN 'Active' ELSE
IF USF_AS_GOREMAL.USF_AS_GOREMAL.GOREMAL_STATUS_IND = 'I' THEN 'Inactive';
END
TABLE FILE USF_AS_GOREMAL
PRINT 
J0.USF_AA_ADDRESS.ID AS 'CWID'
J0.USF_AA_ADDRESS.PREF_FIRST_NAME AS 'First Name'
J0.USF_AA_ADDRESS.PREF_MID_NAME AS 'Middle Name'
J0.USF_AA_ADDRESS.PREF_LAST_NAME AS 'Last Name'
USF_AS_GOREMAL.USF_AS_GOREMAL.GOREMAL_EMAIL_ADDRESS AS 'Email Address'
USF_AS_GOREMAL.USF_AS_GOREMAL.Status AS 'Status Indicator'
BY USF_AS_GOREMAL.USF_AS_GOREMAL.GOREMAL_EMAIL_ADDRESS NOPRINT
WHERE ( UPCASE({length},USF_AS_GOREMAL.USF_AS_GOREMAL.GOREMAL_EMAIL_ADDRESS,'A{length}') EQ '&EMAIL')
   OR ( UPCASE({length},USF_AS_GOREMAL.USF_AS_GOREMAL.GOREMAL_EMAIL_ADDRESS,'A{length}') CONTAINS '&EMAIL');
WHERE J0.USF_AA_ADDRESS.ID NE MISSING;
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,
$
TYPE=REPORT,
COLUMN=N1,
SQUEEZE=1.513889,
$
TYPE=REPORT,
COLUMN=N2,
SQUEEZE=1.402778,
$
TYPE=REPORT,
COLUMN=N6,
SQUEEZE=2.916667,
$
TYPE=REPORT,
COLUMN=N5,
SQUEEZE=2.500000,
$
TYPE=REPORT,
COLUMN=N4,
SQUEEZE=2.055556,
$
TYPE=REPORT,
COLUMN=N3,
SQUEEZE=2.430556,
$
ENDSTYLE
END

Waz,

Thank you so much for helping me figure out where I need to insert those lines! It now looks like this:

-SET &EMAIL = UPCASE(&Email.128,&EMAIL,'A&Email.128');

SET DBACSENSITIV = OFF
JOIN
LEFT_OUTER USF_AS_GOREMAL.USF_AS_GOREMAL.GOREMAL_PIDM IN USF_AS_GOREMAL
TO MULTIPLE USF_AA_ADDRESS.USF_AA_ADDRESS.PIDM IN USF_AA_ADDRESS TAG J0 AS J0
END
DEFINE FILE USF_AS_GOREMAL
Status/A20=
IF USF_AS_GOREMAL.USF_AS_GOREMAL.GOREMAL_STATUS_IND = 'A' THEN 'Active' ELSE
IF USF_AS_GOREMAL.USF_AS_GOREMAL.GOREMAL_STATUS_IND = 'I' THEN 'Inactive';
END
TABLE FILE USF_AS_GOREMAL
PRINT
J0.USF_AA_ADDRESS.ID AS 'CWID'
J0.USF_AA_ADDRESS.PREF_FIRST_NAME AS 'First Name'
J0.USF_AA_ADDRESS.PREF_MID_NAME AS 'Middle Name'
J0.USF_AA_ADDRESS.PREF_LAST_NAME AS 'Last Name'
USF_AS_GOREMAL.USF_AS_GOREMAL.GOREMAL_EMAIL_ADDRESS AS 'Email Address'
USF_AS_GOREMAL.USF_AS_GOREMAL.Status AS 'Status Indicator'
BY USF_AS_GOREMAL.USF_AS_GOREMAL.GOREMAL_EMAIL_ADDRESS NOPRINT
WHERE ( UPCASE(128,USF_AS_GOREMAL.USF_AS_GOREMAL.GOREMAL_EMAIL_ADDRESS,'A128') EQ '&EMAIL')
OR ( UPCASE(128,USF_AS_GOREMAL.USF_AS_GOREMAL.GOREMAL_EMAIL_ADDRESS,'A128') CONTAINS '&EMAIL');
WHERE J0.USF_AA_ADDRESS.ID NE MISSING;
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,
$
TYPE=REPORT,
COLUMN=N1,
SQUEEZE=1.513889,
$
TYPE=REPORT,
COLUMN=N2,
SQUEEZE=1.402778,
$
TYPE=REPORT,
COLUMN=N6,
SQUEEZE=2.916667,
$
TYPE=REPORT,
COLUMN=N5,
SQUEEZE=2.500000,
$
TYPE=REPORT,
COLUMN=N4,
SQUEEZE=2.055556,
$
TYPE=REPORT,
COLUMN=N3,
SQUEEZE=2.430556,
$
ENDSTYLE
END

However, I'm now running into this error:

0 ERROR AT OR NEAR LINE 1 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC295) A VALUE IS MISSING FOR: &Email
0 ERROR AT OR NEAR LINE 1 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC295) A VALUE IS MISSING FOR: &Email

I'm getting closer though!

quote:

-SET &EMAIL = UPCASE(&Email.128,&EMAIL,'A&Email.128');

Should this not be:

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

or if your incoming filed is &Email, it should be:

-SET &EMAIL = UPCASE(&Email.LENGTH,&Email,'A&Email.LENGTH');

Hope this helps ...

GamP,

For some reason I thought I had it as:
-SET &EMAIL = UPCASE(&EMAIL.LENGTH,&EMAIL,'A&EMAIL.LENGTH');

before changing it due to an error I was getting to:
-SET &EMAIL = UPCASE(&EMAIL.128,&EMAIL,'A&EMAIL.128');

Possibly the error I was getting in relation to a WHERE statement I kept editing

But, your suggestion of having it displayed as -SET &EMAIL = UPCASE(&EMAIL.LENGTH,&EMAIL,'A&EMAIL.LENGTH'); worked for me.

Thanks so much for your help everyone!!