I created a view:
create view is3.base_index(bhid, index_1, index_2)
as select bhid,
cast(0+1.5*birth_epd+2.5*weaning_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) 6 1072
"Bob Stearns" <rs**********@charter.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*birth_epd+2.5*weaning_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
"Bob Stearns" <rs**********@charter.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*birth_epd+2.5*weaning_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(index_2,0) as
decimal(8,4)) from is3.base_index
where index_1 is not null
order by index_1
--
Matt Emmerton
Rhino wrote: "Bob Stearns" <rs**********@charter.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*birth_epd+2.5*weaning_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)
Matt Emmerton wrote: "Bob Stearns" <rs**********@charter.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*birth_epd+2.5*weaning_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(index_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.
"Bob Stearns" <rs**********@charter.net> wrote in message
news:nT***************@fe06.lga... Rhino wrote:
"Bob Stearns" <rs**********@charter.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*birth_epd+2.5*weaning_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
"Bob Stearns" <rs**********@charter.net> wrote in message
news:nZ***************@fe06.lga... Matt Emmerton wrote:
"Bob Stearns" <rs**********@charter.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*birth_epd+2.5*weaning_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(index_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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Uwe Mayer |
last post by:
Hi,
I have a subclassed PyQt class:
class Node(object):
def move(self, x,y): pass
class CRhomb(QCanvasPolygon, Node): pass
$ python
|
by: AFN |
last post by:
I am running the code below to generate XML from a data table. But some
fields in the data table are Null for every record. Suppose field5 has a
null database value. I would expect to see:
...
|
by: LP |
last post by:
I am using (trying to) CR version XI, cascading parameters feature works it
asks user to enter params. But if page is resubmitted. It prompts for params
again. I did set...
|
by: Martin P. Hellwig |
last post by:
Hi all,
I was doing some popen2 tests so that I'm more comfortable using it.
I wrote a little python script to help me test that (testia.py):
---------------------------------
someline =...
|
by: aldonnelley |
last post by:
Hi all.
Just having a weird problem with tkinter. I'm trying to make a gui that
shows results from an image search, with a "forward" and "back" button
so the user can compare results from...
| |
by: =?Utf-8?B?Y2F0aGFyaW51cyB2YW4gZGVyIHdlcmY=?= |
last post by:
Hello,
I have build a website with approximately 30 html-pages.
When I search this website in Google, I see the index.html or home.html on
this website, but also other html-pages on this...
|
by: None |
last post by:
Hi All
These code are part of Prototype. There are something that I don't
understand, would you please explain it for me?
.......
toJSON: function(object) {
var type = typeof object;
switch...
|
by: Stef Mientki |
last post by:
Terry Reedy wrote:
sorry, don't know how this happened, as I always copy/paste ?
AFAIK locals() == sys._getframe(0).f_locals
AFAIK, again one level up
weird, I use it in 2.5 and if I remember...
|
by: OldBirdman |
last post by:
This code is in a Module called from an _Click event for my form named fAAA. Variables beginning ix... are Global Long, declared elsewhere.
Public Sub QuickShow()
On Error GoTo QuickShow_Error...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |