How to do a case statement on a Calculated Field

Toad for Oracle & DevOps Toolkit
1

I am using TDP version 5.0.0.694 64 bit

I am trying to build a statement based on certain criteria - one being a Calculated field. It appears it does not recognize the fields alias and wants to input the code behind it instead. Is there a way to reference the result in the field and not the code behind the result?

In the case below "CheckComplete" is the Computed field. CheckComplete tells me if there is enough inventory on hand to fulfill that line request and returns a 'YES'. If not, it is Null. I am trying to see if there are the same amount of records for a particular Unique field (SO_ID) below and CheckComplete. I am also assuming that Count(distinct CheckComplete) will not return nulls.

CASE

WHEN SO_HDR.SO_NOTES_NT LIKE '%ASAP%' AND INVT.ON_HAND_QTY <0

OR SO_HDR.SO_NOTES_NT LIKE '%RED%' AND INVT.ON_HAND_QTY <0

OR SO_HDR.SO_NOTES_NT LIKE '%COMPLETE%' AND
COUNT(DISTINCT "SO_ID" )=COUNT(DISTINCT "CHECKCOMPLETE")

Thanks!

2

Is it possible to try this with CheckComplete not having a NULL value? e.g. have you tested with your calculated field returning YES or NO (vs. YES or NULL)? COUNT() may do funny things with NULL values.

3

push the calculated field up to a WITH, then CASE on it in your main query.

WITH data AS 
  ( your select query with calculated fields goes here.  Be sure to alias all calculated columns and add as much where clause as you can up here to limit rows.)
SELECT statement-with-case-goes-here
FROM  data
4

If the calculated field is in the same select statement as your case statement you can not use the alias name as it is not calculated yet. Either your calculated field must be in an inner query (so it is pre calculated), a With statement, already suggested by John (another way of pre-calculating), or you have to repeat the calculation inside your case statement (which is what it is already doing). This independent of Toad, any SQL code will have this problem.