CODEWITHSUNDEEP
oracle-databaseplsqloracle-apex-5.1
YOUCEF sami
YOUCEF sami

Reputation: 29

Display data based on user session information

I'm trying to display certain data based on session user's structure (STRUCT column in the table bellow

enter image description here)

so i created a global item (:P0_STRUCT) which get the value of the structure of the session user upon login and then i wrote this PL/SQL Function in the source for the classic report

declare
v_query    VARCHAR2(500);
BEGIN
IF :P0_STRUCT is not null
   THEN 
   v_query := 'select ID_CR,
       NUM,
       DATE_AR,
       EXPEDITEUR,
       NOM_EXPEDITEUR,
       REFERENCE,
       OBJET,
       E'||:P0_STRUCT||',
       I'||:P0_STRUCT||',
       C'||:P0_STRUCT||',
       INSTRUCTION,
       DATE_INSTRUCTION,
       COPIES,
       OBSERVATION,

       NECESSITE_REP,
       REF_REPONSE
  from COURRIER_ARRIVE
  where E'||:P0_STRUCT||'= ''A Exécuter'' OR I'||:P0_STRUCT|| '= ''P/Information'' OR C'||:P0_STRUCT||' = ''P/Circulation'' '; 
  else v_query := 'select * from COURRIER_ARRIVE where ECIF = ''a'' ';

 END IF;
   return v_query ;
END;

the problem is that all of the three parts of the condition must be true for the function to return data on the report even thought i used OR not AND. i want to get data if only one or all parts of the condition are true. Please, how can this be solved?

Upvotes: 0

Views: 104

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

"Only one is TRUE" is the XOR function, however it does not exist in Oracle. But 

A XOR B = (A OR B) AND NOT (A AND B)

So for three terms it would be 

(A AND B AND C) OR (A XOR B XOR C) = (A OR B OR C) AND ( NOT (A AND B) OR NOT (A AND C) NOT (B AND C))

Replace A by E'||:P0_STRUCT||'= ''A Exécuter'', B by I'||:P0_STRUCT|| '= ''P/Information'', etc.

Upvotes: 1

Related Questions