Reputation: 656

Adding If-else condition in view query in Oracle

My oracle database has 3 tables

Order (id, order_number, external_id(FK to other app),version, description, create_tS) 
Partner (id, order_id(FK to orders),partner_code, partner_name, identifier, type, value)
Fees (id, order_id(FK to orders), identifier, type, value)

in Partner table, identifier would have (INTERNAL, EXTERNAL, LOCAL) distinct values, Type will have (A,B,C,D,E) distinct values and value column will have corresponding values of it. So for one order_id, it will have 5x3 15 rows with each identifier and types.

similarly in fees table identifier would have (EXPENSE, BILLED, SETTLED) distinct value Type will have (F1, F2, F3, F4) distinct values and value column will have corresponding values of it. It will have 3x4 12 rows for each order_id

Basically these tables are vertical tables and not horizontal.

Now I created a view in which all these horizontal tables are transformed horizontally and there should be a unique row for composite key of order_number, external_id and partner_code and that should be the one with max version.

order_data_view(order_number, external_id, partner_code, partner_name, version, create_ts, description, value_a, value_b, value_c, value_d, value_e, value_f1, value_f2, value_f3, value_f4)

SQL is as below

CREATE OR REPLACE VIEW "order_data_view" (order_number, external_id, partner_code, version, partner_name, create_ts, description
value_a, value_b, value_c, value_d, value_e, value_f1, value_f2, value_f3, value_f4) AS
 select o.order_number, o.external_id, p.partner_code, o.version,
    MAX(p.partner_name) as partner_name,
    MAX(o.create_ts) AS create_ts,   
    MAX(o.description) as description,
    MAX(CASE WHEN p.identifier='INTERNAL' AND p.type='A' THEN p.value END) AS value_a,
    MAX(CASE WHEN p.identifier='INTERNAL' AND p.type='B' THEN p.value END) AS value_b,
    MAX(CASE WHEN p.identifier='EXTERNAL' AND p.type='C' THEN p.value END) AS value_c,
    MAX(CASE WHEN p.identifier='LOCAL' AND p.type='D' THEN p.value END) AS value_d,
    MAX(CASE WHEN p.identifier='LOCAL' AND p.type='E' THEN p.value END) AS value_e,
    MAX(CASE WHEN f.identifier='EXPENSE' AND f.type='F1' THEN f.value END) AS value_f1,
    MAX(CASE WHEN f.identifier='EXPENSE' AND f.type='F2' THEN f.value END) AS value_f2,
    MAX(CASE WHEN f.identifier='BILLED' AND f.type='F3' THEN f.value END) AS value_f3,
    MAX(CASE WHEN f.identifier='BILLED' AND f.type='F4' THEN f.value END) AS value_f4,
    from Order o
    LEFT JOIN Partner p ON o.id = p.order_id
    LEFT JOIN FEES f ON o.id = f.order_id
    where (o.order_number, o.external_id, o.version, o.create_ts) in (
        select o1.order_number, o1.external_id, MAX(o1.version), MAX(o1.create_ts) from Order o1 group by o1.order_number, o1.external_id)
    group by o.order_number, o.external_id, o.version, p.partner_code;

Now i've a requirement to add another table. This is also vertical table

order_attributes(id, order_number, external_id, partner_code, identifier, type, value) This table will have identifiers (CALCULATED, EDITED) as distinct values and type as (X, Y, Z) values so in all 6 rows for composite key of order_number, external_id, partner_code

Now i need to add if else condition in my view for value_a, value_b(INTERNAL type) and value_f1, value_f2(EXPENSE type) such that value_a will have value from 'order_attributes' table if there is a row where identifier=CALCULATED and type = X. If it doesnt exist or it is null, then it should pick from partner table as it was before. value_b would be if identifier=EDITED and type = X or as before.

So below changes i made to the view.

