Division Calc in Column Returns 0's or 1's

I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
calculated in a previous grouped view (View1). The column in View2 that
devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
record... but they should be actual numbers instead.
The divide column in View2 looks like this: Number2 / Number 1
The SQL statement looks like this:
SELECT Number1, Number2, Number2 / Number1 AS Expr1
FROM dbo.View1
What do you think is going on? Is SQL Server getting the DataTypes mixed
up?
Thank You!!
Scott Buerkley
The Source For Premium Newsgroup Access
Great Speed, Great Retention
1 GB/Day for only $8.95If your numbers are INTs then you're getting integer divisions. For
example:
SELECT 1/2
returns 0.
Try casting one of them to NUMERIC:
SELECT Number1, Number2, CAST(Number2 AS NUMERIC(10, 5)) / Number1 AS
Expr1
FROM dbo.View1
"Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
>calculated in a previous grouped view (View1). The column in View2 that
>devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
>record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
> --
> The Source For Premium Newsgroup Access
> Great Speed, Great Retention
> 1 GB/Day for only $8.95|||Hard to know for sure without DDL and sample data, but the most likely cause
is that Number1 and Number 2 are bot integer types (int or smallint, etc).
Then SQL does an integer divide and always returns an integer result.
Change one of them to a type which can have a decimal part (float, or
decimal, etc) before doing the divide, something like
SELECT Number1, Number2, (Cast Number2 As Float) / Number1 As Expr1
Tom
"Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
>calculated in a previous grouped view (View1). The column in View2 that
>devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
>record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
> --
> The Source For Premium Newsgroup Access
> Great Speed, Great Retention
> 1 GB/Day for only $8.95|||Scott Buerkley wrote:
> I am dividing 2 numbers in a view (View2). The 2 numbers we sums of numbe
r
> calculated in a previous grouped view (View1). The column in View2 that
> devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
> record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
>
You're dividing two integers, coming up with a fractional value, and SQL
is rounding it to return it as an integer.|||Yes, the 2 numbers were integers and this worked!!
Thx,
Scott Buerkley
"Mike C#" <xyz@.xyz.com> wrote in message
news:ODvlDeimGHA.464@.TK2MSFTNGP05.phx.gbl...
> If your numbers are INTs then you're getting integer divisions. For
> example:
> SELECT 1/2
> returns 0.
> Try casting one of them to NUMERIC:
> SELECT Number1, Number2, CAST(Number2 AS NUMERIC(10, 5)) / Number1 AS
> Expr1
> FROM dbo.View1
> "Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
> news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>
The Source For Premium Newsgroup Access
Great Speed, Great Retention
1 GB/Day for only $8.95|||Yes, you were all correct. The 2 numbers were integers.
It is working now. Thanks for your help!!
Thx,
Scott Buerkley
"Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
>calculated in a previous grouped view (View1). The column in View2 that
>devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
>record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
> --
> The Source For Premium Newsgroup Access
> Great Speed, Great Retention
> 1 GB/Day for only $8.95
The Source For Premium Newsgroup Access
Great Speed, Great Retention
1 GB/Day for only $8.95

Ditinct Rows Within Grouped Dataset

Hi
Here's one that is puzzling me!!
We have 2 tables, a category table and a sub category table, what we
need is to display a web control that shows unique entries within the
recordset so if the recorset returns the follow
Accomodation Hotels
Accomodation B&B
Accomodation Self Catering
Restaurants American
Restaurants Indian
we would then see
Accomodation Hotels
B&B
Self Catering
Restaurants American
Indian
To create the data we are using a stored procedure so woncered if this
type of array could be returned direct from that? Sort of DIsTINCT
within the column. Or would we have to do it in code into an array?
We would then have a web control that allows each of the trees to be
collapsed / expanded
Cheers
shaunshaunsizen@.msn.com wrote:
> Hi
> Here's one that is puzzling me!!
> We have 2 tables, a category table and a sub category table, what we
> need is to display a web control that shows unique entries within the
> recordset so if the recorset returns the follow
> Accomodation Hotels
> Accomodation B&B
> Accomodation Self Catering
> Restaurants American
> Restaurants Indian
> we would then see
> Accomodation Hotels
> B&B
> Self Catering
> Restaurants American
> Indian
> To create the data we are using a stored procedure so woncered if this
> type of array could be returned direct from that? Sort of DIsTINCT
> within the column. Or would we have to do it in code into an array?
> We would then have a web control that allows each of the trees to be
> collapsed / expanded
> Cheers
> shaun
If you have a control driving the results, then wouldn't you want the
results to include the first column in all cases, so the code knows what
tree to place the second column? If you key off the first column, then
the order of the results is irrelevant and you can remove any ORDER BY
clauses in the SQL, saving additional server resources.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||On 4 Oct 2005 06:59:13 -0700, shaunsizen@.msn.com wrote:
>Hi
>Here's one that is puzzling me!!
>We have 2 tables, a category table and a sub category table, what we
>need is to display a web control that shows unique entries within the
>recordset so if the recorset returns the follow
>Accomodation Hotels
>Accomodation B&B
>Accomodation Self Catering
>Restaurants American
>Restaurants Indian
>we would then see
>Accomodation Hotels
> B&B
> Self Catering
>Restaurants American
> Indian
Hi shaun,
Typically, presentation issues should be handled at the front end (it's
not called "presentation tier" for nothing <g>). Use a query such as the
one below to return the data. Then use the frontend (where you'll have
to loop through the rows anyway) to blank out the first column if the
value is unchanged from the previous row.
SELECT FirstCol, SecondCol
FROM YourTable
ORDER BY FirstCol
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)