3086/3600 = 0.0 .... WHY Should be 0.857

I have a float field in a table ... when I try and add the result of the following division ... 3086/3600 ... to that field it displays as 0.0.

Can anyone explain why?

Does anyone know how to get around this and get 0.857 into that field instead.

I have tried changing the data type to Decimal(20,2) but this has had no effect.

Any suggestions would be very much appreciated.Solved it ... just do

3086 / CAST(3600 AS FLOAT)

and case solved :)|||Hi , Try this

select 3086/3600.00

will give u the desired result...

Cheers
Gola Munjal
Originally posted by briansy
I have a float field in a table ... when I try and add the result of the following division ... 3086/3600 ... to that field it displays as 0.0.

Can anyone explain why?

Does anyone know how to get around this and get 0.857 into that field instead.

I have tried changing the data type to Decimal(20,2) but this has had no effect.

Any suggestions would be very much appreciated.

3 tables to join

hi,
I knew how to join 2 tables but i have a process to select 3 tables. I have a sample table and field below. I want to join Parts & Orders using field Prt_no and Supplier & Parts using field Sup _code

Parts table Orders Table Supplier Table
Prt_no Prt_no Sup_code
Prt_name Oh_qt Sup_name
Re_Level Or_no
Pri_amnt
Sup_code

Thanks...SELECT
p.Prt_no,
o.Prt_no,
s.Sup_code,
p.Sup_code
FROM
Parts p
INNER JOIN Orders o ON p.Prt_no = o.Prt_no
INNER JOIN Supplier s ON p.Sup_code = s.Sup_code

--I would invest in a good TSQL book if I were you. Teach Yourself Transact-SQL in 21 Days by Sam's Publishing is really good for beginners.|||Where I can get that book?

3 / 2=1? Strange result in expression...HELP!

Hi Folks!

I have a strange thing happening; I have a field that Counts the number of
records and another field that shows the number of clients
(Count(RecordID)=3 and NoOfClients=2) When I do a simple expression
"Count(RecordID)/NoOfClients" the expected result should be 1.5. Instead
the result I get is 1. Any ideas?

Thanks!
RickTry converting one of the values to decimal. For example:
select 3/2 results in 1 BUT
select 3/2. should result in 1.5

Difference is in datatypes...

MC

"Rico" <you@.me.comwrote in message news:BP51h.34135$P7.18677@.edtnps90...

Quote:

Originally Posted by

Hi Folks!
>
I have a strange thing happening; I have a field that Counts the number of
records and another field that shows the number of clients
(Count(RecordID)=3 and NoOfClients=2) When I do a simple expression
"Count(RecordID)/NoOfClients" the expected result should be 1.5. Instead
the result I get is 1. Any ideas?
>
Thanks!
Rick
>
>
>

|||That did the trick. Thanks MC. I thought it had something to do with the
data types, but being a newbie didn't think about "Decimal".

Thanks again!
Rick

"MC" <marko_culoNOSPAM@.yahoo.comwrote in message
news:ei2qf1$a13$1@.ss408.t-com.hr...

Quote:

Originally Posted by

Try converting one of the values to decimal. For example:
select 3/2 results in 1 BUT
select 3/2. should result in 1.5
>
Difference is in datatypes...
>
MC
>
>
"Rico" <you@.me.comwrote in message news:BP51h.34135$P7.18677@.edtnps90...

Quote:

Originally Posted by

>Hi Folks!
>>
>I have a strange thing happening; I have a field that Counts the number
>of
>records and another field that shows the number of clients
>(Count(RecordID)=3 and NoOfClients=2) When I do a simple expression
>"Count(RecordID)/NoOfClients" the expected result should be 1.5. Instead
>the result I get is 1. Any ideas?
>>
>Thanks!
>Rick
>>
>>
>>

>
>

|||Hi Rick,

you are using integers and in integer division 3/2 is 1.
Use floats or cast your numbers to floats in the calculation:

