Issue
I have created a function get_depatment_names which returns comma separated values in the single Return Value. The output of function is : Sales,Retail,Electronic.
Another function get_location_names returns the same way for the location. Output: West York,Shire,Lancas
Now, these two functions , I'm calling in my select statement along with other columns.
SELECT dept_id,
date,
get_department_names dept_name,
get_location_names location
status
FROM department
WHERE dept_id = 1;
Output is displayed as below:
dept_id ---date ----dept_name -------location----- status
1 ---01/01/2018 --- Sales,Retail,Electronic ---West York,Shire,Lancas-- Active
Expected output:
1--01/01/2018 --- Sales --- West York-- Active
1--01/01/2018 --- Retail --- Shire -- Active
1--01/01/2018 --- Electronic ---Lancas --Active
I tried using the regexp_sub with connect in the select stmt as below but is giving an error of "single row subquery returns more than one row".
SELECT dept_id,
date,
(select regexp_substr(get_department_names(id),'[^,]+',1,level) from dual
connect by regexp_substr(get_department_names(id),'[^,]+',1,level) is not null) dept_name,
(select regexp_substr(get_location_names (id),'[^,]+',1,level) from dual
connect by regexp_substr(get_location_names(id),'[^,]+',1,level) is not null) location
status
FROM department
WHERE dept_id = 1;
Please let me know how to correct this.
Solution
That would be something like this (lines #1 - 4 represent sample data; query you need begins at line #5):
SQL> with department (dept_id, datum, dept_name, location, status) as
2 (select 1, date '2018-01-01', 'sales,retail,electronic',
3 'West York,Shire,Lancas', 'active' from dual
4 )
5 select dept_id,
6 datum,
7 regexp_substr(dept_name, '[^,]+', 1, level) dept_name,
8 regexp_substr(location , '[^,]+', 1, level) location,
9 status
10 from department
11 where dept_id = 1
12 connect by level <= regexp_count(dept_name, ',') + 1;
DEPT_ID DATUM DEPT_NAME LOCATION STATUS
---------- ---------- --------------- --------------- ------
1 01/01/2018 sales West York active
1 01/01/2018 retail Shire active
1 01/01/2018 electronic Lancas active
SQL>
Answered By - Littlefoot Answer Checked By - Katrina (PHPFixing Volunteer)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.