fetch data based on Column [message #627854] |
Mon, 17 November 2014 08:57 |
|
thelearner
Messages: 133 Registered: April 2013 Location: INDIA
|
Senior Member |
|
|
I have to display data using dynamic column name
create table XX_TEST(name varchar2(20));
insert into XX_TEST('DEPTNO');
commit;
I written this code
DECLARE
CURSOR lc_name
IS
SELECT NAME
FROM xx_test;
str1 VARCHAR2 (1000) := 'Name';
sal NUMBER (10);
ln_sal NUMBER (10);
BEGIN
FOR cur_name IN lc_name
LOOP
str1 :=
'SELECT e.sal -- into ln_sal
FROM emp_d e, dept d
WHERE d.'
|| cur_name.NAME
|| '=e.deptno and empno=7566';
EXECUTE IMMEDIATE str1
INTO ln_sal;
DBMS_OUTPUT.put_line ('sal' || ln_sal);
END LOOP;
END;
am gettig below output
sal 2975
but my requirement is I have to get all values without empno in execute statement like
str1 :=
'SELECT e.sal -- into ln_sal
FROM emp_d e, dept d
WHERE d.'
|| cur_name.NAME
|| '=e.deptno';
my desired output
SAL 800
SAL 1600
SAL 1250
SAL 2975
SAL 1250
SAL 2850
SAL 2450
if there is any mistake please tell me.
How to achieve this..
|
|
|
|
|
|
|
|
|
Re: fetch data based on Column [message #627868 is a reply to message #627854] |
Mon, 17 November 2014 11:00 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
You could use XML:
with t as (
select dbms_xmlgen.getxmltype(
'select sal
from emp e,dept d
where e.' || name || ' = d.' || name
) x
from xx_test
)
select 'SAL ' || sal
FROM t,
xmltable(
'/ROWSET/ROW'
passing x
columns
sal number path 'SAL'
)
/
'SAL'||SAL
-------------------
SAL 800
SAL 1600
SAL 1250
SAL 2975
SAL 1250
SAL 2850
SAL 2450
SAL 3000
SAL 5000
SAL 1500
SAL 1100
'SAL'||SAL
-------------------
SAL 950
SAL 3000
SAL 1300
14 rows selected.
SQL>
SY.
|
|
|