Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Trying to create a sql query using a pl/sql function, only part of the query is working, even though the wuery generated its

Im trying to create a query using the following pl/sql function:
declare

v_sql varchar2(32000) := '';

v_per varchar2(10);

v_month varchar2(100);

begin

v_per := to_char(SUBSTR(:P8_PERIOD, 1, INSTR(:P8_PERIOD, '-') - 1));

v_sql := 'SELECT DISTINCT

p.p_name as Name_Student,

count(x.date_attendance) AS days_counted, ';

for rec in (SELECT column_value AS month_value FROM TABLE(apex_string.split(:P8_MONTH, ':'))) loop

v_month := rec.month_value;

v_sql := v_sql || 'sum(CASE when to_char(x.date_attendance,''mm'') = ''' || v_month || ''' ' ||

'and (x.DTAS = 2 OR x.DTAS = 3) THEN 1 ELSE 0 END) as ' ||

to_char(to_date(v_month, 'MM'), 'Month', 'NLS_DATE_LANGUAGE=SPANISH') || ', ';

end loop;

v_sql := rtrim(v_sql, ', ');

v_sql := v_sql || '

FROM

People p,

attendance x

WHERE

TO_CHAR(x.attendance, ''MM'') IN (SELECT column_value FROM TABLE(apex_string.split( '''||:P8_MONTH||''', '':'')))

GROUP BY p.p_name';

apex_debug.message('v_sql: ' || v_sql);

return v_sql;

while im capable of generating a working sql query, the query it isnt giving me the data of the part created in the cycle
for rec in (SELECT column_value AS month_value FROM TABLE(apex_string.split(:P8_MONTH, ':'))) loop

v_month := rec.month_value;

v_sql := v_sql || 'sum(CASE when to_char(x.date_attendance,''mm'') = ''' || v_month || ''' ' ||

'and (x.DTAS = 2 OR x.DTAS = 3) THEN 1 ELSE 0 END) as ' ||

to_char(to_date(v_month, 'MM'), 'Month', 'NLS_DATE_LANGUAGE=SPANISH') || ', ';

end loop;
When i use the query generated, it works just fine but trying to use it directly, it doesnt work
This is the query generated:
SELECT DISTINCT

p.p_name AS Name_Student,

count(x.date_attendance) AS days_counted,

sum(CASE when to_char(x.date_attendance,'mm') = '01' and (x.DTAS = 2 OR x.DTAS = 3) THEN 1 ELSE 0 END) as Enero,

sum(CASE when to_char(x.date_attendance,'mm') = '02' and (x.DTAS = 2 OR x.DTAS = 3) THEN 1 ELSE 0 END) as Febrero,

sum(CASE when to_char(x.date_attendance,'mm') = '03' and (x.DTAS = 2 OR x.DTAS = 3) THEN 1 ELSE 0 END) as Marzo,

sum(CASE when to_char(x.date_attendance,'mm') = '04' and (x.DTAS = 2 OR x.DTAS = 3) THEN 1 ELSE 0 END) as Abril

FROM

People p,

attendance x

WHERE

TO_CHAR(x.attendance, ''MM'') IN (SELECT column_value FROM TABLE(apex_string.split( '''||:P8_MONTH||''', '':'')))

GROUP BY p.p_name

English is not my first language, so I apologize for any spelling mistakes, and i will appreciate any feedback you have. Thanks

This post has been answered by Aamir Sohail on Oct 31 2024
Jump to Answer
Comments
Post Details
Added 6 days ago
6 comments
144 views