SQL query...decimals

[jdm]

Hello,
I'm attempting to query a pricing table (part_price), and I want to return all products (part) whose price (price) is extended out by three or more decimal places (.xxx). For example I want the product whose price is 10.765 (or 100.5327) to be returned, but I don't want the product whose price is 11.23 (or 7.2, or 28) to be returned. Can anyone offer any insight into my conundrum?

Thanks,
JDM

[debasisdas]

Kindly post the database thet you are using for the purpose.

Also post your table structure.

[jdm]

Oracle 8i

Column Data Type Null?
PART VARCHAR2 (20) N
PRICE NUMBER (22,7) N

- No PRIMARY KEY

Regards,
JDM

[debasisdas]

Question moved to Oracle Forum.

[amitpatel66]

Oracle 8i

Column Data Type Null?
PART VARCHAR2 (20) N
PRICE NUMBER (22,7) N

- No PRIMARY KEY

Regards,
JDM

Make use of INSTR,SUBSTR and LENGTH to achieve this!!

[nramrits]

Select *
From
Part_price
Where
Price=
Decode(
Sign(
Length(substr(p rice,instr(pric e,'.')+1,length (price)))-2),1,price)

[amitpatel66]

[code=oracle]
Select *
From
Part_price
Where
Price=
Decode(
Sign(
Length(substr(p rice,instr(pric e,'.')+1,length (price)))-2),1,price)
[/code]

nramrits,

Please make us of the code tags when ever you post any code in the forum which will make the code very clear and readable

[jdm]

Thank you very very much...EXTREMEL Y HELPFUL.


JDM