I don't understand results

[Bob Stearns]

I created a view:

create view is3.base_index( bhid, index_1, index_2)
as select bhid,
cast(0+1.5*birt h_epd+2.5*weani ng_epd as decimal(8,4)),
cast(null as decimal(8,4))
from is3.epd

then selected from it:

select * from is3.base_index
where index_1 is not null
order by index_1

expecting the results to be formatted like y.xxxx. What I got is below.
Why is the formatting not what I expected? Is there an easy to force
what I want to see?

140 -32.4 (null)
104162 -31.95 (null)
156973 -31.45 (null)
106005 -31.25 (null)
104002 -30.45 (null)
153400 -30.4 (null)
103786 -28.35 (null)
172478 -28.2 (null)
156044 -28.15 (null)
103294 -27 (null)
103560 -26.75 (null)
105396 -26.5 (null)

[Rhino]

"Bob Stearns" <rs**********@c harter.net> wrote in message
news:Qv******** *********@fe04. lga...

I created a view:

create view is3.base_index( bhid, index_1, index_2)
as select bhid,
cast(0+1.5*birt h_epd+2.5*weani ng_epd as decimal(8,4)),
cast(null as decimal(8,4))
from is3.epd

then selected from it:

select * from is3.base_index
where index_1 is not null
order by index_1

expecting the results to be formatted like y.xxxx. What I got is below.
Why is the formatting not what I expected? Is there an easy to force
what I want to see?

140 -32.4 (null)
104162 -31.95 (null)
156973 -31.45 (null)
106005 -31.25 (null)
104002 -30.45 (null)
153400 -30.4 (null)
103786 -28.35 (null)
172478 -28.2 (null)
156044 -28.15 (null)
103294 -27 (null)
103560 -26.75 (null)
105396 -26.5 (null)

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

[Matt Emmerton]

"Bob Stearns" <rs**********@c harter.net> wrote in message
news:Qv******** *********@fe04. lga...

I created a view:

create view is3.base_index( bhid, index_1, index_2)
as select bhid,
cast(0+1.5*birt h_epd+2.5*weani ng_epd as decimal(8,4)),
cast(null as decimal(8,4))
from is3.epd

then selected from it:

select * from is3.base_index
where index_1 is not null
order by index_1

expecting the results to be formatted like y.xxxx. What I got is below.
Why is the formatting not what I expected? Is there an easy to force
what I want to see?

140 -32.4 (null)
104162 -31.95 (null)
156973 -31.45 (null)
106005 -31.25 (null)
104002 -30.45 (null)
153400 -30.4 (null)
103786 -28.35 (null)
172478 -28.2 (null)
156044 -28.15 (null)
103294 -27 (null)
103560 -26.75 (null)
105396 -26.5 (null)

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(i ndex_2,0) as
decimal(8,4)) from is3.base_index
where index_1 is not null
order by index_1

--
Matt Emmerton

[Bob Stearns]

Rhino wrote:

"Bob Stearns" <rs**********@c harter.net> wrote in message
news:Qv******** *********@fe04. lga...

I created a view:

create view is3.base_index( bhid, index_1, index_2)
as select bhid,
cast(0+1.5*birt h_epd+2.5*weani ng_epd as decimal(8,4)),
cast(null as decimal(8,4))
from is3.epd

then selected from it:

select * from is3.base_index
where index_1 is not null
order by index_1

expecting the results to be formatted like y.xxxx. What I got is below.
Why is the formatting not what I expected? Is there an easy to force
what I want to see?

140 -32.4 (null)
104162 -31.95 (null)
156973 -31.45 (null)
106005 -31.25 (null)
104002 -30.45 (null)
153400 -30.4 (null)
103786 -28.35 (null)
172478 -28.2 (null)
156044 -28.15 (null)
103294 -27 (null)
103560 -26.75 (null)
105396 -26.5 (null)

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

I mean, of course the second column. What I was expecting was:
140 -32.4000 (null)
104162 -31.9500 (null)
156973 -31.4500 (null)
106005 -31.2500 (null)
104002 -30.4500 (null)
153400 -30.4000 (null)
103786 -28.3500 (null)
172478 -28.2000 (null)
156044 -28.1500 (null)
103294 -27.0000 (null)
103560 -26.7500 (null)
105396 -26.5000 (null)

