Performing arithmetic operation

Hi all,

Currently I working with Sybase 5.0.04.  I have written a stored function 
to return a decimal data type with some simple arithmetic operation.

Code as follow :

ALTER FUNCTION test (in @temp varchar(10))
RETURNS Decimal(5,2)
BEGIN
    DECLARE temp1 Decimal(5,2);
    DECLARE temp2 Integer;
    DECLARE temp3 Decimal (5,2);
    SELECT total_cost, quality INTO temp1, temp2 FROM SALE WHERE SALE_ID = 
@temp;

    SET temp3 = temp1/temp2
    RETURN (temp3);
END

-----
However, it doesnt' seems to compute temp1/temp2, always return me 0.00 
value if value of temp1 > value of temp2.  However, if value of temp2 > 
value of temp1, it works perfectly okay.  What could went wrong?  Could it 
be the data type that I am trying to make use of?

PLEASE HELP.

Thanks & Regards
0
png
10/13/2000 2:39:53 AM
sybase.sqlanywhere.general 32637 articles. 4 followers. Follow

3 Replies
408 Views

Similar Articles

[PageSpeed] 40

Yes, it's a datatype problem.  You're using integer as the denominator in
the division operation.  Make that a decimal(5.2) as well and it should all
work fine.  Since the denominator is an integer, it's rounding the result
down to the nearest whole number, which in the case of A < B is 0, and
casting that as Decimal( 5,2 ).

Paul Horan [TeamSybase]
Buffalo, NY

<png.leelee@buck.com.sg> wrote in message
news:AF53A2D675B8943C000EA35E85256977.000EA36E85256977@webforums...
> Hi all,
>
> Currently I working with Sybase 5.0.04.  I have written a stored function
> to return a decimal data type with some simple arithmetic operation.
>
> Code as follow :
>
> ALTER FUNCTION test (in @temp varchar(10))
> RETURNS Decimal(5,2)
> BEGIN
>     DECLARE temp1 Decimal(5,2);
>     DECLARE temp2 Integer;
>     DECLARE temp3 Decimal (5,2);
>     SELECT total_cost, quality INTO temp1, temp2 FROM SALE WHERE SALE_ID =
> @temp;
>
>     SET temp3 = temp1/temp2
>     RETURN (temp3);
> END
>
> -----
> However, it doesnt' seems to compute temp1/temp2, always return me 0.00
> value if value of temp1 > value of temp2.  However, if value of temp2 >
> value of temp1, it works perfectly okay.  What could went wrong?  Could it
> be the data type that I am trying to make use of?
>
> PLEASE HELP.
>
> Thanks & Regards


0
Paul
10/13/2000 5:24:45 AM
Paul has identified the cause of the problem.  ANother way to resolve the problem is via this trick

Instead of 
    SET temp3 = temp1/temp2
try
    SET temp3 = temp1/(temp2+0.0)

Which forces the denonimator to decimal (or real?) before the division. 

