Can you create a calculated field using alternate column titles 1

[dianne2n]

Is it possible to create a calculated field using the alternate column names?
I’m getting an “PROJECTED_OUTPROC_COST - Invalid Identifier” error. Here’s my statement from Oracle 9

SELECT
JOB_ID,
CUSTOMER_NO,
(Std_Dept_Cost + Std_RawMtl_Cost +
Outside_Processing_Cost +
(Std_RawMtl_Cost *0.04 +
Outside_Processing_Cost * 0.11) ) Total_Std_Cost,
(case
when Actual_Dept_Cost >= Std_Dept_Cost
then Actual_Dept_Cost
else Std_Dept_Cost
end) Projected_Dept_Cost,
(case
when actual_rawmtl_Cost >= Std_RawMtl_Cost
then actual_RawMtl_Cost
else std_RawMtl_Cost
end) Projected_RawMtl_Cost,
(case
when actual_OutProcessing_Cost >= Outside_Processing_Cost then
Actual_Outprocessing_Cost
else Outside_Processing_Cost
end) Projected_OutProc_Cost,
(Projected_Dept_Cost + Projected_RawMtl_Cost +
Projected_OutProc_Cost +(Projected_RawMtl_Cost * 0.04 +
Projected_OutProc_Cost * 0.11)) Total_Projected_Cost,
ORDER_LINE_VALUE
FROM
MyTable
WHERE
CONTRACT = '&Site' and
(STATUS <> 'Invoiced/Closed' AND
STATUS <> 'Cancelled')

 

[SantaMufasa]

Dianne,

Yes, you can use this little trick (that leaves your original SELECT nearly intact): In-line Views. (My additional code is in bold font:

[B]SELECT JOB_ID, CUSTOMER_NO, Total_Std_Cost, Projected_Dept_Cost, Projected_RawMtl_Cost, Projected_OutProc_Cost,
(Projected_Dept_Cost + Projected_RawMtl_Cost +
Projected_OutProc_Cost +(Projected_RawMtl_Cost * 0.04 +
Projected_OutProc_Cost * 0.11)) Total_Projected_Cost,
ORDER_LINE_VALUE
from ([/b]
SELECT 
JOB_ID, 
CUSTOMER_NO, 
(Std_Dept_Cost + Std_RawMtl_Cost +
Outside_Processing_Cost +
(Std_RawMtl_Cost *0.04 + 
Outside_Processing_Cost * 0.11) ) Total_Std_Cost,
(case
when Actual_Dept_Cost >= Std_Dept_Cost
then Actual_Dept_Cost
else Std_Dept_Cost
end) Projected_Dept_Cost,
(case 
when actual_rawmtl_Cost >= Std_RawMtl_Cost
then actual_RawMtl_Cost
else std_RawMtl_Cost
end) Projected_RawMtl_Cost,
(case
when actual_OutProcessing_Cost >= Outside_Processing_Cost then
Actual_Outprocessing_Cost
else Outside_Processing_Cost
end) Projected_OutProc_Cost,
[B]/*[/B] -- Commented since this expression floats up
(Projected_Dept_Cost + Projected_RawMtl_Cost +
Projected_OutProc_Cost +(Projected_RawMtl_Cost * 0.04 +
Projected_OutProc_Cost * 0.11)) Total_Projected_Cost,
[B]*/[/B]
ORDER_LINE_VALUE
FROM
MyTable
WHERE
CONTRACT = '&Site' and
(STATUS <> 'Invoiced/Closed' AND
STATUS <> 'Cancelled')
[b]);[/b]

The added pieces are 1) an initial SELECT, 2) commenting out your arithmetic expression and 3) closing paren.

Let us know if this resolves your need.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA @ 21:06 (28Sep04) UTC (aka "GMT" and "Zulu"), 14:06 (28Sep04) Mountain Time)

 

[LKBrwnDBA]

Only if you do it like this:

SELECT 
	JOB_ID, 
	CUSTOMER_NO, 
	Total_Std_Cost,
	Projected_Dept_Cost,
	Projected_RawMtl_Cost,
	Projected_OutProc_Cost,
       (Projected_Dept_Cost + Projected_RawMtl_Cost 
       +Projected_OutProc_Cost 
       +(Projected_RawMtl_Cost * 0.04 +
	Projected_OutProc_Cost * 0.11))
           Total_Projected_Cost,
	ORDER_LINE_VALUE
FROM (
SELECT 
	JOB_ID, 
	CUSTOMER_NO, 
	(Std_Dept_Cost + Std_RawMtl_Cost 
	+Outside_Processing_Cost 
	+(Std_RawMtl_Cost *0.04 
	+Outside_Processing_Cost * 0.11) ) Total_Std_Cost,
	(case
	 when Actual_Dept_Cost >= Std_Dept_Cost
	 then Actual_Dept_Cost
	 else Std_Dept_Cost
	 end) Projected_Dept_Cost,
	(case 
	 when actual_rawmtl_Cost >= Std_RawMtl_Cost
	 then actual_RawMtl_Cost
	 else std_RawMtl_Cost
	 end) Projected_RawMtl_Cost,
	(case
	 when actual_OutProcessing_Cost >= Outside_Processing_Cost 
	 then Actual_Outprocessing_Cost
	 else Outside_Processing_Cost
	 end) Projected_OutProc_Cost,
	ORDER_LINE_VALUE
FROM
	MyTable
WHERE
	CONTRACT = '&Site'
  AND   (STATUS <> 'Invoiced/Closed' 
  AND    STATUS <> 'Cancelled')

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

LK,

For completeness, we should note the need for an additional closing ")" on your code.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:22 (28Sep04) UTC (aka "GMT" and "Zulu"), 14:22 (28Sep04) Mountain Time)

 

[dianne2n]

Thank you for those quick responses. The double select works perfectly! You're awsome!