?
Solved

doing math in a select...

Posted on 2003-11-20
14
Medium Priority
?
1,583 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 69

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 69

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 69

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

777 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