Function Creation [message #666865] |
Wed, 29 November 2017 11:14 |
|
WhoAmI
Messages: 6 Registered: November 2017
|
Junior Member |
|
|
Select distinct a.id,
p1.ref as book,
p2.ref as text,
p3.ref as note
from library a
left join room p1 on a.id=c1.id and p1.name='BOOK'
left join room p2 on a.id=c2.id and p2.name='TEXT'
left join room p3 on a.id=c3.id and p3.name='NOTE'
I have the above query and I need to create a function which dynamically creates the above query. The p1, p2 and p3 are dynamically created. Am novice to Pl/SQL and I tried the below code but it isn´t working.
Can anyone of you help me out please.
create or replace FUNCTION F_TRAIL(vSTART_ID IN NUMBER, vEND_ID IN NUMBER) RETURN VARCHAR2 AS
TYPE fpcur is REF CURSOR;
rollid NUMBER := 0;
cursor c is select ID, name from room where roll_id=rollid;
c_var c%rowtype;
target number := null;
rlsql varchar2(12000) := 'select distinct ';
joinsql varchar2(100) := '';
BEGIN
select roll_id into rollid from main where id=vSTART_ID;
open c; fetch c into c_var;
while c%found loop
rlsql := rlsql||'ref as '||c_var.name||',';
rlsql := rlsql||chr(13)||chr(10);
rlsql := rlsql||' from library';
rlsql := rlsql||' left join room on a.id='||c_var.id||' and name='''||c_var.name||''''||chr(13)||chr(10);
else
rlsql := rlsql||chr(13)||chr(10);
end if;
joinsql := 'left join'||chr(13)||chr(10);
fetch c into c_var;
end loop;
close c;
END F_TRAIL;
|
|
|
|
Re: Function Creation [message #666872 is a reply to message #666867] |
Wed, 29 November 2017 15:48 |
|
WhoAmI
Messages: 6 Registered: November 2017
|
Junior Member |
|
|
I have this query
Select distinct a.name,
p1.ref as book,
p2.ref as text,
p3.ref as note
from library a
left join room p1 on a.id=p1.id
left join room p2 on a.id=p2.id
left join room p3 on a.id=p3.id
I started creating the function as below. The output of the function needs to be the above query. The p1, p2 and p3 are dynamically generated
rlsql varchar2(12000) := 'select distinct ';
rlsql := rlsql||'ref as '||c_var.name||',';
rlsql := rlsql||chr(13)||chr(10);
rlsql := rlsql||' from library';
rlsql := rlsql||' left join room on a.id='||c_var.id||'
when I execute the above part of function then it returning me as the below sample
Select distinct
ref as book from library,
ref as text from library,
ref as note from library
How to remove the "from library" on each line and how to get p1. ,p2. ,p3. dynamically
Any help on this is highly appreciated
|
|
|
|
Re: Function Creation [message #666875 is a reply to message #666874] |
Wed, 29 November 2017 19:09 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
Based upon your malformed & fractious SQL, it appears to me that P1, P2, & P3 are nothing more than static table alias for ROOM table.
>The p1, p2 and p3 are dynamically created.
>how to get p1. ,p2. ,p3. dynamically
>The p1, p2 and p3 are dynamically generated
Please elaborate on from where do p1, p2, & p3 originate?
They are dynamically generated based upon what criteria?
How to ascertain that the generated dynamic values of p1, p2, & p3 are correct?
|
|
|
Re: Function Creation [message #666880 is a reply to message #666875] |
Thu, 30 November 2017 03:31 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
p1, p2 and p3 are table aliases. Dynamically generating table aliases is completely pointless.
If this query really needs to be dynamic then you need to actually show us which bits of it can change and describe what determines when it's changed.
|
|
|