Another way:
    SET temp3 = temp1/(cast (temp2 as decimal(16,5))


Leo Tohill - Team Sybase
>> Please post in newsgroup, not via email <<
Useful links:
Downloads: http://www.sybase.com/developer/mobile/
Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=Bug
0
leotohill
10/13/2000 1:41:00 PM
Somehow I think this is a bug. It should perform type promotion in both
cases. Anyway, the following code works fine with asa 6.0.3.3030 (changed it
a bit)

create function test()
returns decimal(5,2)
begin
  declare temp1 decimal(5,2);
  declare temp2 integer;
  declare temp3 decimal(5,2);
  select 1.2,2 into temp1,temp2;
  set temp3=temp1/temp2;
  return(temp3)
end;

select test()

Paul Horan[TeamSybase] wrote in message ...
>Yes, it's a datatype problem.  You're using integer as the denominator in
>the division operation.  Make that a decimal(5.2) as well and it should all
>work fine.  Since the denominator is an integer, it's rounding the result
>down to the nearest whole number, which in the case of A < B is 0, and
>casting that as Decimal( 5,2 ).
>
>Paul Horan [TeamSybase]
>Buffalo, NY
>



0
Goran
10/13/2000 1:56:26 PM
Reply:

Similar Artilces:

General performance
Name: Rodolfo Wetzel Product: Gran Paradiso Summary: General performance Comments: It works fine, specially with zooming sites like chess.com that are difficult with IE (chess pieces are not properly shown). What I really miss, are some add on like cooliris' blue botton to search faster. So I still use the Firefox 2.1 with working compatibilities. Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9b5) Gecko/2008032620 Firefox/3.0b5 From URL: http://hendrix.mozilla.org/ ...

SQLAnywhere performance
I have a SQLANywhere db running on a Netware v 3.12 server. The database performed well as a stand-alone db but when moved to the network performance is lousy. Where do I look for answers/solutions to this problem? thanks How lousy? What part is lousy? You can't expect multiple row retrieves to perform the same in both a network and standalone environment. ...

What generals generally do
Reading a McChrystal thread elsewhere, I came across someone quoting a US general http://en.wikipedia.org/wiki/Smedley_Butler > I spent 33 years and four months in active military service and > during that period I spent most of my time as a high class thug for > Big Business, for Wall Street and the bankers. In short, I was a > racketeer, a gangster for capitalism. I helped make Mexico and > especially Tampico safe for American oil interests in 1914. I helped > make Haiti and Cuba a decent place for the National City Bank boys to > collect revenues in. I ...

CA operations
On both a new and existing install of iManager 2.6, attempting some operations on CA object, result in "Internal error" "page has peformed an illegal operation" For example, in iManager, Directory Administration, Modify Object, clicking on Certificates tab will give the error. This is in prep to move the CA to a Linux box. FWIW following TID 3618399. I have full admin rights. PKIdiag on CA server (NW6.5) shows 0 errors. Searching KB give no clues. -- joea ------------------------------------------------------------------------ joea, You ...

General Operation
Name: Joe Smetona Email: joedotsmetonaatgmail.com Product: Gran Paradiso Alpha 1 Summary: General Operation Comments: Works well with Windows XP SP2 (fully patched) on P4 HT 3.5 Ghz, 1GB, Dell Optiplex GX620. No Problems Found. Browser Details: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9a1) Gecko/20061204 GranParadiso/3.0a1 ...

arithmetic operators
in short, how can i take 2 values from params passed to the script in a form, and get a third variable from subtracting the first 2? -- Matthew Harrison Internet/Network Services Administrator Peanut-Butter Cheesecake Hosting Services Genstate www.peanutbuttercheesecake.co.uk Look at CGI.pm for grabbing form data... use CGI qw/:standard/; my $diff = param('val1') - param('val2'); Rob -----Original Message----- From: Matthew Harrison [mailto:edhunter@peanutbuttercheesecake.co.uk] Sent: Monday, March 25, 2002 9:20 AM To: beginners@perl.org Subject: ar...

Performance about SQLAnywhere
Currently I have a SQLAnywhere database of size over 250Mb. We have a program written in PB to retrieve data from it, but that program slowed down gradually after we increased the volume of data from 100Mb to its current size. Is this a limitation of SQLAnywhere, or just my machine? Is there any fine tuning possible? The configuration of my machine is Pentium III, 128Mb RAM, 10G HD. If you have allocated sufficient cache to the database (should be at least 25mb, and 64mb would be good) then the remaining suspect would be a table scan somewhere. Have all database-related functio...

Can I perform a minus operation between two result sets, like oracle's MINUS operator?
Hi! For example: Two result sets: A and B. I want a new set : A-B Thank you! AFAIK there are two ways to do this in ASE: (1) Load A into a temporary table and delete from it via a join with B. (2) Select A.STUFF into DIFFERENCE from A where not exists (select B.key1 from B where B.key1 = A.key1 and B.key2 = A.key2 ....) The second technique may be slow owing to the "not exists". "From East" ...

General performance tips
Hi, we have a 8-CPU Solarix box with 32G of memory. The server is running Sybase 12.5 and some misc services and batch jobs (shell scripts and perl scripts) - it is essentially a dedicated database host. In a setup like this, does it make sense to have online engines = 7 and Sybase total memory = 23 GB? (these are the current settings) I understand that it is impossible to give a recommendation based on this information, but generally, would you try max out the memory and the number of online engines? Is it possible to give too much memory or too many parallell CPUs to Sybase...

SQLAnywhere performance over a WAN
Hello SQLAnywhere Pro's !!! I have a client that uses our application (PB60 App) against a 20MB SQLAnywhere (5.5.04) database (they are a new client - normally the database would be about 100 - 200 MB) running on Windows NT. Performance has been great. They just linked up to another office of theirs via a Fractional T1 line (384K pipe). There will be 8 additional users that will access the database remotely. There are 10 people accessing the database locally. The NT Server is a Pentium 400 with 256MB Ram. The WAN has been completed and they are starting to test our app (w...

