Solved

doing math in a select...

Posted on 2003-11-20
14
1,546 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
Comment Utility
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
Comment Utility
example:

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

Author Comment

by:kacalapy
Comment Utility
the hight and width datatypes are both int
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 total points
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
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
Comment Utility
do what scott says and cast everything as decimal.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 1

Author Comment

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

Author Comment

by:kacalapy
Comment Utility
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
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
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
Comment Utility
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 25 total points
Comment Utility
Sorry Scott, I had not seen your posts when I posted the above.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
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.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now