float result=((float)Count(RecordID))/((float)NoOfClients);

Regards,
Andy

Rico schrieb:

Quote:

Originally Posted by

Hi Folks!
>
I have a strange thing happening; I have a field that Counts the number of
records and another field that shows the number of clients
(Count(RecordID)=3 and NoOfClients=2) When I do a simple expression
"Count(RecordID)/NoOfClients" the expected result should be 1.5. Instead
the result I get is 1. Any ideas?
>
Thanks!
Rick

|||Forgot to respond. Thanks! That did the trick!

"MC" <marko_culoNOSPAM@.yahoo.comwrote in message
news:ei2qf1$a13$1@.ss408.t-com.hr...

Quote:

Originally Posted by

Try converting one of the values to decimal. For example:
select 3/2 results in 1 BUT
select 3/2. should result in 1.5
>
Difference is in datatypes...
>
MC
>
>
"Rico" <you@.me.comwrote in message news:BP51h.34135$P7.18677@.edtnps90...

Quote:

Originally Posted by

>Hi Folks!
>>
>I have a strange thing happening; I have a field that Counts the number
>of
>records and another field that shows the number of clients
>(Count(RecordID)=3 and NoOfClients=2) When I do a simple expression
>"Count(RecordID)/NoOfClients" the expected result should be 1.5. Instead
>the result I get is 1. Any ideas?
>>
>Thanks!
>Rick
>>
>>
>>

>
>

2nd POST-How to change color for a visited field?

Hello All,
I have a parameterized report (Report1), which has a field (say
'Control#')that has 'Jump to Report' navigation property setup. When the
user selects their parameters, Report1 displays, say 10 records displayed on
the Report, and when the user clicks on Control# of Report1, it Jumps to
Report2, which displays the details of that Control#. say from 10 records
displayed on Report1 the user cliked on the 5th record to get Report2. after
reviewing that Report, the user would like to go back to Report1. Here, I
want to change the font color of the 5th record Control#, as I have already
viewed it. By doing this, I would be able to make out, which records I
reviewed and which ones I did not. Is there any built in functionality in the
RS to do? Could please someone help me'
Greatly appreciated!There is no built-in feature in RS to magically display the visited field.
Instead, I would log the user visited fields history in the database using
the User!UserId. So, when the user navigates from Report1 to Report 2
(assuming that you pass Control# as parameter) I would base Report 2 on a
stored procedure which will log an entry for that user. Then, the user goes
back to Report 1 you will just need to link the dataset with the visited
history table using User!UserId. Of course, you need at some point to clear
the history, e.g by scheduling a job with the SQL Agent.
--
Hope this helps.
----
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"amma" <amma@.discussions.microsoft.com> wrote in message
news:EDBB4673-4C72-4609-A6EE-106E785653AD@.microsoft.com...
> Hello All,
> I have a parameterized report (Report1), which has a field (say
> 'Control#')that has 'Jump to Report' navigation property setup. When the
> user selects their parameters, Report1 displays, say 10 records displayed
on
> the Report, and when the user clicks on Control# of Report1, it Jumps to
> Report2, which displays the details of that Control#. say from 10 records
> displayed on Report1 the user cliked on the 5th record to get Report2.
after
> reviewing that Report, the user would like to go back to Report1. Here, I
> want to change the font color of the 5th record Control#, as I have
already
> viewed it. By doing this, I would be able to make out, which records I
> reviewed and which ones I did not. Is there any built in functionality in
the
> RS to do? Could please someone help me'
> Greatly appreciated!
>
>

2d Array

hi i need some help can someone say me how to make a 2d Array in a Formula Field ?To create and initialize an array variable in Basic syntax.

In Crystal syntax, you can use the square bracket notation for arrays instead.

Example
The following example is applicable to Basic syntax:

The following sets up an array of days for a week, starting with Monday:

Dim week, firstDay, lastDay

week = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", _

"Saturday", "Sunday")

Rem firstDay contains "Monday"

