I have a report where I need to use like for customer name field in the where statement, something like this.
WHERE ( T1 EQ '&COMPANY' );
WHERE ( T2 EQ '&CODE' );
WHERE ( T3 LIKE '&NAME' );

The parameters are being passed from .Net webpage. I am trying to execute the report with company='h', code=100 and name=''. Eventhough there is data for company='h' and code=100, the report returns '0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0' when I add the third where statement. If I remove that its working just fine.

Am I missing something here ? Or you think I should add conditional wheres like

WHERE ( T1 EQ '&COMPANY' );
WHERE ( T2 EQ '&CODE' );
-IF &NAME NE '' THEN [Not sure if this is the right syntax]
WHERE ( T3 LIKE '&NAME' );


Thank you.

WHERE (T3 CONTAINS '&NAME')
might be an option for you.
However if you want to use LIKE (i.e. 'BEGINS WITH') then you'll need to express your variable a bit differently.
If ,say, T3 is format A8
and you want to compare it to the value FRED
then
WHERE (T3 LIKE 'FRED____')
works.
Those trailing _ characters are needed.
So, if your user enters FRED from a launch page
then you could
-DEFAULT &MYNAME = 'ENTER';
-SET &NAME = EDIT( &MYNAME || '________' , '99999999');
and that sticks however many _ are needed on the end of the user-supplied value.
ok?
oh,and its probably a good idea to UPCASE any user-supplied character strings.

Or you could use WHERE (T3 LIKE 'FRED%') to get all rows where T3 begins with 'FRED'.

% !
zut alors!
merci mille fois encore, francois!

Susannah,

$ or % what's the difference?

CHAR use CONTAINS 'XXX$'
VARCHAR use LIKE 'XXX%'

It's all in the docs.

I tried both after going through docs, it didn't work so I posted the question.

WHERE ( ACCOUNT_NAME CONTAINS '&ICUSTNAME$');
WHERE ( ACCOUNT_NAME LIKE '&ICUSTNAME%');

WHERE ( ACCOUNT_NAME CONTAINS '&ICUSTNAME|$');
WHERE ( ACCOUNT_NAME LIKE '&ICUSTNAME|%');

I thought we need to use concatenation for a parameterized where.

Thank you.

WebFOCUS CONTAINS syntax does not use masks, it will generate a LIKE SQL statement for DBMS tables.

-SET &TEST1 = 'ohn';

TABLE FILE DEALER
PRINT NAME
WHERE NAME CONTAINS '&TEST1'
WHERE RECORDLIMIT EQ 10
END
-RUN

generates

SELECT T1."Name" FROM ZZZZ.Dealer T1 WHERE
 (T1."Name" LIKE '%ohn%');

WebFOCUS LIKE syntax behaves like SQL LIKE:

-SET &TEST1 = 'ohn';

TABLE FILE DEALER
PRINT NAME
WHERE NAME LIKE '%&TEST1|%'
WHERE RECORDLIMIT EQ 10
END
-RUN

generates

SELECT T1."Name" FROM ZZZZ.Dealer T1 WHERE
 (T1."Name" LIKE '%ohn%');

Masking characters are % and _ (described in the docs).

Then there's the IS test (which is equivalent to EQ, so I don't know why it exists. Here the mask is $ in conjunction with *:

-SET &TEST1 = 'ohn';

TABLE FILE DEALER
PRINT NAME
WHERE NAME EQ '$&TEST1|$*'
WHERE RECORDLIMIT EQ 10
END
-RUN

generates

SELECT T1."Name" FROM ZZZZ.Dealer T1 WHERE
 (T1."Name" LIKE '_ohn%');

I added WHERE ( ACCOUNT_NAME LIKE '&ICUSTNAME%');
and printed the generated sql, it gives me this error. I am using sql server view for this report. I am not using any webfocus Join Tool. I executed the generated sql on sql server natively and it returns 4 rows.

