What do you mean that you expected "the results to be formatted like
y.xxxx"? What is "y.xxxx"? Could you please clarify what formatting you
wanted with a few rows of data that respresent what you expected? I can't
tell from your wording whether you are talking about the second column or
the third (or even the first!).

I'm going to guess that you're talking about the third column, although I'm
not quite sure why you'd arbitrarily want to put 0.0000 in the third column
for every row of the result set. If you actually want "0.0000" in the third
column of every row, you could get that with any of the following
expressions in the definition of the third column of the view:
- '0.0000' [this would be a VARCHAR(6), not a number]
- 0.0000 [this would be a DECIMAL(5,4)]
- 0000.0000 [this would be a DECIMAL(8,4)]
- cast (0 as dec(8,4)) [this would be a DECIMAL(8,4)]

If that is not what you mean, please clarify the question, particularly your
expectation for which column should be formatted and how you want the result
to appear.

Rhino

CAST statements in view definitions have no effect.  The rows are formatted
based on the variable types used in the SELECT statement (ie, those of the
underlying table's columns or those implied as a result of caluclations.)

What you really want to do is use them in your select statement instead,
like this:

select bhid, cast(index_1 as decimal(8,4)), cast(index_2 as decimal(8,4))
from is3.base_index
where index_1 is not null
order by index_1

If you want NULL values to show up as zeroes (instead of "(null)" or "-"),
then you should consider using the COALESCE function, like this:

select bhid, cast(index_1 as decimal(8,4)), cast(coalesce(index_2,0) as
decimal(8,4)) from is3.base_index
where index_1 is not null
order by index_1

--
Matt Emmerton