firstDay = week(1)

Rem lastDay contains "Sunday"

lastDay = week(7)

formula = firstDay

Returns "Monday".|||I'm not sure that answers the 2D array question...

A workaround is to create a single dimensional array with x*y elements. e.g.
multi [3] [6]
single [3 * 6]
multi [x] [y] = single[6*(x-1) + y]

2-3 words in field name

Can I define field names with more words in Access and SQL Server like
field: "Bus station" instead "BusStation" or "Bus_Station"? I have had
problems because of this in VB6. Can I have problems in VB 2005 or C# 2005
and SQL Server?Goran (gzelic@.gmail.com) writes:

Quote:

Originally Posted by

Can I define field names with more words in Access and SQL Server like
field: "Bus station" instead "BusStation" or "Bus_Station"? I have had
problems because of this in VB6. Can I have problems in VB 2005 or C# 2005
and SQL Server?

Yes you can. You need to enclose the name in brackets [] or double quotes ""
whenever you use it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

2006-07-28 00:00:00

This is the result I get in Word as I handle a document that merges data
from the database. The field Date_Fin_Membre returns me this result
even if the query stipulates
WHERE (dbo.Membre.Date_Fin_Membre = CONVERT(Char(10),
dbo.Membre.Date_Fin_Membre, 101)). I have also tried other types of
conversion without any success. I just need the date not the following
hours, minutes and seconds.
thanks
You might be using CONVERT(Char(10), dbo.Membre.Date_Fin_Membre, 101)
only for checking in where clause, you will have to specify the same in
Select columns as well.
something like this:
Select
CONVERT(Char(10), dbo.Membre.Date_Fin_Membre, 101) from XYZTable
Where
dbo.Membre.Date_Fin_Membre = CONVERT(Char(10),
dbo.Membre.Date_Fin_Membre, 101))
Regards,
|||Thanks, it solved my problem.
*** Sent via Developersdex http://www.codecomments.com ***

2006-07-28 00:00:00

This is the result I get in Word as I handle a document that merges data
from the database. The field Date_Fin_Membre returns me this result
even if the query stipulates
WHERE (dbo.Membre.Date_Fin_Membre = CONVERT(Char(10),
dbo.Membre.Date_Fin_Membre, 101)). I have also tried other types of
conversion without any success. I just need the date not the following
hours, minutes and seconds.
thanksYou might be using CONVERT(Char(10), dbo.Membre.Date_Fin_Membre, 101)
only for checking in where clause, you will have to specify the same in
Select columns as well.
something like this:
Select
CONVERT(Char(10), dbo.Membre.Date_Fin_Membre, 101) from XYZTable
Where
dbo.Membre.Date_Fin_Membre = CONVERT(Char(10),
dbo.Membre.Date_Fin_Membre, 101))
Regards,|||Thanks, it solved my problem.
*** Sent via Developersdex http://www.codecomments.com ***

2006-07-28 00:00:00

This is the result I get in Word as I handle a document that merges data
from the database. The field «Date_Fin_Membre» returns me this result
even if the query stipulates
WHERE (dbo.Membre.Date_Fin_Membre = CONVERT(Char(10),
dbo.Membre.Date_Fin_Membre, 101)). I have also tried other types of
conversion without any success. I just need the date not the following
hours, minutes and seconds.
thanksYou might be using CONVERT(Char(10), dbo.Membre.Date_Fin_Membre, 101)
only for checking in where clause, you will have to specify the same in
Select columns as well.
something like this:
Select
CONVERT(Char(10), dbo.Membre.Date_Fin_Membre, 101) from XYZTable
Where
dbo.Membre.Date_Fin_Membre = CONVERT(Char(10),
dbo.Membre.Date_Fin_Membre, 101))
Regards,

2006-07-26 00:00:00

