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 have a table which have two columns date on hourly basis and response time. I want to pull the previous date's data on hourly basis with the corresponding response time. The data will be loaded to the table every midnight.
eg: Today's date 23/10/2012
I want to pull data from 22/10/12 00 to 22/10/12 23
The below query is pulling the date as required but I am not able to pull the response time.
with a as
(select min(trunc(lhour)) as mindate, max(trunc(lhour)) as maxdate from AVG_HR)
SELECT to_char(maxdate + (level/25), 'dd/mm/yyyy hh24') as dates FROM a CONNECT BY LEVEL <= (1)*24 ;
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 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 calculate (by SQL) outstanding stock on a First In First Out basis per month?
Table A
Month Oustanding Stock Sales Expired Stock
January 200 0 0
February 100 100 0
March 100 0 0
April 50 150 0
June 50 0 100
July 50 0 0
Desired Results
Month Outstanding Stock
January 0
February 0
March 50
April 50
June 50
July 50
I have three table for the stock calculations. The structure are like this
Product_master
product_id number,
product_name varchar2(30),
company_id number(3),
rate_per_unit number(14,4)
Purchase_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
Sales_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
Purchase_return_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
Sales_return_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
I need to find out the valuation on particular sales date at FIFO method.
I need a query to divide the total consumption of an item into parts as it was stored in store on the basis of FIFO. The item that was stored first it will consumed first.
My sample data is given below.
DROP TABLE STORE_STOCK;
CREATE TABLE STORE_STOCK
(
no NUMBER(4),
vdate DATE,
code VARCHAR2(8),
clqty NUMBER(8,3),
dept_id NUMBER(4)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
[code]........
SQL>select * from store_stock
2 order by dept_id,vdate,no;
NO VDATE CODE CLQTY DEPT_ID
---------- --------- -------- ---------- ----------
359 21-JUL-12 012-042C 1050.1 19
2144 14-NOV-12 012-042C 990 19
1876 23-OCT-12 012-042C 1010 34
4006 12-MAR-13 012-043D 515.425 34
4086 15-MAR-13 012-043D 870 34
4495 13-APR-13 012-043D 498 34
6 rows selected.
SQL>select * from store_issue;
CODE IDATE DEPT_ID QTY
-------- --------- ---------- ----------
012-043D 24-APR-13 34 1650
012-042C 24-APR-13 19 990
Required Output will be
NO VDATE CODE CLQTY DEPT_ID ISS_QTY BAL_ISS
---------- --------- -------- ---------- ---------- ---------- ---------
359 21-JUL-12 012-042C 1050.1 19 990 0
2144 14-NOV-12 012-042C 990 19 0 0
1876 23-OCT-12 012-042C 1010 34 1010 640
4006 12-MAR-13 012-043D 515.425 34 515.425 124.575
4086 15-MAR-13 012-043D 870 34 124.575 0
4495 13-APR-13 012-043D 498 34 0 0
The total consumed against code '012-042C' is 990 for dept_id 19 and we have stored qty 1050.1 against goods receipt no 359 dated 12-jul-12. So have issued the whole qty against no 359 and balance is 0. For item code '012-043D' the total issuance is 1650 and we have issued it against 03 goods receipt nos.
select code,inv_date,inv_co_code,inv_co_name,inv_fnd_code,inv_fnd_name,inv_amount,inv_nofu
from retreport order by inv_co_code, inv_fnd_Code,inv_date
STAT INV_DATE CODE NV_CO_CODE INV_CO_NAME INV_FND_CODE INV_FND_NAME INV_AMOUNT INV_NOFU
----- ------ -------- ----------- ------------ -------------- ---------------------- ----------------------
I 28/06/2010 117 13 CAD 1 MCB 15104708.75 148375.7308
I 01/07/2010 13 CAD 1 MCB 2804.7464
I 30/08/2010 117 13 CAD 1 MCB 700000.00 6882.2380
I 30/08/2010 117 13 CAD 1 MCB 9500000.00 93401.8018
R 31/08/2010 117 13 CAD 1 MCB 39315.8646
R 08/09/2010 117 13 CAD 1 MCB 24515.9089
6 rows selected
STAT I = IN STOCK
STAT R = OUT STOCK
I need FIFO Query First in Fist out (INV_NOFU)
STAT CODE INV_CO_CODE INV_CO_NAME INV_FND_CODE INV_FND_NAME INV_AMOUNT INV_NOFU BALACNE
----- ------ ----------- ----------- ------------ -------------- ---------------------- ---------------------- ------------
I 117 13 CAD 1 MCB 15104708.75 148375.7308 148375.7308
R 117 13 CAD 1 MCB 39315.8646 109059.8662
R 117 13 CAD 1 MCB 24515.9089 84543.8773
I 13 CAD 1 MCB 2804.7464 87348.6237
I 117 13 CAD 1 MCB 700000.00 6882.2380 94230.8617
I 117 13 CAD 1 MCB 9500000.00 93401.8018 187632.6635
I am re-developing some forms which were designed before on some other machine.
I cant able to run the form and not even to compile them. I am getting an error message like THE OUTPUT OF THE FORMS ARE UNABLE TO ADJUST.
I have checked the canvas dimensions like height and width that they are matching with that of windows of the form. I have also checked the values in formweb file. Everything is correct up to my knowledge. I have also ckecked the setting of LCD screen like it is 1024 X 768 resolutions.
I am trying to discern the difference between Shrink and Move and their impact on the High Water Mark of a table.
My understanding is that MOVE in effect rewrites every row of a table ( hence why it can deal with row chaining ) whereas SHRINK basically moves existing rows in a table 'down' the table into any available free space. This is why MOVE takes a table lock whereas SHRINK takes a row lock. What I am trying to discern is - does MOVE and SHRINK effect the high water mark and does both reallocate space and give it back to free space for the tablespace ? I believe MOVE does reduce the HWM and give freed space back to the tablespace. I am not so sure about SHRINK.
"SUM_TOTAL_RECEIVED" ,this is Item name and i set all Calculation Properties correctly and when compile the form , i got an error
FRM-30405: Datatype for Sum/Avg/Variance/Stddev summarized item must be Number.
Item: SUM_TOTAL_RECEIVED
Block: RHCLANDEDCOSTDET_PROMPT
FRM-30085: Unable to adjust form for output.
I've created a form that is working on the basis of Invoice Processing. There I want to get the sum of all the total of the products * quantity..I'm getting this error
FRM-30425: Summarized database item must reside in a block with Query All Records or Precompute Summaries set to Yes.
Item: TOTAL
Block: SBM_SALES_PRODUCTS
FRM-30085: Unable to adjust form for output.
How do i run three scripts one after another automatically for daily basis. Say i have three scripts A,B,C and i want to run the three scripts A followed by B followed by C.
View 7 Replies View Relatedhow to create a view from table which containes rows from every minutes. In view I want to select only rows at 15 or 30 minutes interval.
eg. my base table has 60 rows for 1 hour. The created view must contain 4 rows on 15 minutes interval basis at the boundary of that interval.
O/P of view should be like this.
Value Time
100 00:00:00
200 00:15:00
300 00:30:00
400 00:45:00
500 01:00:00
There are 2 databases, database A and database B. Database A is Oracle 11.2.0.2 which runs on linux and Database B is Oracle 11.2.0.2 which runs on windows xp machine. In database A, there are 100's of tables which are being updated every 10 minutes or 15 minutes. For reporting purpose, the developer wants to run report for the tables. But since database A is being updated every now and then, generating reports takes almost 15 to 20 minutes. So the reports can be generated in Database B. Once in a day the database B should have the updated data from database A so that the reports can be generated in database B with less time. What could be the best solution for the database B to have the updated data on daily basis from database A in oracle?
View 3 Replies View Relatedi want to fetch records of emp whose hiredate between 1 sep to 30 sep of any year. i am using the below query but it show me wrong results.
select * from tran where to_char(timestamp,'DD-MM') between '01-SEP' and '30-SEP'
DB 10.2.0.2
OS Window Server
I need to create a script to take the oracle database backup on daily basis for windows. Which method will be good exp imp, rman or coldbackup?
In our case mostly chance system will crash and we need to OS install first and then restore the oracle home and full database.
How can we find a tablespace growth on a daily basis for a 11.2.0.2 database?
OS: AIX 6.1
We have a 2-Node RAC11g R2.0.3 installed on Linux 5.5.
My problem to have a private connection disconnected on daily bases at 12:00 PM and 3:00 PM ONLY,and come back life within 2 minuets,I am using cross-over cable to connect those private interface , So in the ocssd.log Stating :-
2012-09-18 15:06:43.735: [ CSSD][1096161600]clssnmPollingThread: node racmain1 (1) at 50% heartbeat fatal, removal in 14.340 seconds
2012-09-18 15:06:50.752: [ CSSD][1096161600]clssnmPollingThread: node racmain1 (1) at 75% heartbeat fatal, removal in 7.330 seconds
2012-09-18 15:07:19.821: [ CSSD][1096161600]clssnmPollingThread: node racmain1 (1) at 90% heartbeat fatal, removal in 2.480 seconds, seedhbimpd 1
And the instance is restarted accordingly.
I need any good suggestion basically I have two different location
1- Factory
2- Head office
I have developed PRODUCTION module in factory and it is working fine and now I want to send data on daily basis to head office therefore I develop a form which will create backup in export format (.dmp) then user will send via email to head office.
Backup file should be save in pre-defined location then user will use a form which I developed for loading data into head office there are two different buttons in this form;
First is used to load data, actually first I load data in a temporary user which creates whenever user will press this button. Second is used to copy data in application user but first it checks if data exists then update otherwise insert.
i create form on which 3 window, 3 Canvas, 3 blocks are there.
1)first block contain 3 items from_date and to_date(nondatabase)and find button
2)second block contains 3 items segment1, quantity and payment_terms
when i enter 2 dates and click on find button 2nd window open. segment1 and quantity between this 2 dates comes from base table like
select segment1,quantity
from po_lines_all pll, mtl_system_items mts
where pll.ITEM_ID=mts.INVENTORY_ITEM_ID
and creation_date between :FROM_DATE and :TO_DATE
and payment_terms should enter manually
3)2nd block contains summary button when i click on it, it save segment1, quantity, payment_terms and open new window which contain segment1 ,qty, payment_terms and when i click on summary button data comes from custom table which group clause means
suppose
item qty terms
a 2 2days
a 3 2days
b 3 3days
then on third form i shows like this
a 5 2days
b 3 3days
i done all this things but problem is that
suppose once data between 21-aug-2010 and 24-aug-2010 comes on second form , i enter payment_terms and save it. sumaary form also run properly but if another user enter date between 22-aug-2010 and 30-aug-2010 then it will shows data between 21-aug-2010 to 24-aug-2010 from custom table i.e with payment terms as earlier it save and from 25-aug-2010 to 30-aug-2010 it shows data from base table
like
item qty terms date
a 2 2days 21aug
a 3 2days 22aug
b 3 3days 24aug
b 4 NULL(ENTER MANUALLY) 26aug
c 5 NULL 27aug
There is a table called cd_details. It has fields like id, name, language, type etc.I need a form where the user can search the database on basis of language and name and then display the other details. I used a data block wizard and given a push_buttonwith (execute_query). But it's very clumsy. For eg, if the user presses next the next button then all the records are being displayed. Is there any way to do this thing in a better way?
View 13 Replies View RelatedI use windows 2003 server, oracle 10.2.0.4.0.My question is related to maintaing a standby database
1. Do we need to take RMAN full backup of standby database on a regular basis? At the moment I dont take backups of standby database.
2. I have FRA on standby database. The archivelogs from primary go to FRA on standby. I delete my archivelogs as follows:
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-3';
Though the archivelogs are delete there are still empty folders of archivelogs that are not deleted. How to get rid of them automatically.
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 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 RelatedI have implement multi master replication between two server.
How much amount of data transfer over the network? How to calculate this value?