Solved

doing math in a select...

Posted on 2003-11-20
14
1,559 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 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 25 total points
ID: 9789148
Sorry Scott, I had not seen your posts when I posted the above.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

856 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