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 Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/5ca45414e587195a5a0b9e7d2a1681ae?s=64&d=mm&r=g) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 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
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 19:51:52 CDT 2024
|