Oracle SQL : Need float column with default one decimal value in select queryAdd a column with a default value to an existing table in SQL ServerSQL select join: is it possible to prefix all columns as 'prefix.*'?How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?Insert results of a stored procedure into a temporary tableSQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraintsWhat are the options for storing hierarchical data in a relational database?Nested select statement in SQL Server'IF' in 'SELECT' statement - choose output value based on column valuesSQL select only rows with max value on a columnSelect Query: To Show Average of Columns in Decimal
Can I Retrieve Email Addresses from BCC?
How could Frankenstein get the parts for his _second_ creature?
Applicability of Single Responsibility Principle
Why does John Bercow say “unlock” after reading out the results of a vote?
How can I replace every global instance of "x[2]" with "x_2"
Have I saved too much for retirement so far?
Can somebody explain Brexit in a few child-proof sentences?
What would be the benefits of having both a state and local currencies?
Lay out the Carpet
Your magic is very sketchy
What defines a dissertation?
Will it be accepted, if there is no ''Main Character" stereotype?
Bash method for viewing beginning and end of file
Do I need a multiple entry visa for a trip UK -> Sweden -> UK?
Modify casing of marked letters
What are the ramifications of creating a homebrew world without an Astral Plane?
Why "be dealt cards" rather than "be dealing cards"?
What is the opposite of 'gravitas'?
Can a monster with multiattack use this ability if they are missing a limb?
Teaching indefinite integrals that require special-casing
What to do with wrong results in talks?
Why is delta-v is the most useful quantity for planning space travel?
If you attempt to grapple an opponent that you are hidden from, do they roll at disadvantage?
What's the purpose of "true" in bash "if sudo true; then"
Oracle SQL : Need float column with default one decimal value in select query
Add a column with a default value to an existing table in SQL ServerSQL select join: is it possible to prefix all columns as 'prefix.*'?How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?Insert results of a stored procedure into a temporary tableSQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraintsWhat are the options for storing hierarchical data in a relational database?Nested select statement in SQL Server'IF' in 'SELECT' statement - choose output value based on column valuesSQL select only rows with max value on a columnSelect Query: To Show Average of Columns in Decimal
I have a table DATA_TABLE, it have MAX_RATE_UNIT column as float value.
Below is the table and MAX_RATE_UNIT column values like,
DATA_TABLE
----------
MAX_RATE_UNIT
-----------
1.2
1
3
3.4
0.445
2343.43434
2.123
0.3433423
I want a select query which has return the result as minimum one decimal point if the decimal is not have in tha column value, as 1--> 1.0 and 3-> 3.0 and remaing are same value to return,
OUTPUT:
MAX_RATE_UNIT
-------------
1.2
**1.0**
**3.0**
3.4
0.445
2343.43434
2.123
0.3433423
Help me for this logic,
sql oracle11g oracle10g
asked Mar 21 at 15:23
RJKRPRJKRP
236
add a comment |
I have a table DATA_TABLE, it have MAX_RATE_UNIT column as float value.
Below is the table and MAX_RATE_UNIT column values like,
DATA_TABLE
----------
MAX_RATE_UNIT
-----------
1.2
1
3
3.4
0.445
2343.43434
2.123
0.3433423
I want a select query which has return the result as minimum one decimal point if the decimal is not have in tha column value, as 1--> 1.0 and 3-> 3.0 and remaing are same value to return,
OUTPUT:
MAX_RATE_UNIT
-------------
1.2
**1.0**
**3.0**
3.4
0.445
2343.43434
2.123
0.3433423
Help me for this logic,
sql oracle11g oracle10g
asked Mar 21 at 15:23
RJKRPRJKRP
236
add a comment |
I have a table DATA_TABLE, it have MAX_RATE_UNIT column as float value.
Below is the table and MAX_RATE_UNIT column values like,
DATA_TABLE
----------
MAX_RATE_UNIT
-----------
1.2
1
3
3.4
0.445
2343.43434
2.123
0.3433423
I want a select query which has return the result as minimum one decimal point if the decimal is not have in tha column value, as 1--> 1.0 and 3-> 3.0 and remaing are same value to return,
OUTPUT:
MAX_RATE_UNIT
-------------
1.2
**1.0**
**3.0**
3.4
0.445
2343.43434
2.123
0.3433423
Help me for this logic,
sql oracle11g oracle10g
asked Mar 21 at 15:23
RJKRPRJKRP
236
I have a table DATA_TABLE, it have MAX_RATE_UNIT column as float value.
Below is the table and MAX_RATE_UNIT column values like,
DATA_TABLE
----------
MAX_RATE_UNIT
-----------
1.2
1
3
3.4
0.445
2343.43434
2.123
0.3433423
I want a select query which has return the result as minimum one decimal point if the decimal is not have in tha column value, as 1--> 1.0 and 3-> 3.0 and remaing are same value to return,
OUTPUT:
MAX_RATE_UNIT
-------------
1.2
**1.0**
**3.0**
3.4
0.445
2343.43434
2.123
0.3433423
Help me for this logic,
sql oracle11g oracle10g
sql oracle11g oracle10g
asked Mar 21 at 15:23
RJKRPRJKRP
236
asked Mar 21 at 15:23
RJKRPRJKRP
236
edited Mar 21 at 15:25
RJKRP
asked Mar 21 at 15:23
RJKRPRJKRP
236
asked Mar 21 at 15:23
RJKRPRJKRP
236
asked Mar 21 at 15:23
RJKRPRJKRP
236
236
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Convert the number to a string explicitly with to_char() and a suitable format model:
-- CTE for sample data
with data_table (max_rate_unit) as (
select 1.2 from dual
union all select 1 from dual
union all select 3 from dual
union all select 3.4 from dual
union all select 0.445 from dual
union all select 2343.43434 from dual
union all select 2.123 from dual
union all select 0.3433423 from dual
)
-- actual query
select to_char(max_rate_unit, 'FM999999990.099999999') as max_rate_unit
from data_table;
MAX_RATE_UNIT
--------------------
1.2
1.0
3.0
3.4
0.445
2343.43434
2.123
0.3433423
Strategically using 0 instead of 9 immediately before and after the decimal point means you get a leading zero for values < 1, and at least one digit after the decimal point; the FM modifier stops that showing all the trailing digits.
You need enough placeholders in toe format model, either side of the decimal point, to account for the full range of values you might have to handle. (My original edit didn't have enough so the last digit of the last value was silently truncated...)
answered Mar 21 at 15:33
Alex PooleAlex Poole
134k6108182
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55283830%2foracle-sql-need-float-column-with-default-one-decimal-value-in-select-query%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Convert the number to a string explicitly with to_char() and a suitable format model:
-- CTE for sample data
with data_table (max_rate_unit) as (
select 1.2 from dual
union all select 1 from dual
union all select 3 from dual
union all select 3.4 from dual
union all select 0.445 from dual
union all select 2343.43434 from dual
union all select 2.123 from dual
union all select 0.3433423 from dual
)
-- actual query
select to_char(max_rate_unit, 'FM999999990.099999999') as max_rate_unit
from data_table;
MAX_RATE_UNIT
--------------------
1.2
1.0
3.0
3.4
0.445
2343.43434
2.123
0.3433423
Strategically using 0 instead of 9 immediately before and after the decimal point means you get a leading zero for values < 1, and at least one digit after the decimal point; the FM modifier stops that showing all the trailing digits.
You need enough placeholders in toe format model, either side of the decimal point, to account for the full range of values you might have to handle. (My original edit didn't have enough so the last digit of the last value was silently truncated...)
answered Mar 21 at 15:33
Alex PooleAlex Poole
134k6108182
add a comment |
Convert the number to a string explicitly with to_char() and a suitable format model:
-- CTE for sample data
with data_table (max_rate_unit) as (
select 1.2 from dual
union all select 1 from dual
union all select 3 from dual
union all select 3.4 from dual
union all select 0.445 from dual
union all select 2343.43434 from dual
union all select 2.123 from dual
union all select 0.3433423 from dual
)
-- actual query
select to_char(max_rate_unit, 'FM999999990.099999999') as max_rate_unit
from data_table;
MAX_RATE_UNIT
--------------------
1.2
1.0
3.0
3.4
0.445
2343.43434
2.123
0.3433423
Strategically using 0 instead of 9 immediately before and after the decimal point means you get a leading zero for values < 1, and at least one digit after the decimal point; the FM modifier stops that showing all the trailing digits.
You need enough placeholders in toe format model, either side of the decimal point, to account for the full range of values you might have to handle. (My original edit didn't have enough so the last digit of the last value was silently truncated...)
answered Mar 21 at 15:33
Alex PooleAlex Poole
134k6108182
add a comment |
Convert the number to a string explicitly with to_char() and a suitable format model:
-- CTE for sample data
with data_table (max_rate_unit) as (
select 1.2 from dual
union all select 1 from dual
union all select 3 from dual
union all select 3.4 from dual
union all select 0.445 from dual
union all select 2343.43434 from dual
union all select 2.123 from dual
union all select 0.3433423 from dual
)
-- actual query
select to_char(max_rate_unit, 'FM999999990.099999999') as max_rate_unit
from data_table;
MAX_RATE_UNIT
--------------------
1.2
1.0
3.0
3.4
0.445
2343.43434
2.123
0.3433423
Strategically using 0 instead of 9 immediately before and after the decimal point means you get a leading zero for values < 1, and at least one digit after the decimal point; the FM modifier stops that showing all the trailing digits.
You need enough placeholders in toe format model, either side of the decimal point, to account for the full range of values you might have to handle. (My original edit didn't have enough so the last digit of the last value was silently truncated...)
answered Mar 21 at 15:33
Alex PooleAlex Poole
134k6108182
Convert the number to a string explicitly with to_char() and a suitable format model:
-- CTE for sample data
with data_table (max_rate_unit) as (
select 1.2 from dual
union all select 1 from dual
union all select 3 from dual
union all select 3.4 from dual
union all select 0.445 from dual
union all select 2343.43434 from dual
union all select 2.123 from dual
union all select 0.3433423 from dual
)
-- actual query
select to_char(max_rate_unit, 'FM999999990.099999999') as max_rate_unit
from data_table;
MAX_RATE_UNIT
--------------------
1.2
1.0
3.0
3.4
0.445
2343.43434
2.123
0.3433423
Strategically using 0 instead of 9 immediately before and after the decimal point means you get a leading zero for values < 1, and at least one digit after the decimal point; the FM modifier stops that showing all the trailing digits.
You need enough placeholders in toe format model, either side of the decimal point, to account for the full range of values you might have to handle. (My original edit didn't have enough so the last digit of the last value was silently truncated...)
answered Mar 21 at 15:33
Alex PooleAlex Poole
134k6108182
edited Mar 21 at 15:47
answered Mar 21 at 15:33
Alex PooleAlex Poole
134k6108182
answered Mar 21 at 15:33
Alex PooleAlex Poole
134k6108182
answered Mar 21 at 15:33
Alex PooleAlex Poole
134k6108182
134k6108182
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55283830%2foracle-sql-need-float-column-with-default-one-decimal-value-in-select-query%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
