OraFAQ Forum: Application Express, ORDS & MOD_PLSQL » Usind DECODE with multi-select fields

Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Usind DECODE with multi-select fields (Oracle 11g - APEX 4.2.5)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Usind DECODE with multi-select fields [message #679726]

Thu, 19 March 2020 12:55

AdrianWard
Messages: 21
Registered: September 2019

Junior Member

Hi,

I have a couple of fields on a report form that I use as selection criteria. One is a standard LOV field (Clerks) and the other is a list of Periods (multi-selection).

:P1_CLERKS returns -1 if no item is selected, else a single Clerk ID is passed
:P1_PERIODS returns NULL if no items are selected, else multiple Periods can be selected and passed

In my PL/SQL select statement, I use DECODE to determine what is selected. With the :P1_CLERKS this works fine: -

AND MBG.ACCT_GROUP_CLERK_ID =
DECODE( :p1_clerks
,-1, MBG.ACCT_GROUP_CLERK_ID
,:p1_clerks
)

so if I don't make a selection, records with ANY Clerk are returned. If I enter a specific Clerk, only records containing that Clerk are returned.

All is good.

However, I have the same code with the Periods criteria: -

AND MBS.PERIOD =
DECODE( :p1_period
,NULL, MBS.PERIOD
,:p1_period
)

If I do not make a selection, NULL is passed and all records are selected regardless of Period. Likewise, if I only select ONE Period, only records for that Period are selected. This is all good.
However, If I make multiple selections for Period, no records are selected at all.

I suspect this is because the list of selected Periods is separated by colons? If this is the case, what do they need to be separated by and can I actually change this in APEX?

Or do I need to use a different command in my PL/SQL to select multiple Periods?

Thanks in advance.
Adrian

Report message to a moderator

Re: Usind DECODE with multi-select fields [message #679735 is a reply to message #679726]

Fri, 20 March 2020 06:00

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

In items that allow multiple selection, values are separated by colon (:). Therefore, you'll need to split such "columns" to rows. Here's one option, based on Scott's schema (as I don't have your tables):

SQL> select * From emp where deptno = 10;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09.06.81       2450                    10
      7839 KING       PRESIDENT            17.11.81      10000                    10
      7934 MILLER     CLERK           7782 23.01.82       1300                    10

Code you'd use looks like this:

select ename, hiredate 
from emp
where deptno = 10
  and (   hiredate in (select to_date(regexp_substr(:p1_period, '[^:]+', 1, level), 'dd.mm.yy')
                       from dual
                       connect by level <= regexp_count(:p1_period, ':') + 1
                      )
       or :p1_period is null
      );

If you run it when :P1_PERIOD is null, you'd get all 3 rows (Clark, King and Miller).

If you select e.g. 09.06.81. and 17.11.81, you'd get Clark and King.

Pay attention to the 4th line which uses TO_DATE function - provide appropriate format mask, the one used in your Apex application.

In your case, it might look like this:

and (   mbs.period in (select to_date(regexp_substr(:p1_period, '[^:]+', 1, level), 'dd.mm.yy')
                       from dual
                       connect by level <= regexp_count(:p1_period, ':') + 1
                      )
     or :p1_period is null
    )

[Updated on: Fri, 20 March 2020 06:02]

Report message to a moderator

Re: Usind DECODE with multi-select fields [message #679736 is a reply to message #679735]

Fri, 20 March 2020 06:14

AdrianWard
Messages: 21
Registered: September 2019

Junior Member

Great answer mate, thanks.

As an addition, I also came up with the following solution: -

-- Periods
AND (
MBS.PERIOD =
DECODE( :p13_month_from
,NULL, MBS.PERIOD
,:p13_month_from
)

OR INSTR(:p13_month_from, MBS.PERIOD) > 0
)

INSTR works just as well Smile

Many thanks again

Report message to a moderator

Re: Usind DECODE with multi-select fields [message #679744 is a reply to message #679736]

Fri, 20 March 2020 16:02

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You're welcome; I'm glad if you made it work.

Report message to a moderator

Previous Topic:	Workspace import
Next Topic:	Downloading Checkboxes to CSV

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Mar 02 13:09:07 CST 2025