easy SQL question (but not for me)

[slechols]

Can you reference a calculated field in the same view that it was calculated?

SELECT
g.niin,
CASE
when (e.total_needs + (m.mdr*6)) is null THEN
g.FUD
ELSE
(g.FUD - (e.total_needs + m.mdr*6))
END CASE
FROM
G072_VIEW g,
EXPRESS e,
MDR_VIEW m
WHERE
g.niin = e.niin AND g.niin = m.niin

 

[slechols]

CREATE OR REPLACE VIEW EXCESS_VIEW (NIIN,EXCESS)
AS
SELECT
g.niin,
CASE
when (e.total_needs + (m.mdr*6)) is null THEN
g.FUD
ELSE
(g.FUD - (e.total_needs + m.mdr*6))
END CASE
FROM
G072_VIEW g,
EXPRESS e,
MDR_VIEW m
WHERE
g.niin = e.niin AND g.niin = m.niin

I need to use EXCESS to calculate other fields.

 

[carp]

How about simplifying this to:

SELECT
g.niin,g.FUD - NVL((e.total_needs + m.mdr*6),0)
FROM
G072_VIEW g,
EXPRESS e,
MDR_VIEW m
WHERE
g.niin = e.niin AND g.niin = m.niin;

 

[slechols]

Can I reference "EXCESS"(g.FUD-NVL((e.total_needs +m.mdr*6),0)) as "EXCESS" anywhere else in this query? I need to reference this number and another calculated number to derive 23 other fields.

 

[carp]

If you alias the column, you can reference it in your ORDER BY clause, but that's about it.