I have a SQL Report I am using and I need to pass a IN par...

[Kristi Carter]

I have a SQL Report I am using and I need to pass a IN parameter, which works fine in Oracle when I run it and it works fine when my input from the FEX file looks like this:

 

However I need to take this one step further and this fex file will actually put on a Portal and my intent is to provide a listing of dropdown selections for the user to select more than one, however within an HTML file I am creating in App Studio for Filter dropdowns, when using a Multi Select for the IN where clause, it is passing as OR. I need it to pass Multiselect as a comma.

Below is an area I found where I could change this, however there is NOT an option to put in whatever is needed, it just has OR, AND, BY, ACROSS and NONE as options. Is there a way to fix this so when it passes it does it as a comma.

 

Below is sample code that is passing in my SQL Query, notice that if this would separate by a comma it should work. And it does work when I run just as the FEX file alone.

 

And It gives this error:

 

Any suggestions would be much appreciated.

Thanks,

Kristi Carter

[Keith Lyles]

Hi Kristi,

Below is a couple of SET commands you can put at the top of the fex that should do the trick. They are based on the assumption that the All option is available through the html. Plug &SQL_PARAMETER_FINAL into sql WHERE clause.

-SET &SQL_PARAMETER = IF &HTML_PARAMETER EQ _FOC_NULL THEN _FOC_NULL ELSE REPLACE(&HTML_PARAMETER , 'OR ', ,);

-SET &SQL_PARAMETER_FINAL = IF &SQL_PARAMETER EQ _FOC_NULL THEN ELSE WHERE PARAMETER_FIELD IN (&SQL_PARAMETER);

Hope this helps,

Keith

[Kristi Carter]

Thanks for the suggestion. I tried to get this option to work, but it still didnt work quite right.

[David Beagan]

In App Studio 8.2.06 I created a fex with a variable:

 

Typically you would see an OR instead of the comma.

Then when I created a page in App Studio and pulled this fex and its variable into the html I was able to create the following:

 

image.png822357 6.27 KB

 

App Studio was able to pick up the comma like you would want. I couldnt see anywhere in any properties that are shown in App Studio of something being set to a comma. But it does look like the comma setting was pulled in from the fex,

[Kristi Carter]

Is your scenario an actual SQL Report where the variable is set on the SQL portion before it even gets to the FOCUS code in the fex file

[David Beagan]

Not a SQL report, but it could be. The variable is set before it gets to the fex.

I changed my fex report to this:

 

image.png1216450 12.2 KB

 

then run my html file already created in App Studio: