SQL: select (x/y) as z

I wish the following query to return z as a real (x/y), not as an integer (x div y) which it does.  x and y are integer values of the table.

select (x/y) as z from table.db
where y > 0

what to do?

bobby

rwvAsked:
Who is Participating?
 
shenqwConnect With a Mentor Commented:
select cast((x) as float(10,3))/cast((y) as float(10,3)) as z from table.db
  where y>0
0
 
simonetCommented:
Try this:

select CAST((x/y) as FLOAT(10,3)) as z from table.db
where y > 0

I can't remeber the syntax correctly, so if FLOAT(10,3) fails, try NUMBER(10,3), REAL, etc.

Alex
0
 
rwvAuthor Commented:
Alex, that didn't work, result was still an integer (or at least truncated)

I tried this and it works:

select (x+0.0)/y as z from table.db

To get 2 decimal places, this works:
select((x+100)/y)/100.0

However, the results look like
10
9.5
9.25
9
...etc.

Is there an easy way to format with out using the fields editor?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
simonetCommented:
>Is there an easy way to format without
>using the fields editor?

I don't think so. Using the fields editor gives you a consistent format throughout the application (wherever that particular TField is being used).

IMO, the Fields Editor is the easiest way to do it for calculated fields.

Alex
0
 
StefaanCommented:
Hi,

Could you tell us which SQL Database you are using ?
0
 
TheNeilCommented:
Why not just extract x and y as integers and then do the division (into a REAL) in your Delph code?

The Neil =:)
0
 
rwvAuthor Commented:
The Neil -
 I could do that, but then I would have to write the onDrawDataCell events for the dbgrid I want to display the results in, right?
0
 
rwvAuthor Commented:
Stefaan,
I'm using paradox tables.
0
 
TheNeilCommented:
rwv,

Good point. Didn't realise that you were outputting to a visual component - oops

The Neil =:(
0
 
rwvAuthor Commented:
error above should read:

To get 2 decimal places, this works:
select((x*100)/y)/100.0

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.