Focal Point
[SOLVED] MULTISELECT

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/830103615

August 26, 2010, 09:50 AM
umun
[SOLVED] MULTISELECT
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
August 26, 2010, 10:11 AM
Francis Mariani
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
August 26, 2010, 10:14 AM
Prarie
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
August 26, 2010, 11:57 AM
umun
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
August 26, 2010, 05:49 PM
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.


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!

August 27, 2010, 04:09 AM
umun
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
August 29, 2010, 05:47 PM
Waz
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!

August 31, 2010, 04:57 AM
GamP
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
August 31, 2010, 06:49 AM
umun
@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
August 31, 2010, 07:37 AM
GamP
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
August 31, 2010, 09:07 AM
umun
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
August 31, 2010, 09:15 AM
GamP
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
August 31, 2010, 05:59 PM
Waz
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!

September 02, 2010, 04:20 AM
umun
@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
September 02, 2010, 08:53 AM
GamP
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