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     [SOLVED] MULTISELECT

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] MULTISELECT
 Login/Join
 
umun
Platinum Member
posted
I am trying to use a WHERE Clause for a MULTISELECT variable.


1. WHERE AC_CODE EQ '&AC_CODE.(OR(FIND AC_CODE,AC_CODE IN ACCODE_MF)).AC_CODE.';

This is giving me error:

(FOC257) MISSING QUOTE MARKS: '29''
0 ERROR AT OR NEAR LINE 18 IN PROCEDURE ACCODE_MF_
(FOC257) MISSING QUOTE MARKS: '29''; ;
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

NB: 29 is one of the multiselected values for &AC_CODE

2. WHERE AC_CODE IN (&AC_CODE.(OR(FIND AC_CODE,AC_CODE IN ACCODE_MF)).AC_CODE.);
This seems to be working but result not validated yet. From the GUI, I was able to determine that the WHERE ...IN implies "is in an external file of test literals". What does this mean?


Please, does anyone know why the first WHERE clause is not working? Is the second one correct? OR can u let me know the correct syntax for a where clause with a multiselect variable.

Again, any significant difference between "MULTISELECT OR" and "MULTISELECT AND"

Thanks

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


WebFOCUS 7.7.02
Windows/SQL/CUBES
HTML/PDF/XLS
 