[Bob Stearns]

Matt Emmerton wrote:

"Bob Stearns" <rs**********@c harter.net> wrote in message
news:Qv******** *********@fe04. lga...

I created a view:

create view is3.base_index( bhid, index_1, index_2)
as select bhid,
cast(0+1.5*birt h_epd+2.5*weani ng_epd as decimal(8,4)),
cast(null as decimal(8,4))
from is3.epd

then selected from it:

select * from is3.base_index
where index_1 is not null
order by index_1

expecting the results to be formatted like y.xxxx. What I got is below.
Why is the formatting not what I expected? Is there an easy to force
what I want to see?

140 -32.4 (null)
104162 -31.95 (null)
156973 -31.45 (null)
106005 -31.25 (null)
104002 -30.45 (null)
153400 -30.4 (null)
103786 -28.35 (null)
172478 -28.2 (null)
156044 -28.15 (null)
103294 -27 (null)
103560 -26.75 (null)
105396 -26.5 (null)

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(i ndex_2,0) as
decimal(8,4)) from is3.base_index
where index_1 is not null
order by index_1

--
Matt Emmerton

Thank you. I didn't realize the CASTs in the VIEW were ignored. Perhaps
they should be diagnosed as warnings at least.

[Rhino]

"Bob Stearns" <rs**********@c harter.net> wrote in message
news:nT******** *******@fe06.lg a...

Rhino wrote:

"Bob Stearns" <rs**********@c harter.net> wrote in message
news:Qv******** *********@fe04. lga...

I created a view:

create view is3.base_index( bhid, index_1, index_2)
as select bhid,
cast(0+1.5*birt h_epd+2.5*weani ng_epd as decimal(8,4)),
cast(null as decimal(8,4))
from is3.epd

then selected from it:

select * from is3.base_index
where index_1 is not null
order by index_1

expecting the results to be formatted like y.xxxx. What I got is below.
Why is the formatting not what I expected? Is there an easy to force
what I want to see?

140 -32.4 (null)
104162 -31.95 (null)
156973 -31.45 (null)
106005 -31.25 (null)
104002 -30.45 (null)
153400 -30.4 (null)
103786 -28.35 (null)
172478 -28.2 (null)
156044 -28.15 (null)
103294 -27 (null)
103560 -26.75 (null)
105396 -26.5 (null)

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

I mean, of course the second column.

[snip]

Then perhaps you should have made that clear in your original question. It
certainly wasn't obvious to *me* that you meant the second column.

Rhino

[Matt Emmerton]

"Bob Stearns" <rs**********@c harter.net> wrote in message
news:nZ******** *******@fe06.lg a...

Matt Emmerton wrote:

"Bob Stearns" <rs**********@c harter.net> wrote in message
news:Qv******** *********@fe04. lga...

I created a view:

create view is3.base_index( bhid, index_1, index_2)
as select bhid,
cast(0+1.5*birt h_epd+2.5*weani ng_epd as decimal(8,4)),
cast(null as decimal(8,4))
from is3.epd

then selected from it:

select * from is3.base_index
where index_1 is not null
order by index_1

expecting the results to be formatted like y.xxxx. What I got is below.
Why is the formatting not what I expected? Is there an easy to force
what I want to see?

140 -32.4 (null)
104162 -31.95 (null)
156973 -31.45 (null)
106005 -31.25 (null)
104002 -30.45 (null)
153400 -30.4 (null)
103786 -28.35 (null)
172478 -28.2 (null)
156044 -28.15 (null)
103294 -27 (null)
103560 -26.75 (null)
105396 -26.5 (null)

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(i ndex_2,0) as
decimal(8,4)) from is3.base_index
where index_1 is not null
order by index_1

--
Matt Emmerton

Thank you. I didn't realize the CASTs in the VIEW were ignored. Perhaps
they should be diagnosed as warnings at least.

Actually, I was wrong. CASTs defined in a VIEW are valid and are NOT
ignored, so the original query was fine.

However, note that a CAST merely changes DB2's internal handling of the
column type, and does not neccessarily change how the output of the data is
formatted. Formatting of the output data is the responsiblity of the client
application. The DB2 CLP is a database client and has it's own ways of
formatting the output of queries.

--
Matt Emmerton