Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

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


Kristi Carter

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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,

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...