Comparison of values in CASE statements

[elsenorjose]

Hello gurus,

I am trying to write a query using 3 CASE statements to sum amounts for charges, payments, and adjustments and I want to then compare the values to find records where there is a $0 payments amount and adjustments = charges. My attempt at this results in an error message:

ORA-00904: "CHARGES": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 22 Column: 19

My SQL query:

SELECT DISTINCT
  TDL.TX_ID,
  TDL.ORIG_SERVICE_DATE,
  TDL.ORIG_POST_DATE,
  TDL.TX_NUM,
  TDL.PROC_ID,
  TDL.DETAIL_TYPE,
  CLARITY.ACCOUNT.ACCOUNT_ID,
  CASE WHEN TDL.DETAIL_TYPE IN (1, 10) THEN SUM(AMOUNT) END AS CHARGES,
  CASE WHEN TDL.DETAIL_TYPE IN (2, 5, 11, 20, 22, 32, 33) THEN SUM(AMOUNT) END AS PAYMENTS,
  CASE WHEN TDL.DETAIL_TYPE IN (3, 4, 6, 12, 13, 21, 23, 30, 31) THEN SUM(AMOUNT) END AS ADJUSTMENTS
  
FROM
  CLARITY.CLARITY_TDL_TRAN TDL,
  CLARITY.ACCOUNT

WHERE TDL.ACCOUNT_ID = ACCOUNT.ACCOUNT_ID

AND TDL.SERV_AREA_ID  =  10
AND TDL.POST_DATE  BETWEEN  '01-APR-2012'  AND  '30-APR-2012'
AND PAYMENTS = 0
AND (ADJUSTMENTS = CHARGES)
   
GROUP BY
  TDL.TX_ID,
  TDL.ORIG_SERVICE_DATE,
  TDL.ORIG_POST_DATE,
  TDL.TX_NUM,
  TDL.PROC_ID,
  TDL.DETAIL_TYPE,
  CLARITY.ACCOUNT.ACCOUNT_ID

Apparently I cannot do comparisons on the aliases for my CASE statements. Can someone provide direction on how I can accomplish my desired result?

Using Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi.

Thank you.

 

[Turkbear]

One problem I see it that you will not have any values ( or aliases) for any CASE statement that is not met,
that is, if the detail_type is NOT IN 1 0r 10, the alias CHARGES will not be created so it cannot be 'found' by the parser.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[IanWaterman]

It might be a red herring but I always use my sum around the case

SELECT DISTINCT
TDL.TX_ID,
TDL.ORIG_SERVICE_DATE,
TDL.ORIG_POST_DATE,
TDL.TX_NUM,
TDL.PROC_ID,
TDL.DETAIL_TYPE,
CLARITY.ACCOUNT.ACCOUNT_ID,
sum(CASE WHEN TDL.DETAIL_TYPE IN (1, 10) THEN AMOUNT END) AS CHARGES,
sum(CASE WHEN TDL.DETAIL_TYPE IN (2, 5, 11, 20, 22, 32, 33) THEN AMOUNT END) AS PAYMENTS,
sum(CASE WHEN TDL.DETAIL_TYPE IN (3, 4, 6, 12, 13, 21, 23, 30, 31) THEN AMOUNT END) AS ADJUSTMENTS

Ian

 

[SantaMufasa]

Folks,

Expression aliases (such as ElsenorJose's CHARGES, PAYMENTS, and ADJUSTMENTS, above) are visible only to an outer SELECT or to the inner SELECT's ORDER BY clause. In illustration:

  select last_name, salary-(salary * .23) net_pay
    from s_emp
   where net_pay > 1500;

 where net_pay > 1500
       *
ERROR at line 3:
ORA-00904: "NET_PAY": invalid identifier

So to make an alias visible, simply define the alias in an inner query, then it is visible in the outer query:

select *
  from (select last_name, salary-(salary * .23) net_pay
          from s_emp)
 where net_pay > 1500;

LAST_NAME               NET_PAY
-------------------- ----------
Velasquez                  1925

1 row selected.

Let me know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[IanWaterman]

Mufasa

I did not spot the

AND PAYMENTS = 0
AND (ADJUSTMENTS = CHARGES)

Doh!

I would deal with it this way, is their a better way?

SELECT DISTINCT
  TDL.TX_ID,
  TDL.ORIG_SERVICE_DATE,
  TDL.ORIG_POST_DATE,
  TDL.TX_NUM,
  TDL.PROC_ID,
  TDL.DETAIL_TYPE,
  CLARITY.ACCOUNT.ACCOUNT_ID,
  sum(CASE WHEN TDL.DETAIL_TYPE IN (1, 10) THEN AMOUNT END) AS CHARGES,
sum(CASE WHEN TDL.DETAIL_TYPE IN (2, 5, 11, 20, 22, 32, 33) THEN AMOUNT END) AS PAYMENTS,
sum(CASE WHEN TDL.DETAIL_TYPE IN (3, 4, 6, 12, 13, 21, 23, 30, 31) THEN AMOUNT END) AS ADJUSTMENTS
FROM
  CLARITY.CLARITY_TDL_TRAN TDL,
  CLARITY.ACCOUNT
WHERE TDL.ACCOUNT_ID = ACCOUNT.ACCOUNT_ID
AND TDL.SERV_AREA_ID  =  10
AND TDL.POST_DATE  BETWEEN  '01-APR-2012'  AND  '30-APR-2012'
GROUP BY
  TDL.TX_ID,
  TDL.ORIG_SERVICE_DATE,
  TDL.ORIG_POST_DATE,
  TDL.TX_NUM,
  TDL.PROC_ID,
  TDL.DETAIL_TYPE,
  CLARITY.ACCOUNT.ACCOUNT_ID 
HAVING
sum(CASE WHEN TDL.DETAIL_TYPE IN (2, 5, 11, 20, 22, 32, 33) THEN AMOUNT END) = 0
AND sum(CASE WHEN TDL.DETAIL_TYPE IN (1, 10) THEN AMOUNT END) = sum(CASE WHEN TDL.DETAIL_TYPE IN (3, 4, 6, 12, 13, 21, 23, 30, 31) THEN AMOUNT END)

Ian

 

[cmmrfrds]

Hi, this is an alternative approach. I see you are using Epic's Clarity database. I have worked with the Clarity database for a number of years and the tdl_tran table can be extremely large and the queries take a lot of resources. Are you aware that Epic summarizes the tdl_tran information into a table called arpb_transactions where the data you are looking for is already summarized. This table is much smaller and less resouce intensive. If you don't see it in the database, you can ask your ETL admin to extract it for you.

 

[SantaMufasa]

I would deal with it this way, is their a better way?

Ian, I'm not sure which it you are referring to. But I will re-iterate that in your code, above, you cannot refer to CHARGES, PAYMENTS, or ADJUSTMENTS in either the SELECT clause, the WHERE clause, the GROUP BY clause, or the HAVING clause. If you added an ORDER BY clause, you can successfully refer to aliases.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[elsenorjose]

Thank you all for your input. It's been a hectic few weeks and I hadn't had time to check in. The report got reassigned so I'm no longer working on it but I appreciate the tips.

 

[Beilstwh]

FYI,
You can control display of aggregates by using a having clause

Bill
Lead Application Developer
New York State, USA