SQLAnywhere performance problem
We have a complicated general performance problem in SQL Anywhere: The problem only occurs when retrieveing data to a datawindow in Powerbuilder (both 5.04 and 6.0) from a SQL Anywhere (5.5) database. Not with other databases and not when retrieveing the same data in another way (Embedded SQL with cursor). Large packets of what seems to be garbage data is sent from the client to the server in between small packets of real data from the server to the client. In fact more data is sent from the client than what is received (by a factor of four). We�re only reading data, no updates, ...

Performance problem in general
Hi, Our main file server is on nw 6.5 (sp3-cpr). It's a dl360 g4 (dual 3.6 - HT disabled) with 4G ram and aroung 2 TB of data on a HP EVA5000. We're experiencing performance problem with most of our interactions with the server. Some days are better than others but it still slow. We mostly get our feedback from MSOffice users but we found out that even copying files or listening to mpg's are slow. I included a config.txt. Any ideas? Thanks Dave ---------- Start of Config.txt ---------------- Novell File Server Configuration Report For Server: Server1 Nov...

sqlanywhere performance improvement
Hi, 1. Does anyone know how I can rebuild indexes in a sqlanywhere database. 2. can anyone tell me how to see how the optimizer searches for the best path, if he uses his indexes properly, ... 3. Is there a possibility to view all sql-statements that are executed on the database, with there execution time? Thanks in advance ! Xavier Xavier Keters wrote: > > Hi, > > 1. Does anyone know how I can rebuild indexes in a sqlanywhere database. IF EXISTS(SELECT name FROM sysindexes WHERE name='MyIndexName') DROP INDEX MyTableName.MyIndexName GO ...

SQLAnywhere-performance-problem
We are using a windows-software for dialysis units based on an SQLAnywhere 5.505 Server running under Novell 5.0 ( SP5). After the last software-update we have performance-problems using a few querys ( server-utilisation more than 90%, poor performance of the query-lasting 2-3 times longer etc. ). Now the software-house told us, that the performance of the Novell-version oft ASA 5.505 would be much lower ( using the same hardware ) than the windows-version and we should switch our server to W2K ( or switch to ASA 7.02 and use SMP under W2K additionally! ). We do not know what to...

Web resources about - Performing arithmetic operation - sybase.sqlanywhere.general

File:Periplaneta americana performing a high-speed inversion on a ramp (side view) - Journal.pone.0038003.s002.ogv ...
Periplaneta_americana_performing_a_high-speed_inversion_on_a_ramp_(side_view)_-_Journal.pone.0038003.s002.ogv ‎ (Ogg Theora video file, length ...

Demi Lovato falls on stage while performing Cool For The Summer - video
DEMI Lovato fell flat on her back during a gig at New York City’s iconic Madison Square Garden venue on Friday night.

Molybdenum this year's worst-performing commodity
An obscure metal used to make steel has become this year's worst-performing commodity.

You think AFL coaches face pressure: what about principals of high-performing state schools?
Being a high-achieving non-selective state school produces high demand for places and a lot of pressure to keep improving results. Meet Pitsa ...

How is Scott Morrison performing as Treasurer?
When Malcolm Turnbull challenged Tony Abbott for the leadership of the Liberal Party last year, he said the government needed a different leadership ...

The Revenant: Actor Grace Dove talks about performing with Leonardo DiCaprio
The B.C. First Nations actor who plays Leonardo DiCaprio's wife in The Revenant says she had nothing but great experiences working opposite the ...

Internet in tears over video of kids choir performing Arabic welcome song
It’s a shaky and grainy video, but its message is what has the Internet in tears.

How Are Presidential Candidates Performing on YouTube?
With a GOP debate tomorrow night , we decided to check in and see how Republican presidential candidates are performing on YouTube. To no surprise, ...

Israel's best-performing tech stock hasn't sold a single product
An Israeli upstart which is trying to sell investors on a future in which physical objects materialise with the press of a buttonis attracting ...

Here Are the Best- and Worst-Performing Assets of 2015
From currencies to corporate bonds. As 2015 draws to a close, it's time to tally up the best- and worst-performing investments of the year. ...

Resources last updated: 1/22/2016 7:10:14 PM