I want to convert an amount value in 99,999,999,990.99 format without using TO_CHAR function. I was trying to use regex. Is there any other function through which I would be able to do this?
View 6 RepliesI have a very simple table with 2 columsn. As_of_date is one of the column. This column is "Date" data type.
When I use distinct clause inside a to_char function it gives the following error:
ORA-00936: missing expression
00936. 00000 - "missing expression"
The Sql is
select to_char(distinct(as_of_date),'mm-dd-yyyy') from sales
I can't see any syntax error in the sql..but forsome reason, it doesn't work.
I need to use an outer join outside a to_char
to_char(the_date_field,'YYYYWW')(+) = '200815'
This throws up the error ORA-00936 missing expression.
I know it will work if I put the (+) right after the date before the mask but it need to check the week rather than the date.
I have tried using a function based index without success.
I have a problem when trying to create a PLSQL function based on an XML extraction query.
I have three dummy tables:
SQL> get create_address
1 create table ADDRESS
2 (
3 id NUMBER not null,
4 house_number NUMBER,
5 house_name VARCHAR2(20),
6 street_name VARCHAR2(30),
[code]....
And the following dummy data for these:
1 insert all
2 into ADDRESS (ID, HOUSE_NUMBER, HOUSE_NAME, STREET_NAME, CITY, COUNTY, POSTAREA, POSTSTREET)
3 values (1, 1, '', 'Tube Street', 'Norwich', 'Norfolk', 'NF12', '2DF')
4 into ADDRESS (ID, HOUSE_NUMBER, HOUSE_NAME, STREET_NAME, CITY, COUNTY, POSTAREA, POSTSTREET)
5 values (2, 5, '', 'Dave Street', 'Edlington', 'Kent', 'CT34', '8GH')
6 into ADDRESS (ID, HOUSE_NUMBER, HOUSE_NAME, STREET_NAME, CITY, COUNTY, POSTAREA, POSTSTREET)
[code]....
So far so good then. But, what I want to create is a function where I can pass in an id value and return the corresponding XML CLOB.
So I try, very simply, this:
SQL> get get_xml_data
1 create or replace function get_xml_data(p_id in number) return clob is
2 Result clob;
3 begin
4 select xmlroot(xmlelement("HomeData",
[code]....
And, alas, i'm greeted by this:
SQL> /
Warning: Function created with compilation errors.
SQL> sho err
Errors for FUNCTION GET_XML_DATA:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/5 PL/SQL: SQL Statement ignored
24/63 PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got -
SQL>
I've tried to redo the query in several different ways but so far nothing.
how a number can be displayed using comma separation without using TO_CHAR function.
NUMBER 3455678.05 should be displayed as 3,455,678.05 NUMBER 3455678 should be displayed as 3,455,678
Is there an oracle function that returns the current fiscal year in format (2012/2013)?
View 9 Replies View RelatedI have two tables : oa_membership_dtl(in this created_by field is varchar2(200 byte) ,oa_partner_usr_dtl(in this table partner_userid is number(8,0) i need to do join on above fields.
I am using following two queries:
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on to_char(partner_user.partner_userid,'9999')=membership.created_by
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on rtrim(ltrim(partner_user.partner_userid||' '))=rtrim(ltrim(membership.created_by))
by using first data is not fetched but 2nd is working fine , i am getting the matched records using 2nd query.
whats the diff between to_char and || symbol?
I am using below code to spell number in words
create or replace
function spell_number( p_number in number )
return varchar2
-- original by Tom Kyte
-- modified to include decimal places
[Code]....
The number amount is : 9899.25
I am getting the output is:
NINE THOUSAND EIGHT HUNDRED NINETY-NINE POINT TWO FIVE
But i want output should be
NINE THOUSAND EIGHT HUNDRED NINETY-NINE AND Twenty five FILS ONLY
where i need to modify the code.
Files is equal to cent as per DOLLOR currency
I need to put amount '0' if the row exist but has no amount in my "where " conditions. the orginal commad is :
select t.aaa, count (t.bbb), sum (t.ccc) from nrb t where t.vvv IN ('3','4','5','6','D','E','F') and t.ddd like '50%' and t.eee >= TO_DATE('2012/03/21','YYYY/MM/DD') and t.eee <= TO_DATE('2012/07/21','YYYY/MM/DD') group by t.aaa order by t.aaa
and the result is : "result" tab in excel atached file.i need this result: "result 2" tab in excel atached file.
I need to get payment amount. For example i have one payment for 3 invoices.I need to show in my report for this payment 3 invoices:
invoice_number, invoice_amount, payment_amount.
Total payment is $10,000.
Invoice A: $5,000
Invoice B: $5,000
Invoice C: $5,000
So,
for Invoice A payment is 3,000$,
for Invoice B payment is 2,000$,
for Invoice C payment is 5,000$.
This is that i need to get.In action payment amounts are after withholding tax and i need to get amount before.
i want to get balanced amount based on max(suid ).
my query is
create or replace procedure prc_stk_upd_pur(v_comp_suid in i_purchase_h.pih_company_suid%type,
v_area_suid in i_purchase_h.pih_area_suid%type,
v_invoice_dt in i_purchase_h.pih_invoice_date%type,
v_pih_suid in i_purchase_h.pih_suid%type,
v_type in i_purchase_h.pih_type%type) is
[code]......
my output is
SEFF_SUIDSEFF_INV_DATESEFF_LOT_SUIDSEFF_LOC_SUIDSEFF_TRAN_SUIDSEFF_TRAN_TYPESEFF_COMP_SUIDSEFF_AREA_SUIDSEFF_STOCK_INSEFF_STOCK_OUTSEFF_CURRENT_STOCKCREATE_USERCREATE_TIMESEFF_RATE_USDSEFF_RATE_LCURNSEFF_PID_SUID
1121411/29/20121317151346LP11115720.00020.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14107665.30601382
2121511/29/20121317151346LP11115755.00055.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14097665.30001383
3121611/29/20121317151346LP11115730.00030.000SYSTEM29-NOV-12 06.53.45.000000 PM0.440729.08401384
my output should come
SEFF_SUIDSEFF_INV_DATESEFF_LOT_SUIDSEFF_LOC_SUIDSEFF_TRAN_SUIDSEFF_TRAN_TYPESEFF_COMP_SUIDSEFF_AREA_SUIDSEFF_STOCK_INSEFF_STOCK_OUTSEFF_CURRENT_STOCKCREATE_USERCREATE_TIMESEFF_RATE_USDSEFF_RATE_LCURNSEFF_PID_SUID
1121411/29/20121317151346LP11115720.00020.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14107665.30601382
2121511/29/20121317151346LP11115755.00075.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14097665.30001383
3121611/29/20121317151346LP11115730.000105.000SYSTEM29-NOV-12 06.53.45.000000 PM0.440729.08401384
I have oracle 11gr2 database on linux os. It's total sga size is 500mb only. Now, if uses wants read the 1gb of data from database, then there is no sufficient memory in buffer cache. so how it will works. the transaction will get successful or it will fail.And i have another doubt, does oracle can read the data from memory only or it can also read directly from disk.
View 11 Replies View RelatedI have implement multi master replication between two server.
How much amount of data transfer over the network? How to calculate this value?
I've a form that allow the user to enter information & save it. in the form there is text_item called AMOUNT with multiable records.
I want when the user press SAVE button, a popup message shows the number of records enterd & total amount in all records.
EX:
AMOUNT
1000
200
3000
After clicking Save button a popup message shows ( you enterd 3 records & 4200 $ ) OK ? Cancel?
this my code in WHEN-BUTTON-PRESSED trigger
if :amount IS NOT NULL THEN
declare
cnt_record number := 1;
cnt_amount number :=:amount;
begin
go_item('amount');
[code]....
BUT !! when I test the form nothing happened.
I have a table and below are the rows:
create table employee_rev(employee_id number, month_name varchar2(10), month_end date, financial_year date, revenue_amt number)
Begin
insert into employee_rev values(111, 'DEC-09', '31-DEC-2009', '01-APR-2009', 1300);
insert into employee_rev values(111, 'JAN-10', '31-JAN-2010', '01-APR-2009', 1000);
insert into employee_rev values(111, 'FEB-10', '28-FEB-2010', '01-APR-2009', 800);
insert into employee_rev values(111, 'MAR-10', '31-MAR-2010', '01-APR-2009', 1000);
insert into employee_rev values(111, 'APR-10', '30-APR-2010', '01-APR-2010', 1000);
insert into employee_rev values(111, 'MAY-10', '31-MAY-2010', '01-APR-2010', 1100);
insert into employee_rev values(111, 'JUN-10', '30-JUN-2010', '01-APR-2010', 2100);
End;
I also need a YTD (Year to Date) field at the last which is sum of current month revenue_amt and sum(revenue_amt) for previous months for a particular financial_year_date.
The output should be:
Person_ID Month End Date Year Date Rev YTD
111 Dec-09 31-Dec-09 1-Apr-09 1300 1300
111 Jan-10 31-Jan-10 1-Apr-09 1000 2300
111 Feb-10 28-Feb-10 1-Apr-09 800 3100
111 Mar-10 31-Mar-10 1-Apr-09 1000 4100
111 Apr-10 30-Apr-10 1-Apr-10 1000 1000--change in financial year
111 May-10 31-May-10 1-Apr-10 1100 2100
111 Jun-10 30-Jun-10 1-Apr-10 2100 4200
I guess this should be achievable using some analytical functions, but I am unable to get the desired output.
I have the below SQL code -
SELECT DISTINCT
opr, CAST (distinctcount AS REAL) * 0.02 AS total, distinctcount
FROM ( SELECT DISTINCT opr, COUNT (*) AS distinctcount
FROM out
WHERE code = '22'
AND timestamp LIKE '201303%'
GROUP BY opr)
I would like this value 'distinctcount' to be multiplied with different amount for period January till June and from June to December. For instance for period (January till June) to be multiplied with 0.02, for period (June to December) to be multiplied with 0.03.
My problem:
+ Oracle 11g
+ Centos 5.5
+ I create many users by shell script (user01, user02, user03 ..... user0n)
+ How can I connect all user to Oracle database in Centos (may be create connect.sh) - command line env ?
......
// user01 , user02 ... ---===> connect Oracles ?
My idea: using fork to create multi sqlplus to connect but it cant o_0
If all users can connect to Orcl db, I can count real user connections.
Is there a way to force a query to ALWAYS bring back a certain amount of rows, even if some of the rows are blank?
I know there's a way in MSSQL but not sure how to in SQL+
extract a huge amount of data from a couple of views... the problem is that they want it in TXT files with fixed record length. There will be like 6 files, for a total amount of about 10GB.
export those tables in the fastest possible way? If I'm not mistaken exp and expdp can't create txt files, so do I really need to use utl_file or spool?
I got this intertesting request from one of my client some time ago and is still trying to find an answer.
Getting total sales dollar amount from two tables.
T1
Item_no(number)Total_Sales_Number(number)
160
250
35
420
T2
Item_no(number)Sales_Date(date)Sales_Number(number)Sale_Price(number)
12012-01-012015
12012-02-153012
22012-01-104016
22012-03-053018
32012-02-011020
Required to generate report to total sale dollar amount by combining information from T1 and T2 (matching with item_no). Rules are
a)Formula is Sales_Number*Sale_price
b)Choose the earliest sale record to latest by Sales_Date with the sum of Sales_Number is less or equal the Total_Sales_Number from T1.
c)If the sum of Sales_Number is less than the Total_Sales_Number from T1, then the use a fixed price for all items, say $10
d)If a item is not in T2 then use $10 for the price
For example,
item_no 1:
Total sale dollar amount = 20*$15+30*$15+(60-20-30)*$10
item_no 2:
Total sale dollar amount = 40*$16+(50-40)*$18
item_no 3:
Total sale dollar amount = 5*$20
item_no 4:
Total sale dollar amount = 20*$10
The current logic is done by using cursor. Can this be done without cursor using such as analytical statements? The Oracle DB is 9i.
I am creating 5 dbms_job at run time in side a stored procedure.But when I execute that procedure, all the job get 100%cpu and as a result other process does not get response. so my question is can we limit the dbms_job to use a defined amount of cpu%.
View 9 Replies View RelatedI want to adjust 2900 against certain amount from table on fifo basis.
DROP TABLE ABC
CREATE TABLE ABC(ID NUMBER PRIMARY KEY,AMT NUMBER);
INSERT INTO ABC VALUES(1,1000);
INSERT INTO ABC VALUES(2,2000);
INSERT INTO ABC VALUES(3,3000);
Can i do it using SQL. I know that it can be done using PL/SQL but i just wanted to confirm if its possible using SQL.
I'm working on a project and I can't figure out the procedure I will need to use. I've got a sysdate field in my "Calls" table which generates a call date + time, however I need to insert a severity level of the call after a certain amount of time, e.g. after 1 hour level turns from level 4 to level 3 and so forth until reaching level 1 after x amount of time. I know I'd need to put in an else if statement within this procedure I just can't work out how to do it with the sysdate field that has also been created.
A bit of info about the table, table name = calls, column 1 = date_time and column 2 is called severity_level
I have been trying to construct a query in Oracle that allows me to do the following:
For example if I have the data below:
EmpNo DOB SickDays
Alex 445 15/06/1985 7
Tom 778 22/08/1981 4
James 992 07/10/1978 5
I need to write and a query to lists the employee number and the amount of days sick that they have had and also add a column that compares the number of sick days to the average number of suck days by ALL employees.
I can calculate the average sick days etc, but It wont see to allow me to find the difference between that and the amount of sick days that each person has had. I have tried this many ways and have not been able to come up with a solution.
select sum(bytes/1024/1024) from dba_segments where owner='IPPS';
Does the above finding means that IPPS has use up 'this amount' of space in the database from all his objects?
I have table which has 240 columns.
Here is the list of column data type.
VARCHAR2(50)
TIMESTAMP(6)
VARCHAR2(25)
VARCHAR2(25)
NUMBER(15,2)
VARCHAR2(50)
NUMBER(1)
[code]....
The table has 64 partition.. When i count the table(select count(*) from table), i see close to 22 million records.
SQL> select (num_rows*avg_row_len)/1024/1024/1024 GB,num_rows,avg_row_len
2 from dba_tables
3 where table_name='TRX_TAB';
GB NUM_ROWS AVG_ROW_LEN
---------- ---------- -----------
74.9393936 21871585 3679
SQL>
It is supposed to take 75 GB. But this table is consuming 135 GB now. It is occupying 8 GB per day.
I'm using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production and TNS for Linux: Version 11.2.0.3.0 - Production.Requirement is to create a script to add a LIST partition to some selected tables in a schema (tables do not have data, they are not partitioned). There are about 300 such tables (can vary) and their names are maintained in a separate table. Example -Existing table
-CREATE TABLE test_part(id number (2),
name varchar2(20),
audit_userid number (9));
Expected table
-CREATE TABLE test_part(id number (2), name varchar2(20), audit_userid number (9))
PARTITION BY LIST (audit_userid) (PARTITION p1_audit_userid VALUES (1));
Ultimate goal is to add more partitions based on the amount of data to be populated.
I need to add the calculation of the amount in an invoice line.
I write the item description, the price, the quantity and the tax. Which is the best solution to calculate the total price of the line. I think to add a SQL function after the button “Add line” is clicked.
i have a large OLTP database and we are doing table stats copy amount subpartition to save the load on system. while doing an copy default subpartition stats: I see the following error:
SQL> exec DBMS_STATS.COPY_TABLE_STATS('cusms','STATUS_HIST','P_VDEF_10_2012_S100','P_VDEF13_10_2012_S100',force=>true);
BEGIN DBMS_STATS.COPY_TABLE_STATS('cusms','STATUS_HIST','P_VDEF_10_2012_S100','P_VDEF13_10_2012_S100',force=>true); END;
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
How can I write a query to combine amount1 and amount1 into single column amount?
create table a (
id_code varchar(20),
invoice_date date,
invoice_type number,
interest number,
advance number,
invoice number
);
[code]....