Forum Stats

  • 3,837,208 Users
  • 2,262,236 Discussions
  • 7,900,222 Comments

Discussions

How can I do a conditional SQL?

Edisson Gabriel López
Edisson Gabriel López Member Posts: 65 Green Ribbon

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

Best Answer

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,853 Silver Crown
    Answer ✓

    Based on the images, you are trying to assign the value to :is_admin in the same "sentence" as the query.

    Replace :is_admin with the logic you are trying to assign to it directly on the SELECT and it should work. (remove line 1 and use that logic on lines 26 and 27)

«1

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,212 Red Diamond

    Hi, @Edisson Gabriel López

    Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

    Simplify the problem if possible. for example, if you have a query that involves four tables, but the same problem occurs when you use only one or two of them, then post a problem involving only those one or two tables.

  • tsangsir
    tsangsir Member Posts: 486 Silver Badge

    What is the output of the following SQL? Show a few rows

    select 
      :is_admin = 1, trim(FQ7076E3.FETAX), sys_context('APEX$SESSION','APP_USER'),user
     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(+)
    


  • BEDE
    BEDE Oracle Developer Member Posts: 2,454 Gold Trophy

    Apparently you are working in APEX and :is_admin is a field in your APEX form and are willing to perform some dynamic SQL. RTM on dynamic SQL and see how you can apply that to what you actually need to do.

    Still, I think it would be better to avoid dynamic SQL and use some if logic to execute one of several select statements.

    Still, what I do not really understand is: if the select you posted is included in some PL/SQL block in APEX, then the values are to be fetched into some variables. Or how? Where is that select statement used?

  • BluShadow
    BluShadow Member, Moderator Posts: 42,110 Red Diamond

    a) You would be better using ANSI syntax SQL so you can clearly differentiate between your join conditions and filter (WHERE Clause) conditions...

    b) Clearly your schema has been designed by people who have no idea about good table or column naming. So, aliasing your tables with exactly the same nonsensical name doesn't help others to understand and debug code, now or in the future. Choose aliases that actually mean something, or at the very least don't alias with exactly the same as the table name.

    c) Ensure you specify which table/alias the columns are coming from, so others don't have to guess.

    d) Avoid double quoting column aliases, as it makes the result case sensitive and causes a massive headache for anyone having to use it in code as they would also need to know the exact case used throughout. It's generally considered bad practice.

    select 
      FQ7076E3.FEQ70NN as Numero_Comprobante, -- avoid double quoting column names which makes them case sensitive
      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,
      <table>.DOCUMENT_TYPE_1, -- specify the table/alias for the field, I assume this is "DOCU."
      sys.dbms_lob.getlength("DOCUMENT_TYPE_1") as Archivo_PDF,
      DOCU.DOCUMENT_FILENAME_1,
      DOCU.DOCUMENT_MIMETYPE_1,
      <table>.DOCUMENT_TYPE_2, -- specify the table/alias for the field, I assume this is "DOCU."
      sys.dbms_lob.getlength("DOCUMENT_TYPE_2") as Archivo_XML,
      DOCU.DOCUMENT_FILENAME_2,
      DOCU.DOCUMENT_MIMETYPE_2
    from F0101
         join F0010 on F0010.CCAN8=F0101.ABAN8
         join FQ7076E3 on FQ7076E3.FECO=F0010.CCCO
         left outer join F_ASR_DOCUMENTS DOCU on DOCU.DOCUMENT_ID = FQ7076E3.FEQ70NN
    where <table>.FEDL02 = '03' -- specify the table/alias for the field
    and   <table>.FEQ70DTP = '2' -- specify the table/alias for the field
    and (    :is_admin = 1 -- If user is admin it shows all records
         or (    :is_admin = 0 -- If not admin show records for the app_user
             and trim(FQ7076E3.FETAX) = coalesce(:APP_USER, USER)  -- Trim(coalesce(sys_context('APEX$SESSION','APP_USER'),user))
            )
        )
    

    Your use of sys_context from apex$session is lengthy, and Apex provides the shorter version of :APP_USER, so simplify your code to make it more readable. (also no need to trim the user names, they shouldn't have space around them).

    The only reason I can think of that that logically wouldn't work and not give you the results in the way you describe would be if there was a NULL value being tested, i.e. :is_admin is null, in which case it's neither 1 nor 0.

    Ensure your variable has been correctly populated.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,995 Blue Diamond


    You might find it useful to look at a feature called (depending which pages you read):

    • row-level securirty (RLS)
    • fine grained access control (FGAC)
    • Virtual Private Database (VPD).

    The feature was created so that difference users would see different datasets when running the same query. Essentially you write a function that generates an extra WHERE clause that is dicated by something about the user (e.g. their username, or some logon context).

    Regards

    Jonathan Lewis

    BluShadow
  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Dec 21, 2021 3:33PM

    What do you think you are accomplishing with these lines:

    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",
    

    to_date takes a string and converts it to a DATE, which is an internal, binary construct. So you start with:

    to_date(1900000 + FQ7076E3.FEIVD, 'yyyyddd')
    

    which starts by adding the value of FQ7076E3.FEIVD (which I assume is a NUMBER data type) to the number 1900000. It than has to perform an implied TO_CHAR on the resulting number, in order to get the correct data type for the TO_DATE. Finally, it creates a DATE data type from that string.

    But then you wrap all that in another TO_DATE:

    to_date(to_date(1900000 . . . 
    

    Thus forcing the result of the inner TO_DATE to be converted back to a string by means of another implied TO_CHAR. Just to turn it back into a DATE data type.

    If you think you are changing the format of the DATE (the result of TO_DATE), you need to think again. A DATE type has no format as you think of it. It is an internal, binary structure. All DATE types have exactly the same format. "format", as you think of it, is purely a display issue. Since a DATE is a binary structure, and display screeens an printers only display character strings, the idea of "formatting" a DATE is purely a decision of how we want the date to be represented as a character string on a display device.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,110 Red Diamond

    What do you think you are accomplishing with these lines:

    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",
    


    Jeepers, I must be asleep, I didn't even notice he'd done that. 😂

  • Edisson Gabriel López
    Edisson Gabriel López Member Posts: 65 Green Ribbon

    Hi @tsangsir

    This is the result (can't put :is_admin because it returns the following error so I removed it ORA-20999: Failed to parse SQL query! ORA-06550: line 3, column 10: ORA-00923: FROM keyword not found where expected):


    Error screen:


  • Edisson Gabriel López
    Edisson Gabriel López Member Posts: 65 Green Ribbon
    edited Dec 22, 2021 12:30AM

    Hi @BEDE,

    At first, I tried with PL/SQL and using the Function Body type returning SQL Query, but if I'm not wrong and I understand PL/SQL is not designed to return a SELECT to a table (Actually this query is from Ask TOM who gave me that answer, but it didn't work for me and I told them and got no answer).

  • Edisson Gabriel López
    Edisson Gabriel López Member Posts: 65 Green Ribbon

    Hi @BluShadow ,

    I am getting this error ORA-20001: Query must begin with SELECT or WITH :( (heh heh Thanks for the corrections they are good practices that escaped me ):

    Regarding to_date() is quite rare in apex I don't know if it's a bug or something, but it's the only way the apex interactive report will work and take it as date and allow me to make filters with that column.