Can you create a case statement in select command that evaluates two different table fields?

Question

SELECT HQRH_BADGENO,
       HQRH_SHIFT,
       HQBD_BADGENAME,
       HQRH_DISCRRECONCILE,
       HQRH_ADJUSTAMOUNT,
       HQRH_DISCRADJUSTMENT,
       CASE HQRH_DISCRRECONCILE
         WHEN 'HQRH_DISCRRECONCILE <0' THEN
          'HQRH_ADJUSTAMOUNT'
         WHEN 'HQRH_ADJUSTAMOUNT>0' THEN
          'HQRH_ADJUSTAMOUNT'
         WHEN 'HQRH_DISCRADJUSTMENT<0' THEN
          'HQRH_DISCRADJUSTMENT * (-1)'
         ELSE
          'HQRH_DISCRRECONCILE>0'
       END
  FROM HQ_CASHRECONCILEHEADER, HQ_BADGE
 WHERE HQ_CASHRECONCILEHEADER.HQRH_PLAZANO = HQ_BADGE.HQBD_PLAZANO
   and HQ_CASHRECONCILEHEADER.HQRH_BADGENO = HQ_BADGE.HQBD_BADGENO
   AND (HQ_CASHRECONCILEHEADER.HQRH_OPERATIONALDATE =
       TO_DATE(:dtstart, 'DD/MM/YYYY'))
   AND (HQ_CASHRECONCILEHEADER.HQRH_PLAZANO = :plazano)

I need to apply case/if else condition to above select statement. I tried but I am getting an error. That error is

ORA : 00923: from keyword not found where expected

Actually I want to apply an if-else condition like this:

if colA -ve then                             ' here colA = excess
    if colB +ve then paid = colB             ' here colB = paid
    elseif colC -ve then unpaid = colc * -1  ' here colC = unpaid
    else do nothing
else if colA +ve then excess = cola
else do nothing

Can anyone please check my statement and give any suggestions?

Answer 1

Remove single quotes from your query when use case statement:

       CASE HQRH_DISCRRECONCILE
         WHEN HQRH_DISCRRECONCILE <0 THEN
          HQRH_ADJUSTAMOUNT
         WHEN HQRH_ADJUSTAMOUNT>0 THEN
          HQRH_ADJUSTAMOUNT
         WHEN HQRH_DISCRADJUSTMENT<0 THEN
          HQRH_DISCRADJUSTMENT * (-1)
         ELSE
          HQRH_DISCRRECONCILE>0
       END

Answer 2

This is how I re-wrote your query:

SELECT crh.hqrh_badgeno,
       crh.hqrh_shift,
       b.hqbd_badgename,
       crh.hqrh_discrreconcile,
       crh.hqrh_adjustamount,
       crh.hqrh_discradjustment,
       CASE 
         WHEN crh.hqrh_discrreconcile < 0 THEN crh.hqrh_adjustamount
         WHEN crh.hqrh_adjustamount > 0 THEN crh.hqrh_adjustamount
         WHEN crh.hqrh_discradjustment < 0 THEN crh.hqrh_discradjustment * (-1)
         ELSE 0 --'HQRH_DISCRRECONCILE > 0' 
      END
 FROM HQ_CASHRECONCILEHEADER crh
 JOIN HQ_BADGE b ON b.hqbd_plazano = crh.hqrh_plazano
                AND b.hqbd_badgeno = crh.hqrh_badgeno
WHERE crh.hqrh_operationaldate = TO_DATE(:dtstart, 'DD/MM/YYYY')
  AND crh.hqrh_plazano = :plazano

It's unclear to me what is supposed to happen if none of the WHEN clauses match. The CASE expression needs to always return the same data type, and anything inside single quotes is treated as a string but the other values appear to be numeric. So I hardcoded it to return zero, and left the ELSE clause visible in a comment (won't appear in query output).

Answer 3

Try this

SELECT HQRH_BADGENO,
   HQRH_SHIFT,
   HQBD_BADGENAME,
   HQRH_DISCRRECONCILE,
   HQRH_ADJUSTAMOUNT,
   HQRH_DISCRADJUSTMENT,
   CASE 
     WHEN HQRH_DISCRRECONCILE <0 THEN
      HQRH_ADJUSTAMOUNT
     WHEN HQRH_ADJUSTAMOUNT>0 THEN
      HQRH_ADJUSTAMOUNT
     WHEN HQRH_DISCRADJUSTMENT<0 THEN
      HQRH_DISCRADJUSTMENT * (-1)
   END 
  FROM HQ_CASHRECONCILEHEADER, HQ_BADGE
  WHERE HQ_CASHRECONCILEHEADER.HQRH_PLAZANO = HQ_BADGE.HQBD_PLAZANO
and HQ_CASHRECONCILEHEADER.HQRH_BADGENO = HQ_BADGE.HQBD_BADGENO
AND (HQ_CASHRECONCILEHEADER.HQRH_OPERATIONALDATE =
   TO_DATE(:dtstart, 'DD/MM/YYYY'))
AND (HQ_CASHRECONCILEHEADER.HQRH_PLAZANO = :plazano);

The case will return null if all else fails, you can adjust this. The biggest problem is that once you provide the case with a parameter it will use it to compare in the when statements. In the above you are able to compare different conditions