Comparing values from 2 case statements

Question

I have the oracle 11g version installed and am practicing on the HR test database package. This question is a much smaller version of what I require:

here is my query:

    Select description, Case_First_Level, Case_Second_Level, Case_Difference
from
 (Select p.country_name as description,
   (case when (l.location_id between '1000' and '1300') then (l.postal_code) else '0' end) as Case_First_Level,
   (case when (l.location_id between '1400' and '1700') then (l.postal_code) else '0' end) as Case_Second_Level,
   (case when 
             (
              (case when l.location_id between '1000' and '1300' then (l.postal_code) else '0' end) > 
              (case when l.location_id between '1400' and '1700' then (l.postal_code) else '0' end)
             )  
    then 1000  else 2000 end) as Case_Difference
 from countries p, locations l
 where l.country_id = p.country_id
 and p.country_id in ('IT', 'US', 'CN')
 group by p.country_name, p.country_id, l.country_id, l.postal_code, l.location_id
)

This query works fine, and I get the following output:

DESCRIPTION            CASE_FIRST_LEVEL   CASE_SECOND_LEVEL   CASE_DIFFERENCE

United States of America    0           26192             2000
China                           0             0           2000
Italy                         10934           0           1000
United States of America    0           50090             2000
Italy                         00989           0           1000
United States of America    0           99236             2000
United States of America    0           98199             2000

Now, I want to replace the values 1000 and 2000 in the Case_Difference column by the values from the conditions, i.e instead of having the static values, I want to replace the value 1000 by the values from (Case_First_Level - Case_Second_Level) and vice versa for the value 2000. Now, since aliases are not recognised here, is there any way to do this?

Thanks

EDIT: Uploaded correct code now.

Answer 1

Try:

SELECT DESCRIPTION,
       CASE_FIRST_LEVEL,
       CASE_SECOND_LEVEL,
       ABS(CASE_FIRST_LEVEL - CASE_SECOND_LEVEL) AS CASE_DIFFERENCE
 FROM (Select p.country_name as description,
              case
                when l.location_id between '1000' and '1300'
                  then l.postal_code
                else '0'
              end as Case_First_Level,
              case
                when l.location_id between '1400' and '1700'
                  then l.postal_code
                else '0'
              end as Case_Second_Level,
       from countries p
       INNER JOIN locations l
         ON l.country_id = p.country_id
       WHERE p.country_id in ('IT', 'US', 'CN')
       group by p.country_name,
                p.country_id,
                l.country_id,
                l.postal_code,
                l.location_id)

Note that the calculation ABS(CASE_FIRST_LEVEL - CASE_SECOND_LEVEL) AS CASE_DIFFERENCE counts on CASE_FIRST_LEVEL and CASE_SECOND_LEVEL being convertible to NUMBER which may not always be the case as they're postal code values (consider, for example, the British postal codes which look something like 'W11 2BQ').

Share and enjoy.