Oracle - From/to Webfocus for Oracle

Question

Guys, I’d like some help.

I have an ETL that was created in Webfocus, and I will migrate it to Oracle.

I’ve done a lot of/stops, and would like an alternative to the following situations. In Webfocus, it can use a previous column, already calculated as a parameter to calculate another value.

Example:

SUM(CASE
   WHEN T1.M2143831 > T1.M2460254
     THEN
       (T1.M2143831 * 100 / T1.M2143833)
     ELSE
       (T1.M2460254 * 100 / T1.M2460256)
END) AS TAXA_L,
SUM(CASE
   WHEN T4.FABRICANTE_ID = 'TESTE'
        AND TAXA_L >= 80
     THEN  1
   ELSE NULL
END) MAIOR80

As an example, it uses the TAXA_L calculated in the previous step, as a parameter, and in Oracle I cannot do this.

I tried it this way:

SUM(CASE
      WHEN T4.FABRICANTE_ID = 'TESTE'
         AND SUM(CASE
                    WHEN T1.M2143831 > T1.M2460254
                    THEN
                        (T1.M2143831 * 100 / T1.M2143833)
                    ELSE
                        (T1.M2460254 * 100 / T1.M2460256)
                 END) >= 80
      THEN
         1
      ELSE
         NULL
END)AS MAIOR80

But I get the following mistake:

ORA-00937: not a single-group group function

I’m racking my brain with this, some alternative?

Answer 1

Problem solved using CTE structure. CTE - Oracle.

I broke the query in two steps.

Following are examples:

• TEC - Example 1
• TEC - Example 2