(FOC2506) INTERFACE-MANAGED NATIVE JOIN SELECTED FOR THE FOLLOWING
(FOC2517) FST. OR LST. WHERE SORT FIELDS DO NOT COVER KEY
(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
(FOC2592) RDBMS-MANAGED JOIN HAS BEEN DISABLED
SELECT T1."company",T1."call_report_ID",
T1."customer_parent_flag",T1."customer_parent_code",
T1."end_user_id_dest",T1."end_user_id",
T1."end_user_destination",T1."account_name",T1."city",
T1."state",T1."type_of_call_desc",T1."call_date",
T1."meeting_comments",T1."reporting_category_desc",T1."name",
T1."dist_code",T1."dist_name",T1."dist_city",T1."dist_state",
T1."RBU",T1."RegionDesc",T1."Regionno",T1."RegionalMgr",
T1."sales_rep",T1."sales_rep_name" FROM
Task_Management.dbo.VIEW_MONTHLY_CALL_REPORTS T1 WHERE
(T1."account_name" LIKE 'mot%');

Anybody any ideas ?

Thank you.

OK.

CONTAINS and LIKE do not operate in exactly the same way.

If the column is var then the test is CONTAINS with NO WILD CARDS.

Not in a position to check varchar and LIKE at the moment.

JG, CONTAINS never uses wild cards.

KK, please post the WebFOCUS code. It is strange you're getting a JOIN trace message (FOC2506) when the SQL shows that you're accessing only one table.

Cheers.

-* File monthly_call_reports.fex
-OLAP OFF
SET NODATA = No Comments
SET ALL = OFF
-SET ECHO=ALL;
-RUN
-*TO DISPLAY THE SQLS GENERATED
SET TRACEOFF = ALL
SET TRACEON=STMTRACE
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACEUSER = ON
-DEFAULTS &ICOMPANY = 'HH';
-DEFAULTS &IBUSINESSUNIT = 'ABU';
-DEFAULTS &IMARKETCODE='';
-DEFAULTS &IMARKETSEGMENTCODE='';
-DEFAULTS &IREGION = '6';
-DEFAULTS &ITERRITORY = '58';
-DEFAULTS &IPERIOD = '04/2007';
-DEFAULTS &IPARENTNAME = '';
-DEFAULTS &IPARENTCODE = '';
-DEFAULTS &ICUSTNAME = '';
-DEFAULTS &ICUSTCODE = '';
DEFINE FILE TASK-VIEW_MONTHLY_CALL_REPORTS
LOCATION/A20=CITY || (', ' | STATE);
RepName/A20=LCWORD(20, SALES_REP_NAME, RepName);
chgdate/A20=CHGDAT('MYY', 'MYYX', PERIOD,'A20' );
DistCode/A20=IF DIST_CODE NE ' ' THEN ('(' || DIST_CODE ||', ' ) ELSE ' ';
DistName/A60=IF DIST_NAME NE ' ' THEN DIST_NAME ||(', ' | DIST_CITY | ', ' | DIST_STATE || ')') ELSE ' ';
DistCity/A20=(DIST_CITY ||',');
CustParentCode/A20=IF CUSTOMER_PARENT_FLAG EQ 'D' THEN PARENTCODE ELSE ' ';
EndUser/A20=END_USER_ID_DEST;
END
TABLE FILE TASK-VIEW_MONTHLY_CALL_REPORTS
SUM
MEETING_COMMENTS AS ' '
COMPANY NOPRINT
END_USER_ID NOPRINT
END_USER_DESTINATION NOPRINT
CUSTOMER_PARENT_FLAG NOPRINT
CUSTOMER_PARENT_CODE NOPRINT
DIST_CODE NOPRINT
BY REGIONNO NOPRINT
BY REPORTING_CATEGORY_DESC NOPRINT
BY RepName NOPRINT
BY CALL_REPORT_ID NOPRINT
BY ACCOUNT_NAME NOPRINT
BY TYPE_OF_CALL_DESC NOPRINT
BY CALL_DATE AS ' '

HEADING
""
FOOTING
" "
WHERE ( COMPANY EQ '&ICOMPANY' );
WHERE ( RBU EQ '&IBUSINESSUNIT' );
WHERE ( NEO_MARKET_CODE EQ '&IMARKETCODE' );
WHERE ( SEGMENT_CODE EQ '&IMARKETSEGMENTCODE' );
WHERE ( REGIONNO EQ '&IREGION' );
WHERE ( SALES_REP EQ '&ITERRITORY' );
WHERE ( PERIOD EQ '&IPERIOD' );
WHERE ( CUSTOMER_PARENT_CODE EQ '&ICUSTCODE');
WHERE ( ACCOUNT_NAME LIKE '&ICUSTNAME%');
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
END

Thank you much.

quote:

TASK-VIEW_MONTHLY_CALL_REPORTS

I think we need to see the Master (mas) and perhaps the Access (acx) file as well.

Francis you are absolutely correct and my original posting was very miss leading.

But the subsequent one actually did say that.

quote:

(FOC2506) INTERFACE-MANAGED NATIVE JOIN SELECTED FOR THE FOLLOWING
(FOC2517) FST. OR LST. WHERE SORT FIELDS DO NOT COVER KEY
(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
(FOC2592) RDBMS-MANAGED JOIN HAS BEEN DISABLED

These re not necessarily errors - they're SQL trace comments that I almost always try to eliminate by reworking the WebFOCUS code. The code should have returned the four rows that you got when you ran the SQL code.

If I have data-manipulation to do - EDIT, LCASE, etc, I usually have a step that extracts all the required columns and creates a HOLD file, then I do the DEFINEs, EDIT, LCASE, etc. on this hold file, which is usually a small answer set from a large table. Don't forget that your DEFINE statements are operating on each row of your DBMS table.

this will not solf the problem, but....

quote:

SET ALL = OFF
-SET ECHO=ALL;
-RUN

The code should be: -SET &ECHO=ALL;

What might work is

DEFINE ...
SUBACCNAME/A5=EDIT(ACCOUNT_NAME,'99999');

Now use the where statement

WHERE SUBACCNAME EQ '&SUBACC' that only has the 5 first characters.

However, a selection on a defined field is not passed in the SQL to the DBMS.
Here's what we do. On the HTML form, I have two parameters. A drop down box selects the "operator" which will be Contains, Begins with, or Equals.
The other is a text box for user input. Say we are searching for a policy holder:

-SET &NAMSRCH=UPCASE(&NAMSRCH.LENGTH,&NAMSRCH,'A&NAMSRCH.LENGTH');
-SET &SNAM=IF &NAMSRCH GT ' ' THEN (&NAMSRCH || '%');

Then in the TABLE request if have:
-IF &NAMSRCH EQ ' ' THEN GOTO SKIPNAMSRCH;
-IF &SEARCHTYPE NE 'Begins' GOTO SKIPBEGIN2;
WHERE (POL_NAM LIKE '&SNAM' );
-GOTO SKIPNAMSRCH
-SKIPBEGIN2
-IF &SEARCHTYPE NE 'Equals' GOTO SKIPEQUAL2;
WHERE (POL_NAM EQ '&NAMSRCH' );
-GOTO SKIPNAMSRCH
-SKIPEQUAL2
-IF &SEARCHTYPE NE 'Contains' GOTO SKIPNAMSRCH;
WHERE ( POL_NAM CONTAINS '&NAMSRCH' );
-SKIPNAMSRCH

You could also add DM commands to skip the WHEREs altogether if the value is blank.
The appropriate WHERE statements are always passed to the DBMS.

I recreated the synonym (without define fields) and the report and tried adding where statements and display fields one by one. The report is not working. Not sure what's wrong. I still get the same error.


09.30.29 BR (FOC2506) INTERFACE-MANAGED NATIVE JOIN SELECTED FOR THE FOLLOWING
09.30.29 BR (FOC2517) FST. OR LST. WHERE SORT FIELDS DO NOT COVER KEY
09.30.29 BR (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
09.30.29 BR (FOC2592) RDBMS-MANAGED JOIN HAS BEEN DISABLED
09.30.29 AE SELECT T1."company",T1."account_name",T1."method_desc",
09.30.29 AE T1."call_date",T1."RBU" FROM
09.30.29 AE Task_Management.dbo.VIEW_MONTHLY_CALL_REPORTS T1 WHERE
09.30.29 AE (T1."account_name" LIKE 'kaman%') AND (T1."RBU" = 'abu') AND
09.30.29 AE (T1."company" = 'hh');

How about just copy this piece of code

SELECT T1."company",T1."account_name",T1."method_desc",
T1."call_date",T1."RBU" FROM
Task_Management.dbo.VIEW_MONTHLY_CALL_REPORTS T1 WHERE
(T1."account_name" LIKE 'kaman%') AND (T1."RBU" = 'abu') AND
(T1."company" = 'hh');

and go to your DBA and try to run it directly on the database.

Does it work?

Do you get the records you expect?

If not it might be a basic error in your selection.

As mentioned above the fst and lst errors are more a warning.

Try loading the basic selection into a hold file and create the fst and lst aggregation after that.

Finally the problem is resolved by IBI Tech Support. Thank you. The solution is to add quotes around the variable.

-SET &TST = '''' || &CHARS || '%''';
Then pass it through the WHERE &test.

Frank,

I tried that, the query returns the results perfectly fine. I didn't have any problems, so I created a case with the Tech Support.

Thank you everyone for your suggestions.

KK

Intriguing. Is this due to a bug or something?

There's got to be something else going on here. In my example, I set &SNAM and it's not using quotes, but it works fine. I do use parentheses - is that doing the same thing?

They didn't say if it was a bug, but they said its a known problem and they had to search for it too. They did an advance search by using 'Like Variable'.

http://techsupport.informationbuilders.com/sps/31802024.html

KK

Darin,

quote:

I do use parentheses - is that doing the same thing?

Yes, I am using parentheses too.

Unfortunately, that document doesn't really help at all - for one, you cannot run the snippet of code provided - it's missing two variables.

What is being implied here is that creating the LIKE search variable with the quotes added works, but without the quotes does not work. For a FOCUS DB, either gives the same results:

Version 1

-SET &ECHO='ALL';

-*-- Default search type: leading/trailing
-DEFAULT &TP = 'T';

-*-- Default Search value
-DEFAULT &CHARS = 'Y';

-SET &TST = IF &TP.EVAL EQ 'T' THEN '''%' | &CHARS || '''' ELSE '''' || &CHARS || '%''';
TABLE FILE CAR
PRINT COUNTRY
WHERE COUNTRY LIKE &TST
END
-RUN

Version 2

-SET &ECHO='ALL';

-*-- Default search type: leading/trailing
-DEFAULT &TP = 'T';

-*-- Default Search value
-DEFAULT &CHARS = 'Y';

-SET &TST = IF &TP.EVAL EQ 'T' THEN '%' || &CHARS ELSE &CHARS || '%';
TABLE FILE CAR
PRINT COUNTRY
WHERE COUNTRY LIKE '&TST'
END
-RUN

This also works for a MS SQL Server table.

works for DB2 as well. That's why I think there's something more than meets the eye. Also - when I said "using parentheses", I meant in the -SET line.