Posts: 117 | Registered: November 18, 2009Report This Post
Francis Mariani
Expert
posted Hide Post
Provide us the resulting statement (using -SET &ECHO=ALLWink because the value of &AC_CODE may have quotes around it...


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
Prarie
Virtuoso
posted Hide Post
Try this.

quote:
WHERE AC_CODE EQ &AC_CODE.(OR(FIND AC_CODE IN ACCODE_MF)).AC_CODE.);


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
umun
Platinum Member
posted Hide Post
quote:
Originally posted by Francis Mariani:
Provide us the resulting statement (using -SET &ECHO=ALLWink because the value of &AC_CODE may have quotes around it...


Below is the sample code and result

**********************
-SET &ECHO=ALL
TABLE FILE ACCODE_MF
SUM
GROSS_AMT/P15.2CS
NET_AMT/P15.2CS
BY LOCATION
BY TYPE_CD
BY IND_CODE
ON LOCATION SUBHEAD
" "
ON LOCATION NOSPLIT
HEADING
"REPORT - ALL"
"Organisation: DEP"
FOOTING
"Report Produced in:<+0>Local Currency"
"PageWHERE AC_CODE EQ &AC_CODE.(OR(FIND AC_CODE,AC_CODE IN ACCODE_MF)).AC_CODE.;
WHERE WET_IN EQ 'N';
WHERE CANCL EQ 'N';
WHERE LOCATION EQ '&LOCATION.(FIND LOCATION,LOCATION IN ACCODE_MF).LOCATION.';
WHERE SOURCE EQ '&SOURCE.(FIND SOURCE,SOURCE IN ACCODE_MF).SOURCE.';
WHERE FIN_FILTER;
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
***************************

The above code works well when I selected only one value(9) from the multiselect variable(&AC_CODE). But as soon as I select multiple values as shown below, the following was captured.

Result as below:

***********************************
TABLE FILE ACCODE_MF
SUM
GROSS_AMT/P15.2CS
NET_AMT/P15.2CS
BY LOCATION
BY TYPE_CD
BY IND_CODE
ON LOCATION SUBHEAD
" "
ON LOCATION NOSPLIT
HEADING
"REPORT - ALL"
"Organisation: DEP"
FOOTING
"Report Produced in:<+0>Local Currency"
"Page WHERE AC_CODE EQ '9' AND '29';
WHERE WET_IN EQ 'N';
WHERE CANCL EQ 'N';
WHERE LOCATION EQ 'FO';
WHERE SOURCE EQ 'FOC_NONE';
WHERE FIN_FILTER;
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *

0 ERROR AT OR NEAR LINE 19 IN PROCEDURE ACCODE_MF
(FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD:
WH$$$1
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND
*************************************

One thing though is that AC_CODE is defined as I6 in the maste file. I don't know why its adding quotes ('9' AND '29') But when I change the where clause to the following, it works even with multiple selection.

WHERE AC_CODE IN (&AC_CODE.(OR(FIND AC_CODE,AC_CODE IN ACCODE_MF)).AC_CODE.);


Any help will be appreciated.


WebFOCUS 7.7.02
Windows/SQL/CUBES
HTML/PDF/XLS
 
Posts: 117 | Registered: November 18, 2009Report This Post
Waz
Expert
posted Hide Post
There is something fishy here.
quote:
"PageWHERE AC_CODE EQ &AC_CODE.(OR(FIND AC_CODE,AC_CODE IN ACCODE_MF)).AC_CODE.;


You have started a footing line, but added a where clause to it, and not closed the footing line with the " char.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
umun
Platinum Member
posted Hide Post
quote:
Originally posted by Waz:
There is something fishy here.
quote:
"PageWHERE AC_CODE EQ &AC_CODE.(OR(FIND AC_CODE,AC_CODE IN ACCODE_MF)).AC_CODE.;


You have started a footing line, but added a where clause to it, and not closed the footing line with the " char.


Please, disregard the fishy stuff. it was a mistake. Even without the fishy stuff, the error keeps coming when multiple values are selected.


WebFOCUS 7.7.02
Windows/SQL/CUBES
HTML/PDF/XLS
 
Posts: 117 | Registered: November 18, 2009Report This Post
Waz
Expert
posted Hide Post
Are you able to reproduce with one of the sample files ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
GamP
Virtuoso
posted Hide Post
Waz,
actually what umun posted was:
"Page<TABPAGENO of <TABLASTPAGE"
WHERE AC_CODE EQ '9' AND '29';

What strikes me as odd is that he specified WHERE AC_CODE EQ &AC_CODE.(OR(FIND AC_CODE,AC_CODE IN ACCODE_MF)).AC_CODE.; and that the result is not an 'OR' but an 'AND'.
Maybe it's a 769 thing? I tested this on the car file under 7701, and I get the correct expected result.


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
umun
Platinum Member
posted Hide Post
@GamP, you are spot on. I don't understand why the captured result is adding 'AND' when I used 'OR'. Does it mean there is no difference between multiselct OR and Multiselect AND?

/////////
code: WHERE AC_CODE EQ &AC_CODE.(OR(FIND AC_CODE,AC_CODE IN ACCODE_MF)).AC_CODE.;

Captured Result: WHERE AC_CODE EQ '9' AND '29';
//////////

@Waz, I am still getting the error. I haven't tried it on any sample files. Any suggestion on how it can be done?

Please, does anyone know if this WHERE clause where I used 'IN' is right?
WHERE AC_CODE IN (&AC_CODE.(OR(FIND AC_CODE,AC_CODE IN ACCODE_MF)).AC_CODE.);

Its working, though users have not validated the figures. Thanks.


WebFOCUS 7.7.02
Windows/SQL/CUBES
HTML/PDF/XLS
 
Posts: 117 | Registered: November 18, 2009Report This Post
GamP
Virtuoso
posted Hide Post
A simple example would be:
TABLE FILE CAR
PRINT COUNTRY
      CAR
      MODEL
      BODYTYPE
WHERE CAR EQ &CAR.(OR(FIND CAR,CAR IN car)).car.;
ON TABLE PCHOLD FORMAT HTML
END

In my environment this produces the correct result.
If I change the OR in the where to AND I get the same error message as you get (FOC282) when I select more than 1 option.


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
umun
Platinum Member
posted Hide Post
quote:
If I change the OR in the where to AND I get the same error message as you get (FOC282) when I select more than 1 option.


I have tested the sample code and I got same result as you. With OR in the where Clause, it worked fine. But with AND, I get similar error message as below:

****************************
TABLE FILE CAR
PRINT COUNTRY
CAR
MODEL
BODYTYPE
WHERE CAR EQ 'ALFA ROMEO' AND 'AUDI';
ON TABLE PCHOLD FORMAT HTML
END
0 ERROR AT OR NEAR LINE 7 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD:
WH$$$1
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND
*********************************************

I think the problem with my code is that webfocus is changing the 'OR' in the WHRER CLAUSE to an "AND". When I captured the result of this code (WHERE AC_CODE EQ &AC_CODE.(OR(FIND AC_CODE,AC_CODE IN ACCODE_MF)).AC_CODE.Wink, I get WHERE AC_CODE EQ '9' AND '29';. Notice that the "OR" got changed to "AND".

Is this how WF works? Or is it my version?

Thanks.


WebFOCUS 7.7.02
Windows/SQL/CUBES
HTML/PDF/XLS
 
Posts: 117 | Registered: November 18, 2009Report This Post
GamP
Virtuoso
posted Hide Post
It might be your version, but I can't be sure.
I tested this in a lower release (7.6.4) and also in 7.7.01. Both react correct and do not change the or into and. Maybe opening a case will give you a definite answer to this. Alternatively, you could check the list of issues resolved for releases 7610 7611 and 7701 to see if something like what you're experiencing has been resolved.


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Waz
Expert
posted Hide Post
What is the table ACCODE_MF?

What is the format of the field AC_CODE.

It works for me on Win 7.6.9 for the CAR field and SEATS.

If the code works with the car file, then I think the source data needs to be looked at.

You may need to put in a case with IBI as well.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
umun
Platinum Member
posted Hide Post
@GamP and @Waz, thanks for your assistance. I got it resolved. The 'EQ' is now working for multiselect OR. What I did was to recreate the html part of the report and everything started working.

Here is what I think caused the problem: Initially, the report wasn't multiselect, it was just single select. Later, the users requested that I should make the report multiselect so that they can select more than one option. Then, I manually modified the report/html. It seems that I didn't change all the required variables/names to reflect multiselect OR.


Thanks again.


WebFOCUS 7.7.02
Windows/SQL/CUBES
HTML/PDF/XLS
 
Posts: 117 | Registered: November 18, 2009Report This Post
GamP
Virtuoso
posted Hide Post
That explains it.
Lesson learned: never manually change generated code, unless you know exactly what it is you're doing. And even if you do know exactly what you're doing it may backfire on you.
The HTML painter is a very tricky tool to go change manually, because you just do not know what all the underlying javascripts are doing.


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report 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     [SOLVED] MULTISELECT

Copyright © 1996-2020 Information Builders