APEX - switch item - modify report

1
votes

I have a set of switch items (yes,no) which should result in a modified report when clicked.

Simplified setup: Button1, Dynamic Report

PL/SQL query from dynamic report

declare
q_query varchar2(500);
q_select varchar2(500);
q_where varchar2(500);

begin
q_select := 'select Kat1, Kat2, Kat3 from TBL ';

IF :Button1 = '1' THEN
q_where := 'where Kat3=KatXYZ';
END IF;

q_query := q_select||q_where;

return q_query;
end;

FYI

  • Kat3 contains strings
  • KatXYZ is the string to search for
  • The query (select ... from ... when Kat3='KatXYZ';) works when changing to SQL instead of PL/SQL
  • Dynamic Event created for Button1 to refresh report on change
  • q_where := 'where Kat3="KatXYZ"'; does not work
  • Button1 Custom Settings On Value: 1 - Off Value: 0

Problem: Nothing happens when clicking the switch item "Yes". Any ideas?

1
sqlplsqloracle-apex

1 Answers

0
votes

This:

q_where := 'where Kat3=KatXYZ';

says that column Kat3 should be equal to column/parameter/function named KatXYZ, not string 'KatXYZ'.

Try with 

q_where := 'where Kat3 = ''KatXYZ''';

or

q_where := 'where Kat3 = ' || chr(39) || 'KatXYZ' || chr(39);

or 

q_where := q['where Kat3 = 'KatXYZ']'

Any of those should be OK.