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.

How can I do a conditional SQL?

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))) ))

This post has been answered by L. Fernigrini on Dec 22 2021
Jump to Answer

Comments

Isn't the legend in approx_percentile picture incorrect?

Shouldn't it be "Exact | Deterministic | Non-deterministic"?

Dude!

Isn't the legend in approx_percentile picture incorrect?

Shouldn't it be "Exact | Deterministic | Non-deterministic"?

You missed an 'n' in Loooooooooooooooog Names

Hendrik Schmidt

Hello,

for the link: "here's the script" I receive Page Not Found error

Non-deterministic results are around 5x faster than deterministic. And nearly 15x faster than exact results. So if an estimate is all you need, you can save yourself a lot of time using approx_percentile. If you want to check this for yourself, here's the script I used.

Evening,

I'm working on mvc projects using mean stack how can i integrate these projects without using mongodb. take for example using sql server..any tips will be helpful.

Peter Schlaeger

Hallo

I must worn everyone for trusting in Oracle Json Support. I have very very very painfull experiences  with Json in Oracle.

In 12.1 it took more than one year and lots of support calls before I was able to use it without bugs ( including full database crashes ).

And now I test on 12.2 and I am again back at the state  where I was in March 2016. Again the same bugs and the solution still not include in any windows bundle patch.

So I suggest that everyone should carefully test his Oracle Version and Patch level if it is capable of running Json without bugs before you decide to use Json.

blessed DBA

We are going start some project on this JSON So it is more useful for us

martin_olivia

The Oracle Database 12c is a high-performance, enterprise-class database.

1 - 7

Post Details

Added on Dec 20 2021
14 comments
869 views