Insert decimal in query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Insert decimal in query

Author

Topic

misterzr
Starting Member

49 Posts

Posted - 2005-09-12 : 18:01:24

I have a table that has a column that is float type and has values in it with no decimals. The values are a price that is rounded down to 4 decimal places that displays without the decimal. I need to return a query that diplays the decimal at the 4 digit place from the right.

Here is my query now that returns the column as is.
The column in question is P1UPRC

SELECT RTRIM(IMLITM),IMDSC1,SUBSTRING(IMDSC2, 11, 30) as SIZE,P1BTID as SRP,P1UPRC  as COST,IMAITM,IBTAX1,IMSRTX,IMSRP3,IMSRP1,LIPQOH-(LIHCOM+LIPCOM)as AVAIL
FROM   PRODDTA.F4101 INNER JOIN PRODDTA.F5541060 ON IMLITM = P1LITM INNER JOIN PRODDTA.F4102 ON IMLITM = IBLITM INNER JOIN PRODDTA.F41021 ON IMITM = LIITM
WHERE  (IBSTKT ='S' OR IBSTKT ='U')
AND (IMGLPT ='IN10' OR  IMGLPT ='IN11' OR  IMGLPT ='IN12' OR  IMGLPT ='IN13' OR IMGLPT ='IN17')
AND IMSRP1 <> 'NAR'
AND IMLNTY ='S'
AND IMPRP7 IS NOT NULL
AND LTRIM(IBMCU) ='UP'
AND LTRIM(LIMCU)='UP'
AND LIPBIN='P'

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2005-09-12 : 19:08:11

Please supply some sample data and what you want it to look like as output.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-13 : 01:16:20

and also the table structure

Madhivanan

Failing to plan is Planning to fail

misterzr
Starting Member

49 Posts

Posted - 2005-09-13 : 09:54:45

100081|BIAXIN 250MG FILMTABS|100|170.57|1502589|55325336860|N|ABBOTT-ROSS LABS|PHA|RX1|53|

COST is 1502589 should be 150.2589

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-13 : 09:57:48

look in BOL at the convert function with money datatype.

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-13 : 09:58:27

may be you need to divide that by 10000.0?

Madhivanan

Failing to plan is Planning to fail

misterzr
Starting Member

49 Posts

Posted - 2005-09-13 : 10:01:55

tried the convert with money type and it makes it 1502589.0000

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-13 : 10:04:15

Is this?
select convert(decimal(12,4),1502589/10000.0)

Madhivanan

Failing to plan is Planning to fail

misterzr
Starting Member

49 Posts

Posted - 2005-09-13 : 10:10:22

Thanks alot madhivanan, your solution seems to work fine

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-13 : 10:14:07

wait a minute... just dividing the result by 10k is never a good solution.
what happens when you get a result that isn't supposed to be divided by 10k??

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources