how to reduce quantity from one table due to other table for example .i have a purchase a shampoo in 20 quantity and then sale this shampo 2 quantity...so i want now there should be 18 quantity of shampoo in purchase table automatically
Purchase tableSale tabel
Product IdSale id
Product Nameproduct name
QuantityQuantity
I have problem about sum record on form.
This is records display on form.
NO Merchandise Slip Quantity
------------------------------------------------
152501B002CAH2-002980016
162501B002CAH1-003528716
142501B002CAH1-003529812
122501B002CAH2-002979316
132501B002CAH2-002979612
202501B002CAH1-003529612
212501B002CAH1-003529716
192501B002CAH2-002979516
172501B002CAH2-002978716
182501B002CAH1-003529116
112501B002CAH1-003529212
42501B002CAH1-003530012
52501B002CAH2-002978916
[Code]....
Now, i want to count quantity follow slip_no and merchandise.
Example:
Merchandise :2501B002CA have 8 slip with quantity is 12 and 5 slip with quantity is 16.
Merchandise: 2501B001CA have 6 slip with quantity is 12 and 7 slip with quantity is 16.
Detail you can see file attach
-------
But i don't know how to do for result same above.
I have schedule table like below format:Materila Code Schedule No. Schdule Quanity Balance Quantity
ABC 1 500 500
ABC 2 300 300
ABC 3 200 200
If i received 600 quantity then data should update as below in schedule table:
ABC 1 500 0
ABC 2 300 200
ABC 3 200 200
Single material contain multiple schedules. how to programatically do the above updation.
getting the yearly qty distributed evenly across 12 months. I can do this in PL/SQL but want to know is it possible in SQL.
Database version 10g
create table test_tbl( qty number(22),
yr number,
key_val varchar(10)
);
insert into test_tbl values( 500000, 2013, '1');
insert into test_tbl values (56789, 2014, '2');
commit;
This is the output I need to get from a SQL select query
Key Month Year qty
--------------------------------------------------------------
1 1 2013 41667
1 2 2013 41667
1 3 2013 41667
1 4 2013 41667
1 5 2013 41667
1 6 2013 41667
1 7 2013 41667
[code]....
I want to write select query to get sum of quantity for each type of item ,in the table I have itemnum,itemtype and quantity ,type have value(A,B,C,....)
the result will be like that ex:
item.No A B C D ....
10 50 60 80 20
--- 50 will be sum(quantity) for item type A
how can I do that, I mean how can I retrieve many column from the same field?
i am trying to create a update trigger that allows any quantity reduction of 5 units and if the reduction is more than 5 units it blocks it and just reduces it by 5.here are two scenarios
SQL Update statement where the new Quantity value being smaller than 5 is allowed by the trigger.
-- old qty 20
update purchase_order
set quantity = 18
where po_no = 'PO11';
-- new qty 18
SQL Update statement where the new Quantity value being larger than 5 is only reduced by 5 by the trigger.
--old qty 25
update purchase_order
set quantity = 19
where po_no = 'PO15';
-- new qty 20
i just have the basic trigger code, but i think it is the math that i am not getting (i was never good at math)
create or replace trigger purchase_quantity_updt
before update of quantity on purchase_order
for each row
when (new.quantity < old.quantity )
begin
-- not sure what to put here
end;
which table do I have to use to get the Inventory On Hand Packed Quantity based on Oracle EBS Suite using backend?
View 2 Replies View Relatedi have data about 3 gb but my files in e:oraclexeapporacleoradataXE has grown to 16 gb
1)my e: drive has less space ,pls tel how can i fee some space and give it back to OS
2)is there any other place where i can free up some space from oracle and give it back to OS.
-----------------------
windows (2008)
---
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
"CORE 11.2.0.2.0 Production"
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production (2008)
NLSRTL Version 11.2.0.2.0 - Production
[code]....
is there any way to reduce the dblink timeout, i need to test if the remote server is up by testing the dblink throw 'select 1 from dual@link_name' but it take too long time 5-6 min., i need to make it 5 sec after that the exception tns timeout appear. I try with some sqlnet.ora parameters like INBOUND_ TIMEOUT but not work for me, also i try using tnsping but it also take long time when the remote server is down.
View 2 Replies View RelatedI have one very big table , says employee table, I need to keep less rows to do this
1. create table employee_backup as select * from employee
2. truncate table employee
3. insert into employee select * from employee_backup where rownum < 1000
is #3 sript is OK? if I only want 1000 rows ?
We have Oracle 10G database over Unix platform, Customer want to reduce the size of database as much as possible and the ami of customer to move the storage area of this database to other one. so we resize some datafiles and get lots of free space at mount point but while checking the utilzation of table is showing some what different as other. Below O/P:
%
% MaxPoss Max
Tablespace Name KBytes Used Free Used Largest Kbytes Used
------------------------------ --------------- --------------- --------------- ------ --------------- --------------- ------
*a DATA 45,875,200 8,740,992 37,134,208 19.1 1,728,512 100,663,248 45.6
*a HIGH_S_DATA 21,504,000 1,331,520 20,172,480 6.2 3,048,704 0 .0
*a HIGH_S_IND 15,360,000 853,568 14,506,432 5.6 1,661,504 0 .0
[Code]....
above all o/p is different, no able to understand it. is there any way to reset the HWM at Datafile level and how we reset the HWM of those tables having Materlized view?
i got a table and it had 5000 rows of data...ive deleted around 2000 to decrease the db size but i have no success. My harddrive is still showing the same size with no increase in mb.
I've looked at shrink etc methods but some are not compatible with 8i.
I take it the db is still reserving that those deleted rows thinking it may be used again which is the reason for no increase in space.
how to reduce the clustering_factor's value which appears in the user_indexes view?
in my table ,its clustering factor's value is so high:
SQL> SELECT UI.clustering_factor,UI.num_rows,UI.index_type,UI.distinct_keys FROM USER_INDEXES UI WHERE UI.table_name = 'TAWB_AWB';
CLUSTERING_FACTOR NUM_ROWS INDEX_TYPE DISTINCT_KEYS
----------------- ---------- --------------------------- -------------
83609 187603 NORMAL 187603
and its block numbers is 5063
SQL> SELECT COUNT(DISTINCT DBMS_ROWID.rowid_block_number(ROWID)) BLOCK_NUM FROM TAWB_AWB A;
BLOCK_NUM
----------
5063
How can I reduce the consistent gets of the sql:
select b.OFFER_ID,
b.OFFER_CODE,
b.OFFER_NAME,
b.OFFER_COMMENTS,
b.BAND_ID,
b.CAN_BE_BUY_ALONE,
b.PRICING_PLAN_ID,
b.PRIORITY,
b.STATE,
[code]...
I have already added the indexes for the outer sql to reduce the cost from 374 to 150. But the the consistent gets seems not reduce that much. I notice that the function will cost 6 consistents gets per execution. It seems it not very high. But if this function goes with the sql, it will cost a very high consistent gets, if I remove this function, the sql only cost 1903 consistents gets.
So what I am thinking there should be two ways to reduce the consistent gets..The first one is reduce the recursive call of the sql. The second is reduce the consistent gets of the function. (but it seems that the consistent gets in this function is very low, only 6.)
I have a view SV (say) which holds approximately 33,000 records. But, when I try to insert these many records into a table SV_T (say) it is taking huge amount of time i.e. 2-3 hrs (approx.)
how reduce the time taken for the insert.
After ran db health check, my database report gives the following details
dml_locks OK. dml_locks = 3396, transactions = 849.
In 11g database i have allocated 30 GB temp space now am seeing only 500 MB free space in database.
how to reduse temp space.
There are tablespaces where the current usage is crossed 90% and we are getting alerts.I want to know how much space required to add for these tablespaces ( datafiles) so that current usage can be reduced to 80 %. or 70 % Is the any SQL query to find this information . oracle 10 g database. .
View 5 Replies View RelatedI am getting the following error when i try to re size the data file.But the data file is having lot of free space.
ORA-03297: file contains used data beyond requested RESIZE value
Presently table space size is 220GB with 8 data files.As the process of the performance tuning we moved data to different table spaces.Now used space of the tablespace is 90GB.So I am trying to resize the datafile but it throw an error.
how can I reduce the size of ------------- when table is null. I m in sqlplus I typeSelect A,B,C,D,F,G,H from SOMEHERE where B='GOAT1';
if A is 10 char long
B is 50
c is 10
d is 30
e is 10
f is 50
if any of those don't have data it still outputs ----------------------------- (50) for B and tht covers the whole screenhow can I make is to show less if it null
One of our solaris machines is running Oracle 8.0.3
A table reached the 2 Gb size and oracle failed due to the operating system file size limitation.
The information in the table is not relevant and can be deleted, but the table contains a lot of indexes.
I would like to know the best procedure to delete the information and reduce the size of the file.
I have Following wjich takes some minutes to executes i want to be tune so this query Executes fast.
Query :
SELECT a.CHDR_EXCH_CD ,TMHR_EXCH_TM_CD,'S' Sec_type,
round(SUM (Decode(csdt_Depo_Typ,'I',(Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-RR'),csdt_stsc_cd,csdt_scp_qty)*csdt_scp_qty)-
(Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-
[Code]...
Explain Plan Result :
Plan
SELECT STATEMENT ALL_ROWS
Cost: 1,669 Bytes: 67 Cardinality: 1
15 HASH GROUP BY
Bytes: 67 Cardinality: 1
14 CONCATENATION
[Code].....
After i see result , no 4 in explain plan result gives TABLE ACCESS FULL . i want to be indexing on that how to do this..
This table MG_COLL_SCP_DTL have index like this
CREATE UNIQUE INDEX CSDT_PK ON MG_COLL_SCP_DTL
(CSDT_CHDR_TRANS_NUM, CSDT_PROD_TYP, CSDT_TRAN_SR_NO, CSDT_CHDR_CDTL_COLL_TYP, CSDT_CHDR_CDTL_COLL_TYP_CD,
CSDT_STSC_CD, CSDT_CHDR_CLNT_CD, CSDT_CHDR_CLNT_TM_CD)
[Code]....
How to Reduce cost ???
Using rownum in PL/SQL can significantly reduce performance and throughput of queries.
For example,
CODEselect *
from (select ...
from ...
join ... on ...
join ... on ...
left join ... on ...
where ...
group by ...)
where rownum < 500
takes much more time on a heavy loaded db than
CODEselect Y.*
from (
select X.*, rank() over(order by ...) rnk
from (select ...
from ...
join ... on ...
join ... on ...
left join ... on ...
where ...
group by ...) X) Y
where rnk < 500
I suspect it's because Oracle optimizer goals all_rows and first_rows.
how to reduce the system tablespace size
my system01.dbf size is 6gb
i want reduce from 6gb to 2gb
How to reduce the space usage in RMAN commands.
View 3 Replies View Relatedi just now installed oracle and import some schema to this new DB. but in OEM showing 40 % full of dump area
how to reduce the dump area size?
We have two tablespace xyz_ts & pqr_ts having 167 datafiles . We have to reduce this number of files. These tablespace having 580 GB size .
View 1 Replies View RelatedIs there any way to reduce the index creation time. I have one table which has 7700000 records and every day this table get truncate and we create with create table as select statement and then create the 4 indexes and each index took 5 minutes so in totality it took 20 minutes in index creation.
View 2 Replies View RelatedIn my insert query, Window sort takes longer time i.e. 93% of total execution time, How do i reduce this time? are there any tuning parameters availabe for this?
View 5 Replies View RelatedI am running full database backup it take 10hrs to complete.
Size of the database=590GB
Platform:Solaris 9
database version:10.2.0.5
using catalog database.
Is there any way to reduce the time ?
Is there any query to see how long does the backup runs?Monitor the backup time?