This is the result I get in Word as I handle a document that merges data
from the database. The field Date_Fin_Membre returns me this result
even if the query stipulates
WHERE (dbo.Membre.Date_Fin_Membre = CONVERT(Char(10),
dbo.Membre.Date_Fin_Membre, 101)). I have also tried other types of
conversion without any success.
thanksI haven't worked with OLE automation in Office in some time, but can't
you specify the field type in Word? It soulnd like it's looking for a
date/time formatted field in Word.

HTH,
Stu|||Fernand St-Georges (fernand.st-georges@.videotron.ca) writes:
> This is the result I get in Word as I handle a document that merges data
> from the database. The field Date_Fin_Membre returns me this result
> even if the query stipulates
> WHERE (dbo.Membre.Date_Fin_Membre = CONVERT(Char(10),
> dbo.Membre.Date_Fin_Membre, 101)). I have also tried other types of
> conversion without any success.

Maybe you could explain what's wrong with the result? Maybe it's an
error to you that the end date for a member is in the future, but I
don't know your business.

If you are talking about the time portion, then the WHERE clause is not
the place to handle it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland, What i believe his problem is,
when he is trying to show date like "07/26/2005" while he is getting
"2006-07-26 00:00:0"

He is not converting the same date in the SELECT list

Mistakingly, he is doing something like this:

Select dbo.Membre.Date_Fin_Membre, Col2, Col3, ......
WHERE (dbo.Membre.Date_Fin_Membre = CONVERT(Char(10),
dbo.Membre.Date_Fin_Membre, 101)).

insteed of

Select CONVERT(Char(10), dbo.Membre.Date_Fin_Membre, 101), Col2, Col3,
......
WHERE (dbo.Membre.Date_Fin_Membre = CONVERT(Char(10),
dbo.Membre.Date_Fin_Membre, 101)).|||Zero.NULL (manish19@.gmail.com) writes:
> Erland, What i believe his problem is,
> when he is trying to show date like "07/26/2005" while he is getting
> "2006-07-26 00:00:0"

Of course, we could start some betting on what output Fernand really
wants. I think my odds are better. 2006-07-26 is a well-formatted date,
and complies with international standards. 07/26/2005 does not even look
like a date to most people in this world. I seem to recall that there
is one country where they use this format, but Fernand does not seem
to live there.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||That's right, we can only think about the possible situation on the
basis of provided data, and I have got this idea just because of style
of conversion Fernand used to compare dates.

In Where clause he used CONVERT(Char(10), dbo.Membre.Date_Fin_Membre,
101)), which converts date in char in mm/dd/yy format, but at the same
time he did not provide the information of what kind of conversion he
is using in Select List for this date column. He is puzzled with the
kind of result he is getting for this date column but did not written
what kind of result he has expected. That is what I am suspecting here
that his date display requirement is the same as he did in where clause.|||Hi Fernand St-Georges
Can you please explain ur problem. Please let us know, whats the
expected result and how the system is behaving now

best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***

2005 perf much worse than 2000... suggestions please..

I have this SP that takes several varchar columns and concatinates them all together then inserts them into a text field. I do this with a cursor which was the quickest way to get it done when it was setup...

However when I moved the process to a 2005 server (on the same physical server) the process drastically slowed down. On 2000 the process took about 7 min to handle all 350k+ rows with the processors hanging around 20-40%... On 2005 it took over 30 min (not sure how long it would take cause I killed the process) and the processors stay above 98%...

I have rewritten the process to use a while loop instead of the cursor (I wanted to do this anyways) and it had no effect. At this rate (about 1 row a second) it will take forever and this process runs everyday.

Any ideas?

Here is the procedure...

declare @.srch_field varchar(8000)

declare @.row int, @.productid varchar(25)

DECLARE @.title varchar(150), @.actors_keyname varchar(1200), @.directors_name varchar(400)

Declare @.genres varchar(700), @.theme varchar(1500), @.type varchar(1500), @.studio_desc varchar(100)

DECLARE @.media_format varchar(50), @.artist_name varchar(100), @.dev_name varchar(100)

