problems with case statement

create table lab_results
(result numeric(19,6),
PQL numeric(19,6),
result_text varchar(50),
PQL_text varchar(50),
sig_figs int))
I have 16000 rows of data, and I am having trouble updating the PQL_text
field.
This query returns all 16000 rows
SELECT result,
CASE
WHEN sig_figs + floor(-log10(abs(result))) < 0 THEN
-- The result will have trailing zeros - round first and then
use str
STR(round(result, cast(sig_figs + floor(-log10(abs(result))) as
int)), 10, 0)
ELSE
-- The result will have no trailing zeros - use str directly
STR(result, 10, cast(sig_figs + floor(-log10(abs(result))) as
int))
END
FROM lab_results
but this query only returns 2193 rows
SELECT PQL,
CASE
WHEN sig_figs + floor(-log10(abs(PQL))) < 0 THEN
-- The result will have trailing zeros - round first and then
use str
STR(round(PQL, cast(sig_figs + floor(-log10(abs(PQL))) as int)),
10, 0)
ELSE
-- The result will have no trailing zeros - use str directly
STR(PQL, 10, cast(sig_figs + floor(-log10(abs(PQL))) as int))
END
FROM lab_results
The only thing that I am changing is the result and PQL fields.
WHy does the second query not return all rows.
Thanks for any help.
ArcherYou do not have a Where clause in either query, so, both shopuld return 1 ro
w
for each record in the table. If teh second one returned only 2139 records,
then either it threw an error somewhre in the middle, or you're pointing to
a
different database...
"bagman3rd" wrote:

> create table lab_results
> (result numeric(19,6),
> PQL numeric(19,6),
> result_text varchar(50),
> PQL_text varchar(50),
> sig_figs int))
> I have 16000 rows of data, and I am having trouble updating the PQL_text
> field.
> This query returns all 16000 rows
> SELECT result,
> CASE
> WHEN sig_figs + floor(-log10(abs(result))) < 0 THEN
> -- The result will have trailing zeros - round first and then
> use str
> STR(round(result, cast(sig_figs + floor(-log10(abs(result))) a
s
> int)), 10, 0)
> ELSE
> -- The result will have no trailing zeros - use str directly
> STR(result, 10, cast(sig_figs + floor(-log10(abs(result))) as
> int))
> END
> FROM lab_results
> but this query only returns 2193 rows
> SELECT PQL,
> CASE
> WHEN sig_figs + floor(-log10(abs(PQL))) < 0 THEN
> -- The result will have trailing zeros - round first and then
> use str
> STR(round(PQL, cast(sig_figs + floor(-log10(abs(PQL))) as int)
),
> 10, 0)
> ELSE
> -- The result will have no trailing zeros - use str directly
> STR(PQL, 10, cast(sig_figs + floor(-log10(abs(PQL))) as int))
> END
> FROM lab_results
> The only thing that I am changing is the result and PQL fields.
> WHy does the second query not return all rows.
> Thanks for any help.
> Archer
>|||I know that it is pointing to the right database and I just double checked
that.
What would the error possibly be?
I have been able to update the result_text field, but when I try ot update
the PQL_text field, I get this error message:
A domain error occurred.
A
"CBretana" wrote:
> You do not have a Where clause in either query, so, both shopuld return 1
row
> for each record in the table. If teh second one returned only 2139 record
s,
> then either it threw an error somewhre in the middle, or you're pointing t
o a
> different database...
> "bagman3rd" wrote:
>|||I figured it out. I had 0 (zero) values in my PQL field which should have
been nulls in the first place. The query was getting all rows until it hit
a
0, then blew up.
Thanks.
A
"bagman3rd" wrote:
> I know that it is pointing to the right database and I just double checked
> that.
> What would the error possibly be?
> I have been able to update the result_text field, but when I try ot update
> the PQL_text field, I get this error message:
> A domain error occurred.
> A
> "CBretana" wrote:
>|||Why are you doing formatting in the database, in violation of the basic
principle of a tiered architecture?
Rows are not records and fields are not columns. But you are still
thinking as if you were writing COBOL or some other 3GL language that
uses a PICTURE() model of data internally.|||So, if you ever use the word field instead of column you are automatically
thinking in terms of a 3GL langauge instead of sets? Funny, I notice that
the SQL Server BOL regularly refers to fields instead of columns. At this
point, field and column are synonymous as are row and record.
Thomas
(Does anyone developer worth their salt still code in cobol? Is it some sort
of punishment?)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1112827218.645873.193010@.g14g2000cwa.googlegroups.com...
> Why are you doing formatting in the database, in violation of the basic
> principle of a tiered architecture?
> Rows are not records and fields are not columns. But you are still
> thinking as if you were writing COBOL or some other 3GL language that
> uses a PICTURE() model of data internally.
>|||>> So, if you ever use the word field instead of column you are
automatically thinking in terms of a 3GL langauge instead of sets? <<
The mental model you have does make a difference. You cannot separate
the logical model and the physical implementation, so you start
thinking of sequential storage, cursors and so forth. I have been
teaching SQL for over a decade and I see it all the time.
instead of columns. At this point, field and column are synonymous as
are row and record. <<
"Caesar: Pardon him, Theodotus. He is a barbarian and thinks the
customs of his tribe and island are the laws of nature." - Caesar and
Cleopatra; George Bernard Shaw 1898.
SQL Server is still based on contigous, indexed files under the covers,
so you can say things about the physical files having fields and
records. But that is not the SQL logical model and I agree that MS is
sloppy about documentation. It is not the way that many newer VLDB
products implement their data storage.|||>>> So, if you ever use the word field instead of column you
> automatically thinking in terms of a 3GL langauge instead
> of sets? <<
> The mental model you have does make a difference. You
> cannot separate
> the logical model and the physical implementation, so you
> start
> thinking of sequential storage, cursors and so forth. I
> have been
> teaching SQL for over a decade and I see it all the time.
So, somehow using the word "row" instead of "record" stops
people from thinking about cursors? Somehow using the word
"field" makes people think about sequential storage? That
might have been true back when people had heard of COBOL.
However, in the past ten years, I haven't read a book or
online article where "field" was use in lieu of "column" and
not known which model they were discussing.

> instead of columns. At this point, field and column are
> synonymous as
> are row and record. <<

> "Caesar: Pardon him, Theodotus. He is a barbarian and
> thinks the
> customs of his tribe and island are the laws of nature." -
> Caesar and
> Cleopatra; George Bernard Shaw 1898.
What's in a name? That which we call a rose by any other
name would smell as sweet."
-Shakespeare

> SQL Server is still based on contigous, indexed files
> under the covers,
> so you can say things about the physical files having
> fields and
> records. But that is not the SQL logical model and I
> agree that MS is
> sloppy about documentation. It is not the way that many
> newer VLDB
> products implement their data storage.
You are falsely presuming that when people talk about
"records" that they are somehow referring to the deep dark
innards of the way SQL's designed.
I'd bet that you would find quite a bit of documentation
from a variety of vendors where the word "field" and
"column" are used interchangeably.
Thomas