Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Problem with filtering data

Ivan2405Aug 26 2013 — edited Aug 26 2013

Hi!

I'm having trouble with filtering data from my table and don't know how to resolve it properly.

I created a tabular form my table, a few text fields that are used as input for different columns of the filter and a filter button that submits the page.

The problem appeared when I try to filter a column that has null values in the table and is editable...that's as far as I have concluded.
So when I press the button I get zero rows even though there should be some.

This is the submitted query:

select

"MSR_PRD_ID",

"SRC_STM_ID",

"ID",

"ISIN",

...

from "#OWNER#"."IZV_SLOG_DET"

WHERE

lower(MSR_PRD_ID) = lower(nvl(:P7_X_MSR_PRD_ID,MSR_PRD_ID)) and

lower(SRC_STM_ID) = lower(nvl(:P7_X_SRC_STM_ID,SRC_STM_ID)) and

lower(ISIN) = lower(nvl(:P7_X_ISIN,ISIN)) and

...

The nvl function is used so that if the user leaves the field empty it only filters by the fields that have input.

Does anyone know how I could circumvent this?


Regards,

Ivan
This post has been answered by Denes Kubicek on Aug 26 2013
Jump to Answer

Comments

Gaz in Oz

Please supply a repeatable test case.

user10621655

Hi,

once connected to db wit sqlcl:

CREATE TABLE T_PINO

(

  "colOne"  VARCHAR2(10 BYTE),

  "colTwo"  VARCHAR2(10 BYTE)

);

Insert into T_PINO ("colOne", "colTwo") Values  ('1234567890', 'TestValue');

COMMIT;

set sqlformat json

select * from T_PINO;

{"results":[{"columns":[{"name":"colOne","type":"VARCHAR2"},{"name":"colTwo","type":"VARCHAR2"}],"items":

[

{"colone":"1234567890","coltwo":"TestValue"}

]}]}

Thanks

Glen Conway

You have definitely found a bug.  Checking some earlier versions of SQLcl and SQL Developer that support set sqlformat, all have the same problem.  We can only hope this is something that will get fixed for 18.2.

user10621655

... a trouble shared is a trouble halved ...thanks a lot

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 23 2013
Added on Aug 26 2013
#apex-discussions, #filter, #form, #problem, #query, #text
2 comments
202 views