SQL & PL/SQL :: Format Column Value In Decimal

Jul 26, 2013

I have an amount field and I want the o/p in the format of $9,999,999.99

like for 1500 it should display $1,500.00
for 25000 it should display $25,000.00
for 25000000 it should display $25,000,000.00

Test case

add comma from last 3rd position ,and append a $ in the start with two decimal places

View 15 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Format Output Of Column

Jun 10, 2011

I have output from select something like :

A_PID B_TEXT id_f
-------------- ----------------------------- -----------
23 B_text sample 9888888

and my question is how can I format the output to have it thinner .

I can do it for text: COLUMN FORMAT B_TEXT A5, but i dont know to apply it for fields with number e.g. A_PID column.

View 4 Replies View Related

SQL & PL/SQL :: Record To Column Format

Feb 1, 2011

I have to display a record in column format. I have tried doing it using COUNT & DECODE as in below script.

SELECT COUNT (DECODE (a1.application_status, 21, a1.ppid, NULL)) cnt_app21,
COUNT (DECODE (a1.application_status, 22, a1.ppid, NULL)) cnt_app22
FROM (SELECT a.application_status application_status, a.ppid ppid
FROM vp_recon_upload_dtls a, vp_ppid_upload_info b

[Code]...

This gives output as:-

cnt_app21 cnt_app21
3 1

The second query is little different from above as it has 2 more tables joined and more conditions than first query.

SELECT SUM (DECODE (a2.application_status, 21, a2.rate_amount, NULL)) sum_app21,
SUM (DECODE (a2.application_status, 22, a2.rate_amount, NULL)) sum_app22
FROM (SELECT a.application_status application_status,
d.rate_amount rate_amount
FROM vp_recon_upload_dtls a,

[Code]....

Second query output is :-

SUM_APP21 SUM_APP21
222 111

My requirement is to display all the four values in one row as:-

cnt_app21 cnt_app21 SUM_APP21 SUM_APP21
3 1 222 111

One easy way is to create two views and select values from them.

But for some reason I have to write in a query as there would be more dynamic conditions added to it.

View 2 Replies View Related

Column Header Format

Mar 14, 2007

I'm trying to sort out my coloumn headers and make things a bit neater using the code....

column name format A9 heading 'Emp|Name'
column birthdate format A9 heading 'Birth|Date'
select name, birthdate
from Employee
where ....;

It seems to display all the results in double space if i use more than one 'column' statement though? One statement works fine, what've i gotta change?

View 1 Replies View Related

SQL & PL/SQL :: How To Change Column Data In Row Format

Jun 1, 2012

I want to get the following format of data in row format using PLSQL. I want to do that in using a shell script also.

Suppose I have the data like this

123
45
2
789

how to write it in PLSQL as follows:
1427
25 8
3 9

View 3 Replies View Related

Changing Sql Plus Column Width / Format?

Sep 20, 2004

I am using SQL Plus v9.2.0.1.0 and am having trouble with the column widths. By way of example:

Let's say the column is a varchar(2) and the column name is called V9ABC12345. When I see the result of my query I only get the heading name as V9 ie the maximum width of 2 characters. The table has over 100 columns and I know I can specify the column width using the format command but I am using the select * from table_name command

How do I change my formatting to include the column heading by default?

View 6 Replies View Related

SQL & PL/SQL :: Convert Column To Rows Tabular Format

Jun 8, 2011

I have job, working hours, employee id, employee name in test_emp table. The job name and employees are not fixed in this table and it varies from project to project. We don't know how many employees are there and needs to be fetching on runtime.

I have the data like below

JOB---------WRKHR---EMPID-----EMPNM
ANALYST-----10------5478------RAMESH
MANAGER-----10------4258------SACHIN
LEAD---------10------4789------VIVEK
DEVELOPER---20------5632------ROHIT
ANALYST-----20------5843------VIRAT
MANAGER-----20------4789------VIVEK
PROGRAMMER-30------5479------SURESH
LEAD---------30------4258------SACHIN
DEVELOPER---30------5231------PRAVEEN

I need the output like below format.

JOB---------RAMESH--SACHIN--VIVEK--ROHIT--VIRAT--SURESH--PRAVEEN--TOTAL
MANAGER-----0--------10-------20-----0-------0------0---------0---------30
LEAD---------0--------30-------10-----0-------0------0---------0---------40
ANALYST-----10-------0---------0-----0------20------0---------0---------30
DEVELOPER---0--------0---------0-----20------0------0---------30--------50
PROGRAMMER-0--------0---------0------0------0------30--------0---------30
--TOTAL-----10-------40--------30-----20-----20-----30--------30--------180

View 6 Replies View Related

PL/SQL :: Order By Date Column With A Format Mask

May 16, 2013

10g- 10.2.0

This is my query

select to_char(exp_Date,'Mon-YYYY') dt, count(*) from exp_main
where exp_type like 'Income%Photo%'
group by to_char(exp_Date,'Mon-YYYY')
order by exp_date

When I run this I get :not a GROUP BY expression

If I remove the order by, it works fine.

Is there a way to order by since the output of the query is character-sorted.

I did search online most have suggested to use order by column_name. But does not work for me.

View 6 Replies View Related

JDeveloper, Java & XML :: Convert Column Data To XML Format

Jul 9, 2012

CREATE TABLE EMP(NAME VARCHAR2(10 BYTE))

INSERT INTO EMP VALUES ('C');
INSERT INTO EMP VALUES ('A');
INSERT INTO EMP VALUES ('T');

SELECT xmlelement("NAME",NAME) FROM EMP;

I am trying to convert column data to xml format, but I get this error message:

Quote:The query fails because all columns types are currently not supported. I am using:

Quote:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
SQLTools 1.5.0 Beta build 9 as EDITOR

View 7 Replies View Related

Server Utilities :: SQL Loader - Excel File Different Column Order Format?

May 8, 2011

I have an Excel spreadsheet that just had a format change. The fourth column is new. Order of columns in Excel is:

oldcol1
oldcol2
oldcol3
oldcol4
newcol
oldcol5

Sqlldr script is in order of the .csv file just listed. Oracle table is in order

oldcol1
oldcol2
oldcol3
oldcol4
oldcol5
newcol

When I run the sqlldr script, the information is loaded:

oldcol1
oldcol2
oldcol3
oldcol4
newcol in oldcol5

so that all the information is loaded incorrectly. Out side of having to change the table, is there anything I can do to make it load correctly?

View 8 Replies View Related

Reports & Discoverer :: How To Present Report In XLS Format With Proper Column Headings

Dec 12, 2011

I have a report and require the output in xls/xlsx (Excel) format. Currently following properties under System Parameters in Report Builder 6i have been made

Desname = The path for e.g: \C:sz-serverc$ihelp_workingxls
pt_sample_report.xls
Desformat = Delimited
Destype = File

The rest parameters has default settings I have not changed any of the rest User parameter.The report comes up in excel format but totally in distorted manner. i.e; All the column names of report are displayed first in vertical format followed by actual rows for the report.

The requirement is like this

[Select all] [Show/ hide]
Student ID Student Name Fathers Name Guardian Name
1 sample1 sample2 sample3
2 sample4 sample5 sample6

And it is displaying like this

Student ID Student Name Fathers Name Guardian Name 1 sample1 sample2 sample3
Student ID Student Name Fathers Name Guardian Name 2 sample4 sample5 sample6

View 2 Replies View Related

SQL & PL/SQL :: Integer As Decimal

Feb 18, 2010

I have a field in a table that is declared in the CREATE statement as an INT datatype. However, when I query that table using vb.net, the value comes back as a decimal.

How do you declare a field in Oracle as a true integer data type?

View 5 Replies View Related

SQL & PL/SQL :: Decimal Places In A Value

Dec 12, 2011

I have a table with column amount as decimal(11,2). I want to store the values as shown below

15.30

but it stores as

15.3

Is it possible to store the value in 15.30?

View 4 Replies View Related

PL/SQL :: Two Decimal Points

Aug 10, 2012

What would be the syntax if I want to use use '999G999G990D00' to show only two decimal points for outstanding_receipt_value in the following

((declared_quantity - delivered_qty) * po_unit_price) outstanding_receipt_value

View 3 Replies View Related

How To Convert Hex To Decimal For Each 2 Digits

Dec 6, 2008

Actually I got a data in hex values and i want to convert it to datetime format. But before i convert the hex value into datetime format, i must convert the hex values into decimal val first for each 2 digits of hex values. This is an example of the input data:-

This is input data in hex value:-
STARTTIME : 080b1317021a

This is decimal value after convert from hex to decimal:-
STARTTIME : 081119230226

My problem is how could i convert hex to decimal in oracle? Below are my coding:-

case
when substr(LOAD_NGNSM.STARTTIME, 1, 2) not between '00' and '99'
or substr(LOAD_NGNSM.STARTTIME, 3, 2) not between '01' and '12'
or substr(LOAD_NGNSM.STARTTIME, 5, 2) not between '01' and '31'
or substr(LOAD_NGNSM.STARTTIME, 7, 2) not between '00' and '59'
or substr(LOAD_NGNSM.STARTTIME, 9, 2) not between '00' and '59'
[code]....

View 5 Replies View Related

SQL & PL/SQL :: 2nd Decimal Place Not Appearing?

Jul 19, 2012

I am using Oracle Forms 6i. In my program unit i write into a .CSV and .TXT File. Here if the amount is 100.00 then it prints as 100 only. That is if the 2nd decimal digit is 0 it doesnt print it.

For example
10.00 -->10
10.01--> 10.01
10.10-->10.1
10.12-->10.12

I used to_char(nvl(&amount,0),'9999999.99') so if query select to_char(nvl(10.10,0),'9999999.99') from dual, then i get the output as 10.10.

Do you think this is a problem with Oracle forms or SQL or the formatting of .TXT & .CSV.

View 12 Replies View Related

SQL & PL/SQL :: Comparing Two Decimal Values

Nov 5, 2011

Recently i have started working on PLSQL coding. I have a requirement. Either error or un-processed record count is 90% of to be processed records then the script has to fail. Currently I am having a situation where error count is 1 and total to be processed is also 1.

in the below
V_ERR is error count
V_UPS is un processed count
V_PROCESSED_COUNT is total to be processed.

I am expecting PASS result but it is giving FAIL.

DECLARE
V_ERR NUMBER:=0;
V_UPS NUMBER:=0;
V_PROCESSED_COUNT NUMBER:=0;
NIN NUMBER;
BEGIN
V_PROCESSED_COUNT:=1;
[Code] .......

View 1 Replies View Related

SQL & PL/SQL :: IDs Decimal Values Are Truncating

Jul 10, 2012

If I perform a Query as below

SELECT SUM(total_amt) FROM Amont_dtls WHERE id=10;

It's giving me the value as

2.08166817117217E-19

The values in total_amount column for ID=10 is 0.01 and -0.01..Actually it should return 0.If I use the query like

SELECT SUM(0.01-0.01) FROm Amont_dtls WHERE id=10;

Then I am getting 0.Why it's a variation.And also the data type of the column is NUMBER.If I use

SELECT TRUNC(SUM(total_amt)) FROm Amont_dtls WHERE id=10;

Then I am getting 0.But some other ID's decimal values are truncating.

Ex: If i Use TRUNC
Instead of 28781947.48 it's showing 28781947

Instead of 590874.5 it's showing 590874

View 6 Replies View Related

SQL & PL/SQL :: How To Get Integer Portion From Decimal

Feb 21, 2012

Using Oracle SQL Developer 2.1.1.64 to run the queries & Oracle 11g.

I have two numbers in two colomns of an oracle table(colomn a & colomn b). I am trying to divide colomn a/colomn b and putting the results in colomn c & also in colomn d (all in the same table) using update commands

Eg: UPDATE MTOTABLE_PWELD pw SET pw.WELDO=(pw.pipe_length/12000);

But here is the real issue. In colomn d I only need the integer portion of the division value.

For example , when I divide colomn a/colomn b , let us assume that we are getting a value of 2.56. Then I want the value of 2 to go to colomn d.

I tried round((colomn a/colomn b),0). But it rounds off 2.56 to 3. I dont want that. I need the exact integer portion of the value to be seperated.

View 2 Replies View Related

SQL & PL/SQL :: No Rounded Decimal Numbers

Aug 5, 2011

how can show this query in this picture

select round(15.555,2) from dual appear final result 15.55 only

and this query don't be this result 15.56

because i have filed and always Decimal numbers be (15.555,2)

i want only result appear after two numbers and no rounded the numbers

View 2 Replies View Related

PL/SQL :: Number Not Showing Zero After Decimal

Feb 21, 2013

i have a column

NUMBER(13,4)

when i insert 234.000

in show only 234

where as when i insert 234.12

i shows 234.12

is there any setting by which i can show 234.0000

View 13 Replies View Related

SQL & PL/SQL :: Decimal Values Validation

Jun 5, 2012

CREATE TABLE TEST ( CREDIT_BALANCE VARCHAR2(11 BYTE), AWARDS_BALANCE VARCHAR2(11 BYTE) )

Insert into TEST(CREDIT_BALANCE, AWARDS_BALANCE)
Values ('1678.09', '1678.9');
Insert into TEST(CREDIT_BALANCE, AWARDS_BALANCE)

[Code]....

From valid records from test table those two columns,need to be insert into test1 table. We should validate the decimal values while selecting from test table.

using regexp.

View 26 Replies View Related

DECIMAL EXTERNAL In Control File?

Sep 13, 2010

In a control file, the code is as follows:

load data
infile 'C:\Documents and Settings\xxxxx\Desktop\abc.txt'
APPEND
PRESERVE BLANKS
INTO TABLE table1

[code]...

When I run the above control file in sqlldr, I'm getting the error as
Record 1: Rejected - Error on table table1, column column3.
ORA-01481: invalid number format model

In the table the column3 data type is NUMBER(6,2).: The column size in table is 6 and position of column3 in control file is only 4. Also if possible let me know how the same data (send me 2 dummy records) which exactly works for the above control file especially for column3 where decimal number comes in the flat file.

For generating the flat file, for column3, i'm using LPAD(:value,4,0) in the select query column list.

View 1 Replies View Related

Fix Number Of Digit After Decimal Point?

Mar 21, 2007

I have a small prob. I want an amount to be always 2 decimal places. I've used the Round function - Round(amount,2). the problem is that if the amount is only to 1 dp like 1.4. the above function will return 1.4. I want it to appear like 1.40

View 2 Replies View Related

SQL & PL/SQL :: How To Retrieve 6 Decimal Place Of Data

Oct 14, 2011

IF I have Salary Table,In that Salary Table,How to retrieve 6 decimal place of data .

View 16 Replies View Related

SQL & PL/SQL :: How To Convert Decimal Number To A Character

Aug 14, 2013

I want to convert decimal number 44 to character(" ' " i.e single quotation);

I have already tried to_char function but it's not working.

View 2 Replies View Related

Oracle 11g Different Decimal Char While Using Sproc

Jul 6, 2011

I am using an portuguese(brazil) Oracle 11g db,which should have ',' as decimal char. it seems to be working fine when i m trying to get numerical data via select query,but when i am trying to retrive the same data using out parameter of an sproc.I am getting data with '.' as decimal char.

View 2 Replies View Related

SQL & PL/SQL :: Selecting Numbers That Has Only 3 Digits After Decimal Point?

Mar 10, 2011

How to write SQL query for selecting all the values (numbers) in the table which has 3 digit after decimal point.

For example say A is the table it has following values

200.24
300.456
123.22
1234.344

From this I need only

300.456
1234.344

View 14 Replies View Related

SQL & PL/SQL :: Function That Multiply Number When There Is More Than 3 Decimal Point?

Mar 20, 2010

how to create a function that will multiply the number when the number has more than 3 decimal point.

Example.
123.012 - multiply by 10
123.0123 - multiply by 100
123.01234 - miltiply by 1000

Column A is the result of the number being multiplied according to decimal point.
Column B contains the multiplier used in column A.

View 10 Replies View Related

SQL & PL/SQL :: Data Type To Return Decimal Values In Function

Feb 26, 2013

I have the following database function.

GetRegionDetails(id in varchar2, o_lat out number, o_lon out number);

The problem is, the output values are returning as whole numbers ie. 38.108766567 is being returned as 38 and -78.16423574566 is returned as 78

what data type I should use so that my output is returns all the decimal values?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved