Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Usind DECODE with multi-select fields (Oracle 11g - APEX 4.2.5)
Usind DECODE with multi-select fields [message #679726] Thu, 19 March 2020 12:55 Go to next message
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




Re: Usind DECODE with multi-select fields [message #679735 is a reply to message #679726] Fri, 20 March 2020 06:00 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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 Go to previous messageGo to next message
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
Re: Usind DECODE with multi-select fields [message #679744 is a reply to message #679736] Fri, 20 March 2020 16:02 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You're welcome; I'm glad if you made it work.
Previous Topic: Workspace import
Next Topic: Downloading Checkboxes to CSV
Goto Forum:
  


Current Time: Thu Mar 28 18:08:48 CDT 2024