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