| function call with optional parameters [message #687289] |
Wed, 15 February 2023 18:57  |
 |
deahayes
Messages: 6
Registered: January 2023
|
Junior Member |
|
|
I have this function call from a concurrent program parameter:
select RPT_PROJNUM_PKG.get_selected_list(:$FLEX$.PW_PROJECT_NUMBERS, :$FLEX$.PW_PROJECTS) from dual
I would like :$FLEX$.PW_PROJECTS to be optional and only used when :$FLEX$.PW_PROJECT_NUMBERS = ALL. The value from each comes from lovs(valuesets)
FUNCTION get_selected_list( p_item VARCHAR2, p_pw_project_num VARCHAR2 default NULL)
RETURN VARCHAR2
IS
v_pw_project_num varchar2(30);
BEGIN
-- set default
v_pw_project_num := nvl(p_pw_project_num,'P');
IF p_item = 'Clear' THEN
v_selected_list := NULL;
Elsif p_item = 'All' Then
SELECT LISTAGG(segment1,',') WITHIN GROUP (ORDER BY segment1) into v_selected_list
FROM PW_PROJNUMS_V
where class_code = nvl(v_pw_project_num, class_code);
dbms_output.PUT_LINE (' elsif VSELECT LIST = '||v_selected_list);
ELSE
--Concatenate the selected value to the existing list
SELECT NVL2(v_selected_list,v_selected_list ||',',v_selected_list) || p_item
INTO v_selected_list
FROM dual;
dbms_output.PUT_LINE (' Else VSELECT LIST = '||v_selected_list);
END IF;
RETURN v_selected_list;
END get_selected_list;
select RPT_PROJNUM_PKG.get_selected_list(:$FLEX$.PW_PROJECT_NUMBERS, NULL) from dual. The else condition triggers and I get a value and if ALL is selected that does not trigger because of the NULL, what should I do here
[moderator: Code tags added by bb. Next time please add them yourself]
[Updated on: Wed, 15 February 2023 23:35] by Moderator Report message to a moderator |
|
|
|
| Re: function call with optional parameters [message #687291 is a reply to message #687289] |
Thu, 16 February 2023 00:33  |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Just get rid of the P default, as shown below. If there are any continuing problems, it is more likely due to how you are calling the function or other things outside the function.
SCOTT@orcl_12.1.0.2.0> -- table and sample data for testing
SCOTT@orcl_12.1.0.2.0> CREATE TABLE pw_projnums_v AS
2 SELECT 1 AS segment1, 'PROJ1' AS class_code FROM DUAL UNION ALL
3 SELECT 2 AS segment1, 'PROJ2' AS class_code FROM DUAL
4 /
Table created.
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION get_selected_list( p_item VARCHAR2, p_pw_project_num VARCHAR2 default NULL)
2 RETURN VARCHAR2
3 IS
4 v_pw_project_num varchar2(30);
5 -- added missing variable below, presumably in package not provided and data for testing
6 v_selected_list varchar2(1000) := 'some already existing data';
7 BEGIN
8 -- set default
9 -- removed the P deafult below
10 -- v_pw_project_num := nvl(p_pw_project_num,'P');
11 -- and replaced it with the below
12 v_pw_project_num := p_pw_project_num;
13
14 IF p_item = 'Clear' THEN
15 v_selected_list := NULL;
16 Elsif p_item = 'All' Then
17 SELECT LISTAGG(segment1,',') WITHIN GROUP (ORDER BY segment1) into v_selected_list
18 FROM PW_PROJNUMS_V
19 where class_code = nvl(v_pw_project_num, class_code);
20 dbms_output.PUT_LINE (' elsif VSELECT LIST = '||v_selected_list);
21 ELSE
22 --Concatenate the selected value to the existing list
23 SELECT NVL2(v_selected_list,v_selected_list ||',',v_selected_list) || p_item
24 INTO v_selected_list
25 FROM dual;
26 dbms_output.PUT_LINE (' Else VSELECT LIST = '||v_selected_list);
27 END IF;
28 RETURN v_selected_list;
29 END get_selected_list;
30 /
Function created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> -- tests with second parameter null:
SCOTT@orcl_12.1.0.2.0> SELECT get_selected_list ('Clear') FROM DUAL
2 /
GET_SELECTED_LIST('CLEAR')
--------------------------------------------------------------------------------
1 row selected.
SCOTT@orcl_12.1.0.2.0> SELECT get_selected_list ('All') FROM DUAL
2 /
GET_SELECTED_LIST('ALL')
--------------------------------------------------------------------------------
1,2
1 row selected.
elsif VSELECT LIST = 1,2
SCOTT@orcl_12.1.0.2.0> SELECT get_selected_list ('Some') FROM DUAL
2 /
GET_SELECTED_LIST('SOME')
--------------------------------------------------------------------------------
some already existing data,Some
1 row selected.
Else VSELECT LIST = some already existing data,Some
SCOTT@orcl_12.1.0.2.0> -- tests with value in second parameter
SCOTT@orcl_12.1.0.2.0> SELECT get_selected_list ('Clear', 'PROJ1') FROM DUAL
2 /
GET_SELECTED_LIST('CLEAR','PROJ1')
--------------------------------------------------------------------------------
1 row selected.
SCOTT@orcl_12.1.0.2.0> SELECT get_selected_list ('All', 'PROJ1') FROM DUAL
2 /
GET_SELECTED_LIST('ALL','PROJ1')
--------------------------------------------------------------------------------
1
1 row selected.
elsif VSELECT LIST = 1
SCOTT@orcl_12.1.0.2.0> SELECT get_selected_list ('Some', 'PROJ1') FROM DUAL
2 /
GET_SELECTED_LIST('SOME','PROJ1')
--------------------------------------------------------------------------------
some already existing data,Some
1 row selected.
Else VSELECT LIST = some already existing data,Some
SCOTT@orcl_12.1.0.2.0>
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
