Simple CASE Usage

caseoracle

Given:

TABLE test

x  y
a  1
b  2

TABLE test3

x  y
a  1
b  3

I wrote a query to retrieve all values from test, choosing the row with the higher y value.

select t1.x,
  CASE WHEN (EXISTS (select 1 from test2 t2 where t2.y > t1.y))
        THEN t2.y
       ELSE t1.y
  END as x
  from test t1
  LEFT JOIN test2 t2 ON t1.x=t2.x

output:

x  y
a  1
b  3

This query seems correct to me, but I'm not convinced.

Is this the right way to achieve what I'm trying to do?

Best Answer

Your query does have an error. The sub-query you are using for the EXIST is will always return true (or almost always), since you aren't lining it up on the "x" value.

As a side benefit, since it's not doing a sub-query it should perform faster as well.

Try this one:

 SELECT t1.x
    , CASE WHEN t2.y > t1.y THEN t2.y
            ELSE t1.y
            END AS y
 FROM test AS t1
    LEFT OUTER JOIN test2 AS t2 ON t2.x = t1.x

Related Solutions

Oracle – Outer Join Suppressing Index Usage?

This is mostly a partial answer to part 1 with some speculation. You and I know that the following query:

select TD.LAB_NUMBER 
from REQUEST_INFO RI 
LEFT JOIN TEST_DATA TD ON  TD.LAB_NUMBER = RI.LAB_NUMBER AND TD.SEQ_NUMBER = RI.SEQ_NUMBER 
where UPPER(COALESCE(TD.SUPPL_FORMATTED_RESULT,TD.FORMATTED_RESULT))='491(10)376'
  and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549;

Is equivalent to this query:

select TD.LAB_NUMBER 
from REQUEST_INFO RI 
INNER JOIN TEST_DATA TD ON 
TD.LAB_NUMBER = RI.LAB_NUMBER 
AND TD.SEQ_NUMBER = RI.SEQ_NUMBER 
AND UPPER(COALESCE(TD.SUPPL_FORMATTED_RESULT,TD.FORMATTED_RESULT))='491(10)376'
and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549;

However, that doesn't mean that Oracle knows that the two queries are equivalent. The equivalence of the two queries is required for Oracle to be able to use the TD_CUFR_CIDN_SN_LN index. What we're hoping for here is an OUTER JOIN to INNER JOIN conversion. I haven't had a lot of luck finding good information about this, so let's look at the explain plans:

Adding TD.LAB_NUMBER IS NOT NULL to the WHERE clause is a very direct way to let Oracle know that OUTER JOIN to INNER JOIN conversion is possible. We can see that it occurred by looking at the highlighted line. I think that pretty much any column will allow the conversion, although picking the wrong column may change the query results.

If we try a slightly more complicated filter such as (TD.LAB_NUMBER IS NOT NULL OR TD.SEQ_NUMBER IS NOT NULL) then the join conversion does not happen:

We can reason out that the OUTER JOIN is really an INNER JOIN but the query optimizer may have not been programmed to do that. In the original query, you have a COALESCE() expression which is probably just too complex for the query optimizer to apply the query transformation.

Here is a db fiddle for some of the examples.

For the second question, I'm unable to think of a way to work around this. You could try taking advantage of table elimination. As you said this query doesn't even require the REQUEST_INFO table. However, there are a few restrictions:

There are currently a few limitations of table elimination:

Multi-column primary key-foreign key constraints are not supported.

Referring to the join key elsewhere in the query will prevent table elimination. For an inner join, the join keys on each side of the join are equivalent, but if the query contains other references to the join key from the table that could otherwise be eliminated, this prevents elimination. A workaround is to rewrite the query to refer to the join key from the other table (we realize this is not always possible).

Perhaps there is a way to use that for this problem but I'm unable to work around the restrictions.

Getting one record per Emp_ID

I was able to solve my problem by adding the completion dates for JPM_DESCR90 (which is a field decription/name for certifications) in my query i.e. and then looked for max values of those dates in my query by adding MAX( TO_CHAR(C.JPM_DATE_3,'YYYY-MM-DD')) to the SELECT statement. This gives me the most recently acquired certification for each employee. I have attached a snapshot of my result as a jpeg image above

SELECT A.EMPLID, A.JOBCODE, A.JOBCODE_DESCR, A.EMPL_STATUS, A.LOCATION, A.LOCATION_DESCR, A.NAME, E.EMAILID, B.EMPLID, E.OPRID, MAX( TO_CHAR(C.JPM_DATE_3,'YYYY-MM-DD'))
  FROM PS_NPS_VIEW_ALL A, (PS_PERSON_NAME B LEFT OUTER JOIN  PS_NPS_LIC_VW_RPT C ON  B.EMPLID = C.EMPLID ), PSOPRDEFN E
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_NPS_VIEW_ALL A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_NPS_VIEW_ALL A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT)
     AND A.EMPLID = B.EMPLID
     AND A.EMPLID = E.EMPLID
     AND A.PER_ORG = 'EMP'
     AND A.EMPL_STATUS='A'
     AND A.PAYGROUP NOT IN ('SUM','CWR'))
  GROUP BY  A.EMPLID,  A.JOBCODE,  A.JOBCODE_DESCR,  A.EMPL_STATUS,  A.LOCATION,  A.LOCATION_DESCR,  A.NAME,  E.EMAILID,  B.EMPLID,  E.OPRID

However, I have one more question. Is there a way to get all the certifications(JPM_DESCR90 aka Jobcodedescr column( in one row rather than in multiple rows for by getting each value of JPM_DESCR90 separated by ':' character for each employee irrespective of certification completion dates (C.JPM_DATE_3) ?