Computed field references

I am currently developing a stored procedure that includes a number of computed fields. Is it possible to reference a computed value, (eg. FLdA), or do I need to CREATE a temp file and then reference the FldA and FldB values. I have simplified my code, it is much more extensive in that there are numerous WHEN clauses attached to each FldA and FldB computation.

SELECT FldA = CASE
WHEN ... THEN CurQty * 1.5
WHEN ... THEN CurQty * 1.75 ELSE 0 END),
FldB = CASE ....
NewValue = CASE
WHEN ... THEN FldA * CurValue
WHEN ... THEN FldB * CurValue
etc.I'm not sure I understand the question...Do you want to reference the value again inside the sproc?

Then Yes...use a local table variable...

If it's being part of a result set being passed back, then you're already refrencing it...

I'm confused...|||I want to reference the value within the sproc and pass only those records where the OldValue is not equal to the NewValue. In the case I mentioned, I am trying to reference FldA and FldB to compute the NewValue from within the same SELECT stmt, but SQL does not let me reference the FldA and FldB computed values. Is that as clear as mud?|||Reference them, where? In the same query? Or later on in the sproc.

If it's later on in the sproc

SELECT <whatever> INTO #TEMP FROM <whatever>

Then just query the local temp table...

Is that what you mean?|||I'm trying to reference them in the same query.

The INSERT .. INTO stmt seems cumbersome as it appears I would have to define each field as part of the CREATE TABLE stmt. Can't see why it doesn't just pickup the data types from the TABLE.|||Well it's not data type is it...it's column names

Well do this...Keep your computed stuff isolated...and join to a derived table

SELECT * FROM (SELECT <your derived columns> FROM table join table ect) AS A
LEFT JOIN B ON a.key = b.key
WHERE <now you can reference the derived column name> = 'bananas'

Whatever...

I fyou make the derivation this derived table you'll be able to reference the column names you made up...|||Why so complicated?

select * from (
SELECT FldA = CASE
WHEN ... THEN CurQty * 1.5
WHEN ... THEN CurQty * 1.75 ELSE 0 END),
FldB = CASE ....
NewValue = CASE
WHEN ... THEN CASE
WHEN ... THEN CurQty * 1.5
WHEN ... THEN CurQty * 1.75 ELSE 0 END * CurValue
WHEN ... THEN CASE .... * CurValue
) x
where OldValue != NewValue

In other words, instead of trying to reference FldA, use its CASE...END when calculating NewValue. Same with FldB.|||I had mentioned earlier that the code was simplified. The CASE logic is fairly complex, could be up to 20 lines of code. That would mean that I would have to repeat the code everytime the field ('FldA') was referenced. I may just leave the logic in VBA code as it seems a lot easier to manipulate fields in code. My goal was to restrict the query ouput lines so the Access code would run quicker.|||Thanks Brett ... I'll give it a go.|||Here's a model

USE Northwind
GO
SELECT SUM(OutOfBusinessDays) AS VacationDays
FROM (
SELECT ShipLate-ShipDelay AS OutOfBusinessDays
FROM (
SELECT DATEDIFF(dd,OrderDate,ShippedDate) As ShipDelay
, DATEDIFF(dd,OrderDate,RequiredDate) As ShipLate
FROM Orders
) AS XXX
) AS DerivedTableName