CREATE OR REPLACE VIEW "order_data_view" (order_number, external_id, partner_code, version, partner_name, create_ts, description
value_a, value_b, value_c, value_d, value_e, value_f1, value_f2, value_f3, value_f4) AS
 select o.order_number, o.external_id, p.partner_code, o.version,
    MAX(p.partner_name) as partner_name,
    MAX(o.create_ts) AS create_ts,   
    MAX(o.description) as description,
    MAX(NVL(CASE WHEN a.identifier='CALCULATED' AND a.type='X' THEN a.value END,
        (CASE WHEN p.identifier='INTERNAL' AND p.type='A' THEN p.value END))) AS value_a,
    MAX(NVL(CASE WHEN a.identifier='EDITED' AND a.type='X' THEN a.value END,
        (CASE WHEN p.identifier='INTERNAL' AND p.type='B' THEN p.value END))) AS value_b,
    MAX(CASE WHEN p.identifier='EXTERNAL' AND p.type='C' THEN p.value END) AS value_c,
    MAX(CASE WHEN p.identifier='LOCAL' AND p.type='D' THEN p.value END) AS value_d,
    MAX(CASE WHEN p.identifier='LOCAL' AND p.type='E' THEN p.value END) AS value_e,
    MAX(CASE WHEN f.identifier='EXPENSE' AND f.type='F1' THEN f.value END) AS value_f1,
    MAX(CASE WHEN f.identifier='EXPENSE' AND f.type='F2' THEN f.value END) AS value_f2,
    MAX(CASE WHEN f.identifier='BILLED' AND f.type='F3' THEN f.value END) AS value_f3,
    MAX(CASE WHEN f.identifier='BILLED' AND f.type='F4' THEN f.value END) AS value_f4,
    from Order o
    LEFT JOIN Partner p ON o.id = p.order_id
    LEFT JOIN Fees f ON o.id = f.order_id
    LEFT JOIN order_attributes a ON o.order_number = a.order_number and o.external_id = a.external_id and p.partner_code = a.partner_code
    where (o.order_number, o.external_id, o.version, o.create_ts) in (
        select o1.order_number, o1.external_id, MAX(o1.version), MAX(o1.create_ts) from Order o1 group by o1.order_number, o1.external_id)
    group by o.order_number, o.external_id, o.version, p.partner_code;

The problem is confusing in this. for Some order_number it works fine, for some, some columns are picked from partner table even though there is value in order_attribute column for that matching type and identifier. Like value_a will not come fine but value_b will come fine and in the same query if there are 2 partner code, out of 4 values, 3 will be fine 1 is wrong.

I cant find the problem. If i put a wrong clause in else case, it works fine sometimes

for example , if the below is returning wrong value for one partner_code

