Oracle Apex Flash Chart not displaying label

Question

I am building stacked column 3d Flash chart in Oracle Apex. it is based on the PL/SQL returning SQL query. PL/sql is required to capture all types of properties and count number of instances in database connected to those properties. The code is :

        DECLARE
l_qry VARCHAR2(32767);
v_id NUMBER;
v_resort VARCHAR2(80);

  BEGIN
 l_qry := 'SELECT ''fp=&APP_ID.:802::app_session::::P5_SEARCH_MONTH:''||TO_CHAR(E.ENQUIRED_DATE,''MON-YY'')||'':'' link,';

 l_qry := l_qry || ''' ''||TO_CHAR(E.ENQUIRED_DATE,''MON-YY'')||'':'' label,';


  --Loop through the resorts and add a sum(decode...) column with column alias
  FOR r1 IN (SELECT DISTINCT a.resort_id FROM enquiry a, resort b where    a.resort_id IS NOT NULL and a.resort_id = b.id and b.active =1)
    LOOP
       select name into v_resort
       from resort
       where id = r1.resort_id;

What happens now PLSQL is loping through all resorts and counts them. this solution does work to certain degree however after value returned I want to have label with name of resort taken from v_resort. It is where main difficulty is

l_qry := l_qry || 'sum(decode(resort_id,''' || r1.resort_id ||''',1,0)) test,';
l_qry := l_qry || 'sum(decode(resort_id,''' || r1.resort_id ||''',1,0)) '|| v_resort||',';

First line will display 'test' label when you hover over the columns just fine. However the the second one with '|| v_resort ||' will cause issue with not displaying back any results... it is not giving #no_data_found# message but just blank field.. The rest of the code :

  END LOOP; 

 --Trim off trailing comma
 l_qry := rtrim(l_qry, ',');

 --Append the rest of the query
 l_qry := l_qry || ' from  ENQUIRY E,resort r
 where 
 e.enquiry_type=''AVAILR''
 and e.enquiry_channel like '''||:P5_CHANNEL||''' 
 and trunc(e.created) >= '''||:P5_DATE_FROM||'''
 and trunc(e.created) <= '''||:P5_DATE_TO||''' 
 and e.ENQUIRED_DATE > '''||:P5_DATE_FROM||'''
 and ((NVL(:P5_AVAILABLE,''A'')=''A'') or ('''||:P5_AVAILABLE||'''=AVAILABLE))
 and e.resort_id = r.id
 and ((resort_id = '''||:P6_RESORT||''') or ('''||:P6_RESORT||'''  like ''0''))
 group by To_Char(ENQUIRED_DATE,''MON-YY''),TO_CHAR(ENQUIRED_DATE,''YYMM'')
 Order By TO_CHAR(ENQUIRED_DATE,''YYMM'')';

 return(l_qry);


 END;

I tired '|| v_resort ||' , '''||v_resort||''' . Any other ideas how to create value of label to be taken from v_resort which holds resort name ?

foobarbaz foobarbaz · Accepted Answer · 2013-08-16T01:44:55

Try "'||v_resort||'"
You have to use a quoted identifier since v_resort can be any string and there are many rules for unquoted names.

Oracle Apex Flash Chart not displaying label

1 Answers