tsql help

22 Jul 2005 9:53 AM

Neil Evans-Mudie

Guys,

Using tsql (i.e. sql server)

I have a record like:

a        b        c

=        =        =

0.1    45.4    3.9E-2

I want to return a recordset like:

A       a_scale    b            b_scale    c    c_scale

=        ======    =            ======    =    =====

0.1     1                 0.454    100            3.9 0.001

i.e. get all the values into a range of 0 to 1.0, with an associated scaling
factor.

Could anybody offer any help, ideas or pointers?

Regards,

Neil Evans-Mudie
-. . .. .-.. / .----. ... --- -. .. -.-. .----. / . ...- .- -.
.... -....- -- ..- -.. .. .

e: M*@myorg.com address is a spam sink
If you wish to email me, try neilevans underscore mudie at hotmail dot com
w: http://groups.msn.com/TheEvansMudieFamily/_whatsnew.msnw?&pps=k

22 Jul 2005 1:07 PM

Razvan Socol

CREATE TABLE SomeTable (
    TheValue numeric(38,18) PRIMARY KEY
)

INSERT INTO SomeTable VALUES (0.1)
INSERT INTO SomeTable VALUES (45.4)
INSERT INTO SomeTable VALUES (3.9E-2)

SELECT TheValue, ScalingFactor, TheValue/ScalingFactor as
FractionalValue
FROM (
    SELECT TheValue, POWER(CAST(10 AS numeric(38,18)),
    FLOOR(LOG10(TheValue))+1) as ScalingFactor
    FROM SomeTable
) X

Razvan

22 Jul 2005 4:03 PM

Neil Evans-Mudie

Razvan (and John),

Many thanks for the replies and great suggestions. Razvan I really liked the
lok of yours. It appears to work a treat and I am implementing & testing
your solution to my particular problem space (OLTP to OLAP cube data
harvesting). I have an outstanding query on the OLAP newsgroup and once I
get some ideas from this I'll be able to close the end-to-end solution.

I'll add further posts if I get further problems in the hope you or others
wil help me out more.

Regards (and thanks again).

--
Cheers,

Neil Evans-Mudie
-. . .. .-.. / .----. ... --- -. .. -.-. .----. / . ...- .- -.
.... -....- -- ..- -.. .. .

e: M*@myorg.com address is a spam sink
If you wish to email me, try neilevans underscore mudie at hotmail dot com
w: http://groups.msn.com/TheEvansMudieFamily/_whatsnew.msnw?&pps=k

Show quote

"Razvan Socol" <rso***@gmail.com> wrote in message
news:1122037632.660434.185670@o13g2000cwo.googlegroups.com...
> CREATE TABLE SomeTable (
> TheValue numeric(38,18) PRIMARY KEY
> )
>
> INSERT INTO SomeTable VALUES (0.1)
> INSERT INTO SomeTable VALUES (45.4)
> INSERT INTO SomeTable VALUES (3.9E-2)
>
> SELECT TheValue, ScalingFactor, TheValue/ScalingFactor as
> FractionalValue
> FROM (
> SELECT TheValue, POWER(CAST(10 AS numeric(38,18)),
> FLOOR(LOG10(TheValue))+1) as ScalingFactor
> FROM SomeTable
> ) X
>
> Razvan
>

22 Jul 2005 1:09 PM

John Bell

Hi

There may be a better way, but along the lines of:

CREATE TABLE MyTable ( value float )

SELECT value, CASE WHEN LOG10 (value) < 0
        THEN 1/CAST(POWER(10,CEILING((LOG10 (value)+1)*-1)) AS decimal)
        ELSE POWER(10,CEILING(LOG10 (value) ))
        END AS Scale,
value/CASE WHEN LOG10 (value) < 0
        THEN 1/CAST(POWER(10,CEILING((LOG10 (value)+1)*-1)) AS decimal)
        ELSE POWER(10,CEILING(LOG10 (value) ))
        END AS modified
FROM myTable

may be along the lines of what you require.

John

Show quote

"Neil Evans-Mudie" wrote:

> Guys,
>
> Using tsql (i.e. sql server)
>
> I have a record like:
>
> a        b        c
>
> =        =        =
>
> 0.1    45.4    3.9E-2
>
> I want to return a recordset like:
>
> A       a_scale    b            b_scale    c    c_scale
>
> =        ======    =            ======    =    =====
>
> 0.1     1                 0.454    100            3.9 0.001
>
> i.e. get all the values into a range of 0 to 1.0, with an associated scaling
> factor.
>
> Could anybody offer any help, ideas or pointers?
>
> Regards,
>
> Neil Evans-Mudie
> -. . .. .-.. / .----. ... --- -. .. -.-. .----. / . ...- .- -.
> .... -....- -- ..- -.. .. .
>
> e: M*@myorg.com address is a spam sink
> If you wish to email me, try neilevans underscore mudie at hotmail dot com
> w: http://groups.msn.com/TheEvansMudieFamily/_whatsnew.msnw?&pps=k
>
>
>

23 Jul 2005 5:40 AM

Razvan Socol

John,

There are some differences between your query and my query. For
example, if the value is 100, my query returns 0.1*1000 and your query
returns 1.0*100. The specification is not very precise on this matter
(Neil wrote "into a range of 0 to 1.0"); I wrote my query assuming that
Neil wanted to write that the modified value should be >=0.1 and <1.0.

Razvan