Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

doing math in a select...

Posted on 2003-11-20
14
Medium Priority
?
1,600 Views
Last Modified: 2008-02-26
my situation is simple.

i have a database table in sql server 2000. this table has some image data (Id, fileName, Width, Height)
i would like to create a select statment that would return this data to me with 2 additional feilds. theis 2 feilds require a math function perfomed on the width and height.

i am trying to make thumbs that would be 100 width. for this i use the formula...
100 / width --> to get the scale

then i do: scaleValue * width to get the width to be 100.
i then use this scaleValue * height to maniupulate the height soto keep the height : width ration tha same and have no distortion in the image [proportions.

sql is giving me problems with this tSQL.

best i can do is...

select *, ( (100 / width) * width ) as smWidth
from tblCardImages

but the value it gives me is 0 for all images returned. looks liek the tSQL chokes oin the math and is not liking the datatype???

any ideas, or answers???
0
Comment
Question by:kacalapy
  • 5
  • 3
  • 3
  • +2
14 Comments
 
LVL 3

Expert Comment

by:xmstr
ID: 9788717
What data type are the width and height fields?

If they are a numeric data type (int, numeric, etc) than your code should work.  If they are stored as a character data type (char, varchar, etc) then they need to be converted before performing any mathematical operation on them.

If the data types are character types then try using then following: cast(colname as int) .
0
 
LVL 3

Expert Comment

by:xmstr
ID: 9788742
example:

select *, ( (100 / cast(width as int)) * cast(width as int)) as smWidth
from tblCardImages
0
 
LVL 1

Author Comment

by:kacalapy
ID: 9788774
the hight and width datatypes are both int
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 300 total points
ID: 9788803
The width should always be 100, so I don't think you need a calculation for it (100 / width * width = 100, always).

So maybe something like this:

SELECT *, 100 AS smWidth, CAST(CAST(height AS DECIMAL(9, 1)) * 100.0 / CAST(width AS DECIMAL(9, 1) AS DECIMAL(9, 1)
FROM tblCardImages
0
 
LVL 1

Author Comment

by:kacalapy
ID: 9788805
it chokes on values that are less than 1. the 100/ with should be .25 and it returns zero all the way down
for testing i made all width values 400

even the folowing chokes and retuns zeros...
select *, ( (100 / cast(width as int))) as smWidth
from tblCardImages
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9788807
D'oh!

SELECT *, 100 AS smWidth, CAST(CAST(height AS DECIMAL(9, 1)) * 100.0 / CAST(width AS DECIMAL(9, 1) AS DECIMAL(9, 1) AS smHeight
FROM tblCardImages

0
 
LVL 3

Expert Comment

by:xmstr
ID: 9788825
do what scott says and cast everything as decimal.
0
 
LVL 1

Author Comment

by:kacalapy
ID: 9788866
soekthiung not working out in here. i am trying to make some mods now...
0
 
LVL 1

Author Comment

by:kacalapy
ID: 9788938
ok, i made the change to the database table to double. and my select worked out.
guess the points go to scott for pointing out the use of the dbl datatype.
0
 
LVL 1

Author Comment

by:kacalapy
ID: 9788945
although scott can you post the correct syntax for the casting so i can refer to it later.
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9789000
Same as Scott's solution. Just missed two right parenthesis

SELECT *, 100 AS smWidth,
CAST(CAST(height AS DECIMAL(9, 1)) * 100.0 /
     CAST(width AS DECIMAL(9, 1)) AS DECIMAL(9, 1)) AS smHeight
FROM tblCardImages


0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9789005
Sure, you just specify:

CAST(numeric_expression AS datatype)

For example:

CAST(12.3 AS INT)

CAST(100 * width / height - c + x / 14 AS DECIMAL(10,2))

Note, too, that a decimal number has a higher precedence, which in real language means that if any value is a decimal the others will be automatically cast to decimal by SQL.  So, for example, if width is an integer:

width / 100 = 0 (!), because since both are integers, SQL does integer division, so fractions are *ignored*, so 0.99 = 0

but this:

width / 100.0 = 0.99, because since 100.0 is decimal, SQL can't do integer arithmetic, and will have to force width to decimal to the calculation.

I prefer to CAST myself because:
1) it's clearer later what's happening
2) if for some reason you later remove 100.0 from the calcuation, you could accidentally go back to integer arithmetic
0
 
LVL 3

Expert Comment

by:richardjb
ID: 9789081
Hi Kacalapy,

I'm pretty sure the problem is that your columns are integers, so SQL is doing integer arithmetic.

In SQL Server "select 1/3" returns "0", while "select 1/3.0" returns the expected ".333333".

The solution is to replace the literal 100 with 100.0 to force decimals.

create table test4 ( width int, height int)

insert into test4 values (50, 80)
insert into test4 values (500, 800)

select *, 100/width scale, 100 sm_width, 100/width*height sm_height from test4

gives   width       height    scale      sm_width    sm_height  
           ----------- ----------- ----------- -----------     -----------
           50           80           2            100             160
           500         800         0             100            0


select *, 100.0/width scale, 100.0 sm_width, 100.0/width*height sm_height from test4

gives   width       height      scale                    sm_width   sm_height                    
           ----------- ----------- -----------------         --------         ----------------------------
           50           80           2.000000000000   100.0         160.000000000000
           500         800         .200000000000     100.0         160.000000000000

Your other approach is to multiply before dividing.  This still truncates to integers, but has a smaller error as the numbers are bigger.  "Select 5*5/10" gives 2 where "select 5/10*5 gives 0 but to get the correct 2.5 takes "select 5./10*5".  Thus height*100/width gives a better result than 100/width*height or height/width*100.

Regards,  Richard
0
 
LVL 3

Accepted Solution

by:
richardjb earned 75 total points
ID: 9789148
Sorry Scott, I had not seen your posts when I posted the above.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question