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.
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;
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
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.
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?
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.
+ 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.
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?
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%.
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.
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.
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
