Skip to Main Content

APEX

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!

How to set double condition for set of fields? APEX

Nicholas_RApr 15 2019 — edited Apr 15 2019

Hi, I need to set condition for the result of SQL select in APEX.

with    report_3 as (

        -- First query 

        SELECT r3.codart, SUM(r3.valnet) AS Sum_v1

        FROM mgmv r3

        WHERE (r3.codcma IN ('X ','Y','Z', 'A', 'B') AND r3.codart NOT IN ('110085', '500015','500016') AND r3.codart NOT LIKE 'FOR%' AND r3.codart NOT LIKE 'DEN%' AND r3.datare BETWEEN '01-gen-2018' AND '31-dic-2018')

        OR(r3.codcma IN ('A') AND r3.codcma NOT IN ('B') AND r3.codart NOT IN ('110085', '500015','500016') AND r3.codart LIKE 'FOR%' AND r3.codart LIKE 'DEN%' AND r3.datare BETWEEN '01-gen-2018' AND '31-dic-2018')

       GROUP BY r3.codart

)

      , report_4 as (

       --  Second query

        SELECT r4.codart, SUM(r4.qtamov) AS Sum_qt

        FROM mgmv r4

        WHERE (r4.codcma IN ('X ','Y','Z', 'A', 'B') AND r4.codart NOT IN ('110085', '500015','500016') AND r4.codart NOT LIKE 'FOR%' AND r4.codart NOT LIKE 'DEN%' AND r4.datare BETWEEN '01-gen-2018' AND '31-dic-2018' AND r4.caumov NOT IN ('T', '9'))

        OR(r4.codcma IN ('A') AND r4.codcma NOT IN ('B') AND r4.codart NOT IN ('110085', '500015','500016') AND r4.codart LIKE 'FOR%' AND r4.codart LIKE 'DEN%' AND r4.datare BETWEEN '01-gen-2018' AND '31-dic-2018' AND r4.caumov NOT IN ('T', '9'))

        GROUP BY r4.codart

)

SELECT DISTINCT

      r3.codart

    , Sum_qt

    , Sum_v1

    , TRUNC(case when Sum_qt <> 0 then Sum_v1/Sum_qt+0 end, 5) as Cs_md

  

FROM

      report_3 r3

      LEFT OUTER JOIN report_4 r4 ON r3.codart = r4.codart

I need to result FOR1 [...] and DEN1 [...] when they are in A but not in B. But I have the products, with other codes, in A e in B.

Comments

Do you want to run it on the user-tier or the server? User-tier code is typically for doing things like interacting with the user's machine (e.g. opening Acrobat Reader, accessing hardware attached to user's machine, etc). Server side code is usually for doing things that do not use a UI and suppliment what Forms can process (e.g. retrieving data from a REST service, performing complex calculations where the result will be used by the application's PLSQL, etc).

User_OIR0O

Yes, i want to run it on user's machine.

So what you want is called a Forms Java Bean or Plugable Java Components (PJC). A Java Bean is typically used when you want to create your own widget or introduce something unrelated to Forms (e.g. HTML viewer). A PJC is typically used when you want to extend behavior that Forms already provides. For example if you didn't like the way the cursor appears in a Forms text item, you can use a PJC to change that behavior and/or appearance.
There are many example that can be found on the Internet.
https://www.google.com/search?q=oracle+forms+beans
Some information about creating a Java Bean and PJCs can be found in the Form Builder Help.
image.png

User_OIR0O

Thank you for giving me a answer,

After some research and studying oracle forms bean, a jar file need to create to implement java code in oracle bean.

I had create a my custom jar file using netbean tool (which i want to used on my forms) but it show that class not fond error when executing form.

A jar file is also placed in oracle/forms/java/ folder and also add configuration in frmcfg. File as per research .
But i can't find the way to make .jar which is accessible in oracle forms 12c version.

I execute examples files which is download from custom pjc demo. It is working well. But file not working.

You must stop the managed server before copying new files into /forms/java. If the file is already in the directory, stop the server and restart it. Note that the file must be declared in the Web Configuration (i.e. formsweb.cfg) but this must be edited using Fusion Middleware Control. Do not edit the file in a text editor. Also, if you are using Java Web Start, the entry must be made in extensions.jnlp which is found in /forms/java. Again, any changes to this directory will require server restart.

User_OIR0O

Yes, i followed all this steps but still it showing class not found error.

Verify that your JAR is being downloaded - look at the Java Console. If it is, consider the contents of your JAR. Are there sub-directories within it? If so, be sure you reference that path correctly in your form.

User_OIR0O

Thank you,
After adding jar file path into the classpath parameter in the default configuration file and restart the weblogic server it it now accessible and working properly.

I will guess and say that you did not mean "adding jar file path to classpath", but rather adding to ARCHIVE. In most cases, the server classpath has nothing to do with JARs used on the user-tier.

1 - 9

Post Details

Added on Apr 15 2019
3 comments
259 views