MAX(NVL(CASE WHEN a.identifier='CALCULATED' AND a.type='X' THEN a.value END,
          (CASE WHEN p.identifier='INTERNAL' AND p.type='A' THEN p.value END)) AS value_a,

changing the else case to some random thing makes it work

MAX(NVL(CASE WHEN a.identifier='CALCULATED' AND a.type='X' THEN a.value END,
          (CASE WHEN p.identifier='INTERNAL' AND p.type='ABC' THEN p.value END)) AS value_a,

I'm totally confused. Please help !!

Link to reproduce https://dbfiddle.uk/t7m-10iP

Upvotes: 2

Answers (3)

MT0

Reputation: 168796

Pivot the tables and then join:

SELECT o.order_number,
       o.external_id,
       p.partner_code,
       o.version,
       p.partner_name,
       o.create_ts,
       o.description,
       COALESCE(a.calculated_x_value, p.a_value) AS value_a,
       COALESCE(a.edited_x_value, p.b_value) AS value_b,
       p.c_value AS value_c,
       p.d_value AS value_d,
       p.e_value AS value_e,
       f.f1_value,
       f.f2_value,
       f.f3_value,
       f.f4_value
FROM   ( SELECT o.*,
                RANK() OVER (
                  PARTITION BY order_number, external_id
                  ORDER BY create_ts DESC, version DESC
                ) AS rnk
         FROM   Orders o
       ) o
       LEFT OUTER JOIN (
         SELECT order_id,
                partner_code,
                MAX(partner_name) AS partner_name,
                MAX(CASE WHEN identifier = 'INTERNAL' AND type = 'A' THEN value END)
                  AS a_value,
                MAX(CASE WHEN identifier = 'INTERNAL' AND type = 'B' THEN value END)
                  AS b_value,
                MAX(CASE WHEN identifier = 'EXTERNAL' AND type = 'C' THEN value END)
                  AS c_value,
                MAX(CASE WHEN identifier = 'LOCAL' AND type = 'D' THEN value END)
                  AS d_value,
                MAX(CASE WHEN identifier = 'LOCAL' AND type = 'E' THEN value END)
                  AS e_value
         FROM   partner
         GROUP BY order_id, partner_code
       ) p
       ON o.id = p.order_id
       LEFT JOIN (
         SELECT order_id,
                MAX(CASE WHEN identifier = 'EXPENSE' AND type = 'F1' THEN value END)
                  AS f1_value,
                MAX(CASE WHEN identifier = 'EXPENSE' AND type = 'F2' THEN value END)
                  AS f2_value,
                MAX(CASE WHEN identifier = 'BILLED' AND type = 'F3' THEN value END)
                  AS f3_value,
                MAX(CASE WHEN identifier = 'BILLED' AND type = 'F4' THEN value END)
                  AS f4_value
         FROM   Fees
         GROUP BY
                order_id
       ) f
       ON o.id = f.order_id
       LEFT JOIN (
         SELECT order_number,
                external_id,
                partner_code,
                MAX(CASE WHEN identifier = 'CALCULATED' AND type = 'X' THEN value END)
                  AS calculated_x_value,
                MAX(CASE WHEN identifier = 'EDITED' AND type = 'X' THEN value END)
                  AS edited_x_value
         FROM   order_attributes
         GROUP BY
                order_number,
                external_id,
                partner_code
       ) a
       ON o.order_number = a.order_number 
          AND o.external_id = a.external_id 
          AND p.partner_code = a.partner_code
WHERE  o.rnk = 1
AND    o.order_number='ORD001'

or, equivalently, you can organise the pivots queries in sub-query factoring clauses (CTEs) if you do not want them nested in the FROM clause of the main query:

WITH ranked_orders AS (
  SELECT o.*,
         RANK() OVER (
           PARTITION BY order_number, external_id
           ORDER BY create_ts DESC, version DESC
         ) AS rnk
  FROM   Orders o
),
pivoted_partners AS (
  SELECT order_id,
         partner_code,
         MAX(partner_name) AS partner_name,
         MAX(CASE WHEN identifier = 'INTERNAL' AND type = 'A' THEN value END)
           AS a_value,
         MAX(CASE WHEN identifier = 'INTERNAL' AND type = 'B' THEN value END)
           AS b_value,
         MAX(CASE WHEN identifier = 'EXTERNAL' AND type = 'C' THEN value END)
           AS c_value,
         MAX(CASE WHEN identifier = 'LOCAL' AND type = 'D' THEN value END)
           AS d_value,
         MAX(CASE WHEN identifier = 'LOCAL' AND type = 'E' THEN value END)
           AS e_value
  FROM   partner
  GROUP BY order_id, partner_code
),
pivoted_fees AS (
  SELECT order_id,
         MAX(CASE WHEN identifier = 'EXPENSE' AND type = 'F1' THEN value END)
           AS f1_value,
         MAX(CASE WHEN identifier = 'EXPENSE' AND type = 'F2' THEN value END)
           AS f2_value,
         MAX(CASE WHEN identifier = 'BILLED' AND type = 'F3' THEN value END)
           AS f3_value,
         MAX(CASE WHEN identifier = 'BILLED' AND type = 'F4' THEN value END)
           AS f4_value
  FROM   Fees
  GROUP BY
         order_id
),
pivoted_attributes AS (
  SELECT order_number,
         external_id,
         partner_code,
         MAX(CASE WHEN identifier = 'CALCULATED' AND type = 'X' THEN value END)
           AS calculated_x_value,
         MAX(CASE WHEN identifier = 'EDITED' AND type = 'X' THEN value END)
           AS edited_x_value
  FROM   order_attributes
  GROUP BY
         order_number,
         external_id,
         partner_code
)
SELECT o.order_number,
       o.external_id,
       p.partner_code,
       o.version,
       p.partner_name,
       o.create_ts,
       o.description,
       COALESCE(a.calculated_x_value, p.a_value) AS value_a,
       COALESCE(a.edited_x_value, p.b_value) AS value_b,
       p.c_value AS value_c,
       p.d_value AS value_d,
       p.e_value AS value_e,
       f.f1_value,
       f.f2_value,
       f.f3_value,
       f.f4_value
FROM   ranked_orders o
       LEFT OUTER JOIN pivoted_partners p
       ON o.id = p.order_id
       LEFT JOIN pivoted_fees f
       ON o.id = f.order_id
       LEFT JOIN pivoted_attributes a
       ON o.order_number = a.order_number 
          AND o.external_id = a.external_id 
          AND p.partner_code = a.partner_code
WHERE  o.rnk = 1
AND    o.order_number='ORD001'

Which, for the sample data (having removed the quoted identifiers), both output:

ORDER_NUMBER	EXTERNAL_ID	PARTNER_CODE	VERSION	PARTNER_NAME	CREATE_TS	DESCRIPTION	VALUE_A	VALUE_B	VALUE_C	VALUE_D	VALUE_E	F1_VALUE	F2_VALUE	F3_VALUE	F4_VALUE
ORD001	1001	P001	3	Partner 1	05-MAY-24 12.00.00.000000	External Id 1	123	4665	1100	14500	17000	50	75	11	13
ORD001	1002	P002	3	Partner 2	05-MAY-24 12.00.00.000000	External Id 2	250	207	2100	24500	27000	850	875	811	813

fiddle

Upvotes: 0

d r

Reputation: 7891

The problem with VALUE_A and VALUE_B "strange" behavior is within the code and the data. When you have added order_attributes table to the code you got two rows from order_attributes table joined to the same order and partner:

-- these order_attributes rows are joined to the same order and partner
Select * From order_attributes Where ID IN(1, 4)
/*
ID ORDER_NUMBER EXTERNAL_ID PARTNER_CODE IDENTIFIER TYPE      VALUE
-- ------------ ----------- ------------ ---------- ---- ----------
1  ORD001              1001 P001         CALCULATED X           123
4  ORD001              1001 P001         EDITED     X          4665   */

As a consequence, partial underlying dataset for your MAX() aggregation would be something like here:

Select    o.ORDER_NUMBER, o.EXTERNAL_ID, p.PARTNER_CODE, 
          p.TYPE, p.VALUE as P_VALUE, a.VALUE as A_VALUE, a.IDENTIFIER
From      orders o
Left Join partners p ON o.id = p.ORDER_ID
Left Join order_attributes a ON a.ORDER_NUMBER = o.ORDER_NUMBER And
                                a.EXTERNAL_ID = o.EXTERNAL_ID And
                                a.PARTNER_CODE = p.PARTNER_CODE 
Where     p.IDENTIFIER = 'INTERNAL' And a.ID IN(1, 4) And p.TYPE IN('A', 'B')
Order By  p.TYPE
/*
ORDER_NUMBER EXTERNAL_ID PARTNER_CODE TYPE    P_VALUE    A_VALUE IDENTIFIER
------------ ----------- ------------ ---- ---------- ---------- ----------
ORD001              1001 P001         A           150        123 CALCULATED
ORD001              1001 P001         A           150       4665 EDITED    
ORD001              1001 P001         B           170        123 CALCULATED
ORD001              1001 P001         B           170       4665 EDITED      */

NOTE: A_VALUE(s) repeats for both 'A' and 'B' types

-- your aggregation for VALUE_A column
MAX(NVL(CASE WHEN a.identifier='CALCULATED' AND a.type='X' THEN a.value END,
        (CASE WHEN p.identifier='INTERNAL' AND p.type='A' THEN p.value END))) AS value_a,
-- gets to choose MAX(Type A CALCULATED) which are 150 and 123 ==> 150

Above code gets max value of A_VALUE from first row and P_VALUE from second row,so MAX([123, 150]) = 150 which is wrong. If attributes value was 151 (instead of 123) you would have a false ok result.

-- your aggregation for VALUE_B column
MAX(NVL(CASE WHEN a.identifier='EDITED' AND a.type='X' THEN a.value END,
        (CASE WHEN p.identifier='INTERNAL' AND p.type='B' THEN p.value END))) AS value_b
-- gets to choose MAX(Type B EDITED) which are 170 and 4665 ==> 4665

Above code gets max value of A_VALUE from forth row and P_VALUE from third row,so MAX([4665, 170]) = 4665 which is false ok result. If attributes value was 169 (instead of 4665) you would have a wrong result 170.

NVL() Function gets you the value that should be discarded and that value could be greater or not compared to the ones that should be considered. That is the reason for "strange" results.

Quote: "changing the else case to some random thing makes it work"

MAX(NVL(CASE WHEN a.identifier='CALCULATED' AND a.type='X' THEN a.value END,
          (CASE WHEN p.identifier='INTERNAL' AND p.type='ABC' THEN p.value END)) AS value_a,

The above random thing takes no p.value at all - the second parameter of Nvl() function is Null. This means that only a.value is present and that suits you in this example but that is also a false ok result.

SOLUTION:

See MTO's answer
Following your current code and logic:
You need to know when the attribute value is IN or OUT and when it is IN then is it IN for VALUE_A or for VALUE_B (same thing with VALUE_F1 and VALUE_F2).
You could create a CTE (named it grid) to collect the data from all 4 tables and prepare them for later processing. The goal is to get all identifiers, types and values in the same row so you can check them and conditionaly aggregate them to get what you need.

WITH
  grid AS
    ( Select  Distinct  
                o.ORDER_NUMBER, o.EXTERNAL_ID, o.DESCRIPTION, o.CREATE_TS, o.VERSION, o.MAX_VERSION, 
                o.PARTNER_CODE, o.PARTNER_NAME, o.PARTNER_IDENTIFIER, o.PARTNER_TYPE, o.PARTNER_VALUE, 
                f.IDENTIFIER as FEES_IDENTIFIER, f.TYPE as FEES_TYPE, f.VALUE as FEES_VALUE, 
                a.IDENTIFIER as ATTR_IDENTIFIER, a.TYPE as ATTR_TYPE, 
                Case When o.PARTNER_IDENTIFIER = 'INTERNAL' And o.PARTNER_TYPE = 'A' And 
                                    a.IDENTIFIER = 'CALCULATED' And a.TYPE = 'X'
                               Then 'A'
                               When o.PARTNER_IDENTIFIER = 'INTERNAL' And o.PARTNER_TYPE = 'B' And 
                                    a.IDENTIFIER = 'EDITED' And a.TYPE = 'X'
                               Then 'B'
                Else   Case When o.PARTNER_IDENTIFIER = 'INTERNAL' And o.PARTNER_TYPE IN('A', 'B') Then 'N' End
                End AS ATTR_FLAG, 
                --
                CASE WHEN 
                          Case When o.PARTNER_IDENTIFIER = 'INTERNAL' And o.PARTNER_TYPE = 'A' And 
                                    a.IDENTIFIER = 'CALCULATED' And a.TYPE = 'X'
                               Then 'A'
                               When o.PARTNER_IDENTIFIER = 'INTERNAL' And o.PARTNER_TYPE = 'B' And 
                                    a.IDENTIFIER = 'EDITED' And a.TYPE = 'X'
                               Then 'B'
                          Else   Case When o.PARTNER_IDENTIFIER = 'INTERNAL' And o.PARTNER_TYPE IN('A', 'B') Then 'N' End
                          End IN('A', 'B')
                     THEN a.VALUE
                END as ATTR_VALUE
      From    ( Select    o.ID as ORDER_ID, o.ORDER_NUMBER, o.EXTERNAL_ID, o.DESCRIPTION, o.CREATE_TS, o.VERSION, 
                          Max(o.VERSION) Over(Partition By o.ORDER_NUMBER, o.EXTERNAL_ID, p.PARTNER_CODE) as MAX_VERSION, 
                          p.PARTNER_CODE, p.PARTNER_NAME, p.IDENTIFIER as PARTNER_IDENTIFIER, p.TYPE as PARTNER_TYPE, p.VALUE as PARTNER_VALUE 
                From      orders o
                Left Join partners p ON o.id = p.ORDER_ID 
                Where     p.PARTNER_CODE Is Not Null 
                Order By  o.ORDER_NUMBER, o.EXTERNAL_ID, p.PARTNER_CODE, p.IDENTIFIER
              ) o
      Left Join fees f ON( f.ORDER_ID = o.ORDER_ID )
      Left Join order_attributes a ON( a.ORDER_NUMBER = o.ORDER_NUMBER And 
                                       a.EXTERNAL_ID = o.EXTERNAL_ID And
                                       a.PARTNER_CODE = o.PARTNER_CODE And 
                                       a.IDENTIFIER IN('CALCULATED', 'EDITED') And
                                       a.TYPE = 'X' )
    )

Some additional columns were created for later distinction of when to redifine values for VALUE_A, VALUE_B.
I did not apply this to columns VALUE_F1 and VALUE_F2 because I am not sure what values in which case you should have fetched. Don't know for sure when it is from fees table or partners table (and when from attributes). You should adjust that to your needs.

--    M a i n    S Q L :
SELECT    ORDER_NUMBER, EXTERNAL_ID, PARTNER_CODE, VERSION, PARTNER_NAME, CREATE_TS, DESCRIPTION,
          Case When MAX(Nvl(ATTR_VALUE, 0)) > 0 THEN MAX(VALUE_A) ELSE MAX(VALUE_N_A) End AS VALUE_A, 
          Case When MAX(Nvl(ATTR_VALUE, 0)) > 0 THEN MAX(VALUE_B) ELSE MAX(VALUE_N_B) End AS VALUE_B, 
          MAX(VALUE_C) AS VALUE_C, MAX(VALUE_D) AS VALUE_D, MAX(VALUE_E) AS VALUE_E,
          MAX(VALUE_F1) AS VALUE_F1, MAX(VALUE_F2) AS VALUE_F2, MAX(VALUE_F3) AS VALUE_F3, MAX(VALUE_F4) AS VALUE_F4
FROM    ( Select  Distinct  
                    ORDER_NUMBER, EXTERNAL_ID, PARTNER_CODE, PARTNER_IDENTIFIER, PARTNER_TYPE, PARTNER_VALUE, ATTR_VALUE,
                    VERSION, PARTNER_NAME, CREATE_TS, DESCRIPTION,
                    --
                    CASE WHEN Nvl(ATTR_VALUE, 0) > 0 And ATTR_FLAG = 'A' THEN ATTR_VALUE End AS VALUE_A,
                    CASE WHEN Nvl(ATTR_VALUE, 0) > 0 And ATTR_FLAG = 'B' THEN ATTR_VALUE End AS VALUE_B,
                    CASE WHEN Nvl(ATTR_VALUE, 0) = 0 And ATTR_FLAG = 'N' And PARTNER_TYPE = 'A' THEN PARTNER_VALUE End AS VALUE_N_A,
                    CASE WHEN Nvl(ATTR_VALUE, 0) = 0 And ATTR_FLAG = 'N' And PARTNER_TYPE = 'B' THEN PARTNER_VALUE End AS VALUE_N_B,
                    --
                    CASE WHEN PARTNER_IDENTIFIER = 'EXTERNAL' AND PARTNER_TYPE = 'C' THEN PARTNER_VALUE END AS VALUE_C,
                    CASE WHEN PARTNER_IDENTIFIER = 'LOCAL'    AND PARTNER_TYPE = 'D' THEN PARTNER_VALUE END AS VALUE_D,
                    CASE WHEN PARTNER_IDENTIFIER = 'LOCAL'    AND PARTNER_TYPE = 'E' THEN PARTNER_VALUE END AS VALUE_E,
                    --
                    CASE WHEN FEES_IDENTIFIER = 'EXPENSE' AND FEES_TYPE = 'F1' THEN FEES_VALUE END AS VALUE_F1,
                    CASE WHEN FEES_IDENTIFIER = 'EXPENSE' AND FEES_TYPE = 'F2' THEN FEES_VALUE END AS VALUE_F2,
                    CASE WHEN FEES_IDENTIFIER = 'BILLED'  AND FEES_TYPE = 'F3' THEN FEES_VALUE END AS VALUE_F3,
                    CASE WHEN FEES_IDENTIFIER = 'BILLED'  AND FEES_TYPE = 'F4' THEN FEES_VALUE END AS VALUE_F4
          From      grid
          Where     VERSION = MAX_VERSION
        )
GROUP BY  ORDER_NUMBER, EXTERNAL_ID, PARTNER_CODE, VERSION, PARTNER_NAME, CREATE_TS, DESCRIPTION

/*
ORDER_ID EXTERNAL_ID PARTNER_CODE    VERSION PARTNER_NAME CREATE_TS                   DESCRIPTION      VALUE_A    VALUE_B    VALUE_C    VALUE_D    VALUE_E   VALUE_F1   VALUE_F2   VALUE_F3   VALUE_F4
-------- ----------- ------------ ---------- ------------ --------------------------- ------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
ORD001          1002 P002                  3 Partner 2    05.05.24 12:00:00,000000000 External Id 2        250        207       2100      24500      27000        850        875        811        813
ORD001          1001 P001                  3 Partner 1    05.05.24 12:00:00,000000000 External id 1        123       4665       1100      14500      17000         50         75         11         13   */

Upvotes: 0

Koen Lostrie

Reputation: 18790

Without any data and no proper reproducible case this is just guessing... but the column "a.identifier" is from a table that is left joined. That usually means that there are records in PARTNER with no matching row in ORDER_ATTRIBUTES. For those rows the value of column "a.identifier" will be NULL. In Oracle, NULL should not be used with a comparison operator, because that result is "UNKNOWN" (see docs). I suggest to account for the possibility of NULL values by either using NVL(a.identifier,'XXX') or adding an IS NULL clause in the case statement. If this guess is not correct then I suggest creating a proper reproducible case: table scripts, a script to create rows, a select (it probably can be a lot simpler than what you have now - only the column that returns a wrong result matters, the output you are getting and the output you are expecting.

-- update --

I suggest you take a step back and remove everything unnecessary from your query (everything after "value_a",) Then remove the aggregate (MAX) and the group by and look at the individual records. That will show you how the aggregate value is being derived and where your logical error is.

The reason I suspect this is confusing and "not working" is that for a single calculation of the MAX aggregate, there are rows that match the "WHEN "a"."identifier"='CALCULATED' AND "a"."type"='X'" predicate AND rows that match the " "p"."identifier"='INTERNAL' AND "p"."type"='A' "

Upvotes: 0

Adding If-else condition in view query in Oracle

Answers (3)

Related Questions