DECLARE @.flags varchar(256), @.starring varchar(256), @.esrb varchar(100), @.esrb_desc varchar(500)

DECLARE @.ptrval varbinary(16), @.text varchar(max)

declare @.productlist table(product_id varchar(25), IDNUM int identity)

insert into @.productlist (product_id)

select product_id

from music_load..globalsearch

select @.row = @.@.rowcount

while @.row > 0

begin

select @.productid = product_id

from @.productlist

where idnum = @.row

SELECT @.title = rtrim(title) ,

@.actors_keyname = actors_keyname ,

@.directors_name = directors_name,

@.genres = genres ,

@.theme = theme ,

@.type = type ,

@.studio_desc = studio_desc,

@.media_format = media_format ,

@.artist_name = artist_name,

@.dev_name = dev_name,

@.flags = flags ,

@.starring =starring ,

@.esrb = esrb ,

@.esrb_desc = esrb_desc

FROM globalsearch

where product_id = @.productid

Set @.srch_field = isnull(@.title,'')

if @.actors_keyname is not null and @.actors_keyname <> 'unknown'

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.actors_keyname)

if @.directors_name is not null and @.directors_name <> 'unknown'

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.directors_name)

if @.genres is not null

Set @.srch_field = @.srch_field + ' ~ ' + (ltrim(rtrim(replace(@.genres, 0,''))))

if @.theme is not null

Set @.srch_field = @.srch_field + ' ~ ' + (ltrim(rtrim(replace(@.theme, 0,''))))

if @.type is not null

Set @.srch_field = @.srch_field + ' ~ ' + (ltrim(rtrim(replace(@.type, 0,''))))

if @.studio_desc is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.studio_desc)

if @.media_format is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.media_format)

if @.artist_name is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.artist_name)

if @.dev_name is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.dev_name)

if @.flags is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.flags)

if @.starring is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.starring)

if @.esrb is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.esrb)

if @.esrb_desc is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.esrb_desc)

update globalsearch

set srch_field = @.srch_field

where product_id = @.productid

SELECT @.ptrval = TEXTPTR(srch_field),

@.text = credits

FROM globalsearch

where product_id = @.productid

UPDATETEXT globalsearch.srch_field @.ptrval NULL NULL @.text

SELECT @.ptrval = TEXTPTR(srch_field),

@.text = track

FROM globalsearch

where product_id = @.productid

UPDATETEXT globalsearch.srch_field @.ptrval NULL NULL @.text

set @.row = @.row - 1

end

Text fields are going away in 2005. The first thing to do is change srch_field to a varchar(max) type.

The second thing is to make the update one UPDATE command instead of steping thru them one at a time. You don't need a cursor or a temp table or a loop at all. Do something like this:

UPDATE globalsearch
SET srch_field = isnull(rtrim(title),'') +
'~' + isnull(CASE WHEN actors_keyname <> 'unknown' THEN actors_keyname ELSE NULL END,'') +
'~' + isnull(CASE WHEN directors_name <> 'unknown' THEN directors_name ELSE NULL END,'') +
'~' + isnull((ltrim(rtrim(replace(@.genres, 0,'')))),'') +
......|||

I will change to a varchar(max)...

I was thinking about doing just one singe update rather than all the if statements but what about the 2 text columns cause even a varchar(max) will not allow an update using addition.

any suggestions on that?

|||

William Lowers wrote:

I will change to a varchar(max)...

I was thinking about doing just one singe update rather than all the if statements but what about the 2 text columns cause even a varchar(max) will not allow an update using addition.

any suggestions on that?

I don't quite understand your question.

You should change all the TEXT datatypes to VARCHAR(MAX). Then they are just strings and you can use string concatination on them directly just like any other string.|||

You are correct... for some reason I remember trying the string concatination and it didn't work. no idea when or where that was but I changed to all varchar(max) and made a single update statement. now the procedure runs in under 2 minutes...

Thanks a lot.