Hello,
How can I make my SQL work conditionally in the WHERE Clause?
This is the sense that I have:
:is_admin := case when (select ROL_ID from F_ASR_USERS WHERE Trim(USER_IDENTITY) = Trim(coalesce(sys_context('APEX$SESSION','APP_USER'),user))) = 1 then 1 else 0 end;
--IMPORTANT
--IF :is_admin = 1 MUST GET ALL RECORDS (admin)
--IF :is_admin = 0 MUST BRING ONLY CUSTOMER DATA (client)
select
FQ7076E3.FEQ70NN as "Numero_Comprobante",
FQ7076E3.FEQ70NAU2 as "Numero_Autorizacion",
F0101.ABTAX as "RUC_Emisor",
to_date(to_date(1900000 + FQ7076E3.FEIVD, 'yyyyddd'),'DD/MM/YYYY') as "Fecha_Emision",
to_date(to_date(1900000 + FQ7076E3.FEUPMJ, 'yyyyddd'),'DD/MM/YYYY') as "Fecha_Autorizacion",
FQ7076E3.FETAX as "ID_Fiscal",
FQ7076E3.FEALPH as "Razon_Social",
DOCUMENT_TYPE_1,
sys.dbms_lob.getlength("DOCUMENT_TYPE_1")"Archivo_PDF",
DOCU.DOCUMENT_FILENAME_1,
DOCU.DOCUMENT_MIMETYPE_1,
DOCUMENT_TYPE_2,
sys.dbms_lob.getlength("DOCUMENT_TYPE_2")"Archivo_XML",
DOCU.DOCUMENT_FILENAME_2,
DOCU.DOCUMENT_MIMETYPE_2
from F0101 F0101,
F0010 F0010,
FQ7076E3 FQ7076E3,
F_ASR_DOCUMENTS DOCU
where FEDL02='03'
and FEQ70DTP='2'
and FQ7076E3.FECO=F0010.CCCO
and F0010.CCAN8=F0101.ABAN8
-- If user is admin it shows all records
and FQ7076E3.FEQ70NN = DOCU.DOCUMENT_ID(+)
and ( :is_admin = 1 or ( :is_admin = 0 and trim(FQ7076E3.FETAX) = Trim(coalesce(sys_context('APEX$SESSION','APP_USER'),user)) ))
Runs, but does not show data and only works if I remove the variable and the line ( ( :is_admin = 1 or ( :is_admin = 0 and trim(FQ7076E3.FETAX) = Trim(coalesce(sys_context('APEX$SESSION','APP_USER'),user))) ))