SQL & PL/SQL :: Value Distinct Count To Be Multiplied With Different Amount?
Oct 8, 2013
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.
View 12 Replies
ADVERTISEMENT
Feb 1, 2012
I have following query which gives currency code from two different tables. I would like to get the distinct count of currency codes from these two different columns.
SELECT eb.person_seq_id, eb.bonus_amount, eb.currency_cd, ed.currency_cd_host
FROM fr_emp_bonuses eb, fr_emp_details ed, fr_periods p
WHERE eb.person_seq_id = ed.person_seq_id AND ed.period_seq_id = eb.period_seq_id
AND ed.period_seq_id = p.period_seq_id AND p.period_status = 'CURRENT'
AND eb.bonus_amount >= 0 AND eb.person_seq_id = 3525125;
This query gives following result
3525125240000USDINR
35251250 USDINR
352512560000 USDINR
352512550000 USDINR
There are two distinct currency codes (USD, INR) and total amount is 350000. So I am looking for a query to give me the following result
3525125350000 2
View 9 Replies
View Related
Sep 26, 2005
how to count different characters from a string ....
View 7 Replies
View Related
Jun 7, 2013
Is there a way to export records based on a select distinct into different xls or csv files in oracle? I could be using SQL Developer.
The following will generate 74 unique values which each contain approx 3000 records.
select distinct(telespornr from vingelen2012
View 25 Replies
View Related
Feb 21, 2013
I think this is easier than I think but all I want to do is count the distinct rows in a table.
WITH data AS
(SELECT 1 id1, 1 id2, 111 val1
FROM dual
UNION ALL
SELECT 1, 2, 222
FROM dual
UNION ALL
[Code]..
seems to get what I want
ID1VAL1CNT1
111114
212224
321114
422224
but can the count be done in a stand alone query such as??
SELECT DISTINCT id1,
val1,
COUNT(*) over() cnt1,
COUNT(*) over(PARTITION BY id1) cnt2
FROM data
ORDER BY id1, val1
NOT what I want
ID1VAL1CNT1CNT2
1111152
2122252
3211153
4222253
View 5 Replies
View Related
Mar 18, 2010
the below is the csv file data. 1st value is for transaction id, 2nd one is for order id and 3 rd one is category_id
"1","45678","a"
"2","45478","b"
"2","45278","b"
"3","45678","d"
"4","45278","e"
I am reading the above file and need to return the total no. of distinct transaction id from the file . How can i acheive this? In the above case distinct transaction id count is 4
View 15 Replies
View Related
May 27, 2013
i have a table with a clob column and i have 150 records i want retrieve distinct values from the clob using distinct operator on clob will not work
View 1 Replies
View Related
Aug 4, 2009
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
View 6 Replies
View Related
Jul 28, 2012
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.
View 8 Replies
View Related
Aug 8, 2013
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.
View 5 Replies
View Related
Nov 29, 2012
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
View 3 Replies
View Related
Aug 6, 2013
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 Related
Mar 1, 2012
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 Replies
View Related
Mar 1, 2011
I have implement multi master replication between two server.
How much amount of data transfer over the network? How to calculate this value?
View 2 Replies
View Related
Jul 16, 2013
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.
View 5 Replies
View Related
Oct 18, 2010
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.
View 7 Replies
View Related
Mar 12, 2013
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.
View 4 Replies
View Related
Jun 24, 2010
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+
View 7 Replies
View Related
Jul 25, 2011
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?
View 1 Replies
View Related
Aug 28, 2013
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.
View 22 Replies
View Related
Dec 14, 2010
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 Related
Jun 16, 2010
I 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.
View 9 Replies
View Related
Dec 2, 2011
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
View 1 Replies
View Related
Apr 19, 2009
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.
View 3 Replies
View Related
Oct 3, 2011
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?
View 7 Replies
View Related
Nov 21, 2011
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.
View 4 Replies
View Related
Oct 30, 2013
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.
View 1 Replies
View Related
May 10, 2013
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.
View 5 Replies
View Related
Oct 10, 2012
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
View 2 Replies
View Related
Nov 16, 2009
When we execute select count(*) from table_name it returns the number of rows.
What does count(1) do? What does 1 signifies over here? Is this same as count(*) as it gives the same result on execution?
View 13 Replies
View Related
