Home » SQL & PL/SQL » SQL & PL/SQL » Function Creation
Function Creation [message #666865] Wed, 29 November 2017 11:14 Go to next message
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 #666867 is a reply to message #666865] Wed, 29 November 2017 12:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> it isn´t working.
Above is a TOTALLY useless statement!

My car isn't working.
Tell me how to make my car go.

We don't have your tables.
We don't have your data.
Therefore we can't run, test, debug, improve posted code.

You need to post DDL for all tables involved.
You need to post INSERT statements to populate tables involved.
You need to post expected & desired results based upon sample data.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide

Re: Function Creation [message #666872 is a reply to message #666867] Wed, 29 November 2017 15:48 Go to previous messageGo to next message
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 #666874 is a reply to message #666872] Wed, 29 November 2017 16:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide

You need to post DDL for all tables involved.
You need to post INSERT statements to populate tables involved.
You need to post expected & desired results based upon sample data.

BTW - SQL must be known and static at compile time.
I don't understand what is dynamic in the SELECT statement?
Re: Function Creation [message #666875 is a reply to message #666874] Wed, 29 November 2017 19:09 Go to previous messageGo to next message
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 Go to previous message
cookiemonster
Messages: 13917
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.
Previous Topic: switch data positions
Next Topic: regexp_replace() function help
Goto Forum:
  


Current Time: Fri Mar 29 07:17:33 CDT 2024