SQL & PL/SQL :: Combining New Logic Using Old And New Cursor
Aug 22, 2011
I have to implement a new logic such that , Old logic which pulls data will stay in place.now New logic pull data form SAP . So i have implemented the new logic in to the existing cursor as below.
i.e.
Cursor new_sap
old_query
Union
New_query;
I have 3 new cursor like dis. How to process these old and new cursors in FOR loop.. Ex:
For c1 in C2(order_number)
if order_source is sap then ( dis filters old data which is not in SAP)
new query in cursor should process
else
old query in cursor should process
end if;
How will i do this programatically.
View 1 Replies
ADVERTISEMENT
Jun 15, 2012
I had some query.
Actually had some table with following information
Here A,B,C are the vendors and Code is 21 and Amount is mentioned
Code Vendor Amount
21 A 56
21 A 62
21 B 31
21 C 100
21 C 100
Want to display top three vendor for that Code based on Vendor Amount
Code Vendor Amount
21 C 200(100+100)
21 A 118(56+62)
21 B 31
How should i do this?
View 10 Replies
View Related
Jan 17, 2011
I'm having some trouble combining some data. I've done a lot of research into joins and merges, nothing really seemed to do what I needed. I could be over thinking it.
Basically, I want the query to look at the "info" table from a specified time-span (1 to 7). I want it to look at the source of the data and if it is "one_better" always use that data for that specific time. Otherwise, return the data from "one". The results don't care about the source of the data.
The "info" table I'm working with looks like this:
TIMEDATASOURCE
1DATA1one
1DATA1betterone_better
2DATA2one
3DATA3betterone_better
4DATA4one
4DATA4betterone_better
5DATA5 one
7DATA7one
I would like the results to look like this:
TIMEDATA
1DATA1better
2DATA2
3DATA3better
4DATA4better
5DATA5
7DATA7
View 1 Replies
View Related
Aug 15, 2012
I have a table with 4 records with start_date and end_date fields
start_date end_date
1/1/2003 1/4/2005
1/1/2004 5/3/2006
1/1/2007 1/4/2008
1/1/2008 6/3/2009
as you see records 1,2 and records 3,4 have overlapping dates. I need to combine them to get continuous intervals. The result should be:
start_date end_date
1/1/2003 5/3/2006
1/1/2007 6/3/2009
Is it possible to do by SQL query?
View 10 Replies
View Related
Oct 28, 2010
A basic select and group by query I am optimising for my Database course has returned results that indicate it will perform better on a clustered index when returning a smaller number of rows (5% of the largest table) and on a hash clustered index when returning higher volumes (50% and 80%). I understand that it is possible to use more than one index type on a table to improve performance, but I am struggling to understand how I might establish a hash cluster and a cluster on the same table? and then use hints to drive the query down one access path or the other.
Site admin - this site is a very useful resource.
View 5 Replies
View Related
Apr 8, 2012
1. I Wnat to analyze the buffer cache hit ratio. This is what i did.
DECLARE
bufcac NUMBER(10, 2);
BEGIN
[Code]....
2. I would like to analyze the PGA and determine what percentage out of the maximum allocated PGA is being used. I tried the code below but can't find the percentage. Sad
SELECT NAME,
To_char(Decode(unit, 'bytes', VALUE / ( 1024 * 1024 ),
VALUE), '999,999,999.9') VALUE,
[Code]....
Apart from the little problem I have with number 2, not figuring out how to get the precentage seeing that all the data is store in the field VALUE.
I would also like to create a view that has both 1 and 2 in it, but whenever I try creating it, I got and error stating and INTO clause is expected.
View 10 Replies
View Related
May 12, 2013
I have three select queries. Each of them returns a single column. I want the result of these queries into a single table..
I tried this way..
select * from
(first select),(second select),(third select);
this gives duplicate rows...
View 4 Replies
View Related
Dec 11, 2012
Is there any way to combine an identifier and select statement in PL/SQL when using the insert into command?
e.g.
DECLARE
name := 'BOB';
BEGIN
insert into mytable(NAME, SLOWEST_LAP, FASTEST_LAP) name, (select min(time), max(time) from lLAP_TIMES);
END;
In the above statement I am trying to insert the identifier "name" (BOB) into MYTABLE.NAME, along with the result of the select query from the table LAP_TIMES ... min(time) into MYTABLE.SLOWEST_LAP, and, .... max(time) into MYTABLE.FASTEST_LAP.
If the above is possible in one statement how would I also combine an identifier, with two select statements into an "insert into" statement?
e.g.
name := 'BOB';
insert into mytable(NAME, SLOWEST_LAP, FASTEST_LAP, EVENT) name, (select min(time), max(time) from LAP_TIMES), (select race_event from MEET);
In the above example I am also trying to insert the result from MEET.RACE_EVENT into the column MYTABLE.EVENT
View 4 Replies
View Related
May 31, 2013
Is there a way to prevent Apex from combining labels into an "other" category in the charts?
View 4 Replies
View Related
Dec 28, 2010
my table have four cols such as
start_value trans_value result_value create_time
1 4 5 20101018 22:33:40
5 -2 3 20101018 22:33:40
3 3 6 20101018 22:33:40
6 -4 2 20101018 22:33:40
2 +3 5 20101018 22:33:40
5 9 14 20101018 22:33:40
but the data is not in the logic order they are confusing and there no origina sequence such as
start_value trans_value result_value create_time
1 4 5 20101018 22:33:40
3 3 6 20101018 22:33:40
5 -2 3 20101018 22:33:40
2 +3 5 20101018 22:33:40
5 9 14 20101018 22:33:40
6 -4 2 20101018 22:33:40
how to get they in logic order.now i have to create a combination in mem and use loop to get the logic order.
View 6 Replies
View Related
Apr 28, 2012
i have three tables: ot_req, ot_po_breakup, and ot_po. when a row is inserted into the ot_po table reffering to ot_req,there is a trigger which creates a seperate reocrd in ot_po_breakup with the details of ot_req ot_req.ri_sys_id as pb_ri_sys_id and ot_req.ri_qty as pb_ri_qty ,pi_sys_id as pb_pi_sys_id .upto this part is okand when i insert also the logic is okay.
i have created a trigger to update the rows in the ot_po_breakup table after insert on ot_poin order of pb_ri_sys_id and pb_pi_sys_id and try to update the values in the columns pb_ves1q, pb_ves2q, and pb_ves3q order. i am trying to take the quantities in ot_po.pi_qty and insert them into the pb_ves1q, pb_ves2q, and pb_ves3q columns of ot_po_breakup where those columns are empty (0 or null) such that the sum of those three columns for that row does not exceed the pb_ri_qty in that row.
My problem is i need to mofify my trigger to do the update and delete , that is whenever the user is updating the column ofot_po.pi_qtythe qty should in ot_po_breakup should get updated accordingly and sum of pb_ves1q,pb_Ves2q and pb_ves3q should be equal to pb_ri_qty and do the same for delete as well
CREATE TABLE OT_REQ
(
RI_ITEM VARCHAR2(20 BYTE),
RI_SYS_ID NUMBER,
RI_QTY NUMBER
[code]...
View 14 Replies
View Related
Feb 19, 2013
what is the right syntax to use the below decode logic.
Decode(EXTRACT(MONTH FROM sysdate) > 2, ltrim_blanks(GLAMOUNTS.CR_AMOUNT_02) + ltrim_blanks(GLAMOUNTS.DB_AMOUNT_02), 0)
View 12 Replies
View Related
Mar 1, 2011
I need to run a query for students that took ALL the following courses: 6710, 6711, 1032, 1035 and ANY of the following courses: 3061,3065
here is my query:
=SELECT distinct student_number, last_name, first_name, gender, ethnicity, students.grade_level, lunchstatus, course_name, course_number ,termid
from students inner join storedgrades on students.id=storedgrades.studentid
where students.schoolid='0976111'
and (students.grade_level >'9' and students.grade_level <= '12')
and (course_number = all('6710', '6711', '1035')and course_number=any('1031','1032','3061','3065','3062', '3401', '3082'))
order by last_name, first_name, grade_level, course_number
The problem is that I'm not getting any results but I know for a fact that some students took the required courses. On a side note, if I change the ALL to ANY - I do get results - but it is not what I want because it would be hard to keep track of the students that met the requirements.
View 7 Replies
View Related
Jul 10, 2013
Table Name: F_SCENARIO
System : Dataware house
Oracle version : 11g
Record Count : 2 Million records
Correct scenario records
F_Key F_Bridge_key Record_type
1 1 1
2 1 2
3 1 3
Wrong scenario records
F_Key F_Bridge_key Record_type
1 1 1
2 -5 2
3 -6 3
I want to write a Merge statement to update the negative values into 1.
View 6 Replies
View Related
Oct 21, 2010
I have a BEGIN Block of the code where a loop is running .I also have a CONTINUE statement in the EXCEPTION Block of the code where I return the control to the BEGIN Block in case of any exception.Now as per ORACLE Company, CONTINUE has a old issue which is already registered by Oracle Company Bug 7306422.
The issue is where CONTINUE statement does not run properly when your code Optimization level is 2. However it will work properly if we have the Optimization Level as 0 and 1.are facing the same issue here and have a workaround.
Is there any code which I can permanently use instead of CONTINUE but have the same logic incorporated.i.e returning to the next iteration whenever I say to pass the control.
View 10 Replies
View Related
Jul 29, 2013
i am trying to upgrade my existing system (6i) to 11g. I have installed Weblogic 10.3.5 with forms and reports 11.1.2.1.0 on windows 7 32 bit. I can connect to my database (10g) from Form builder.
But when I try to open a form/pll of 10g or 6i, it says PDE-PLI018 could not find library and it shows the path of my earlier folder, which now does not exist.
I have made changes in registry FORMS_PATH, FORMS_BUILDER_PATH, UI_ICON And in default.env file FORMS_PATH and CLASSPATH
I have added entry to tnsnames.ora, through which I can connect to DB. And have followed below site to configure weblogic and FM forms and reports;
[URL]
1) Do we have to connect (create a bridge) to DB from weblogic except tnsnames.ora.
2) For library error I think I am missing some settings to be done.
3) If I am able to rectify above error then can I directly use [URL] and access the system from another pc, or still there are some modifications needed.
View 2 Replies
View Related
Oct 23, 2012
create table t (a varchar2(20),b number(8));
insert into t values ('aa',4);
insert into t values ('ba',6);
insert into t values ('ca',7);
insert into t values ('da',8);
in place of 8 there can be any number between 1 to 100
if in place of 8 number is <10
insert into t values('ea',10); ---- this i need dynamic insert
if in place of the number between 11-19 then
insert into t values('ea',20); ---- this i need dynamic insert
and so on
i tried as below
select case when max(b) <10 then 10 when max(b) between 10 and 20 then 20 end from t;
but i cant write case for again and again upto nth
View 3 Replies
View Related
Sep 5, 2012
I have oracle 10g R2 on windows.I have two tables and say table A ant Table B. Both have column Total_amount. There are primary key's on Both tables.
Sample query
SELECT A.EMPNO,A.EMP_CODE,nvl(A.TOTAL_AMOUNT,0) "A_TOTAL_AMOUNT",
nvl(B.TOTAL_AMOUNT,0) "B_TOTAL_AMOUNT",(nvl(A.TOTAL_AMOUNT,0) - nvl(B.TOTAL_AMOUNT,0)) "DIFFERENCE"
FROM A, B
WHERE A.EMPNO =B.EMPNO (+)
AND A.EMP_CODE = B.EMP_CODE (+)
AND round((nvl(A.TOTAL_AMOUNT,0) - nvl(B.TOTAL_AMOUNT,0)),3) <> 0Above query retrives only Non-matching rows.
I need to retrive :-
1)output of the query shown above and
2)Records which are present in B and not in A.
View 2 Replies
View Related
Feb 19, 2011
SQL> CREATE OR REPLACE TRIGGER TRI_COMPL_FEATURES
2 AFTER INSERT OR UPDATE ON COMPLEMENTS FOR EACH ROW
3 DECLARE
4 v_fno NUMBER;
5 v_tab VARCHAR2(30);
6 v_unique_id VARCHAR2(40);
[code]....
Trigger created.When I am trying to insert into complements table it is throwing error as follows:
SQL> insert into complements values(19,NULL,'5',6,7,NULL,'W2023648',NULL,NULL);
insert into complements values(19,NULL,'5',6,7,NULL,'W2023648',NULL,NULL)
*
ERROR at line 1:
ORA-20010: ORA-04091: table TEEMNGWS.COMPLEMENTS is mutating, trigger/function
may not see it
ORA-06512: at "TEEMNGWS.TRI_COMPL_FEATURES", line 19
ORA-04088: error during execution of trigger 'TEEMNGWS.TRI_COMPL_FEATURES'
I can understand that I am trying to perform DML operation(i.e select) on table which trigger is fired.But how can I implement the same logic when AFTER INSERT OR UPDATE trigger is written.
View 1 Replies
View Related
Feb 3, 2013
I want to retrieve the data which is "n" moths old.
To compare that I want use only SYSDATE without timestamp. I want to use standard timestamp '23:59:59' along with SYSDATE.
The condition should be as below.
UPDATE_DATE <= ADD_MONTHS(15/01/2013 23:59:59,-3)
UPDATE_DATE <= ADD_MOTHS(30/01/2013 23:59:59,-4)
UPDATE_DATE<=ADD_MONTHS(sysdate||' '||'23:59:59',-3);
View 6 Replies
View Related
Jun 25, 2013
we wanted to know the approach of how to implement our custom logic through button click (which is vanilla) in Oracle CRM on Demand
View 1 Replies
View Related
Aug 18, 2013
find the logic to query data based on a condition like..I am having a table dummy
USERNAME RESP_NAME FUNC_NAME MESSAGE
-------- --------- ------------------ ------------
TEST1 SYS_ADMIN CONCURRENT_PROGRAM AAAAAAAAAAAA
TEST1 SYS_ADMIN % BBBBBBBBBBBB
TEST1 % CONCURRENT_PROGRAM CCCCCCCCCCCC
TEST1 % % DDDDDDDDDDDD
% SYS_ADMIN CONCURRENT_PROGRAM EEEEEEEEEEEE
% SYS_ADMIN % FFFFFFFFFFFF
% % CONCURRENT_PROGRAM GGGGGGGGGGGG
% % % HHHHHHHHHHHH
The above 8 are the possible ways to key in the data using a form.The logic is like based on a particular condition the message should display in the report.If they specify a particular USER and RESP_NAME and FUNC_NAME then it should display AAAAAAAAAAA (This should take priority first). The priority is in order the default should be the last one.
View 22 Replies
View Related
Aug 9, 2013
I installed web logic server and form & report on my linux system and gave configuration option later . so now web logic admin server is running on different port and wls_reports server is running to different port in cluster. How its running on cluster and when i run
localhost:9002/reports/rwservlet/getserverinfo?server="report_server_name" i am getting rep-51002 error.
View 3 Replies
View Related
Feb 28, 2012
I'm having a problem with null values. I want to display old student in a display item if subjects text item has records. Otherwise, display new student if it has null values. This is the code that i tried so far..
IF :block2.subjects IS NULL THEN
:block3.type := 'NEW STUDENT';
ELSE
:block3.type := 'OLD STUDENT';
END IF;
But i got logic error during runtime.
View 8 Replies
View Related
Feb 1, 2013
I have a schema DEF and I have a column_name CREATE_DATE.
I wanted to write a procedure which will give me list of tables whose CREATE_DATE data is prior to year 2009.
View 5 Replies
View Related
Apr 9, 2013
I am testing nested blocks, but my logic is apparently not correct.
I am running this script:
DECLARE
v_one number(1) := 1;
v_two number(1) := 2;
[Code]....
I receive the output from only one of the nested blocks:
bad
PL/SQL procedure successfully completed.
SQL>
I understand that I don't need nested blocks for the example above, but this was just a condensed version of what I'm trying to do. I think nesting blocks will be easier to read and maintain, instead of having a huge CASE statement.
How can I execute only the nested block for which the condition is true and ignore the nested blocks that follow?
Are nested blocks not the correct answer here? Should I be looking at invoking procedures/functions instead?
View 8 Replies
View Related
Dec 26, 2011
the following proble.The emp table is having 14 records.
SELECT * FROM emp ORDER BY empno;
EMPNOENAMESALDEPTNO
7369SMITH80020
7499ALLEN160030
7521WARD125030
[code]...
The emp table is having 10 records.
SELECT * FROM emp_10 ORDER BY empno;
EMPNOENAMESALDEPTNO
7369SMITH80020
7499ALLEN160030
7521WARD125030
7566JONES200020
[code]...
I have written the following PL/SQL block logic tofetch the records from the emp table and compare the records with emp_10 table to perform insert if the records are newelse to perform update the existed records in the emp_10 table.
DECLARE
CURSOR tranche_balance_cur
IS
SELECT empno,
ename,
sal,
[code]...
Execution scenario 1:
I have commented insert and update statements in that case I got the following out put.
Inserted Records4
Updated Records10
As per the logic it's giving the correct output because the cursor is fetching 14 records in that already 10 records are existed in emp_10 tableand 4 are new records.so that it's showing the count for inserted records as 10 and updated records as 4.
Execution scenario 2:
I have uncommented insert and update statements in that case I got the following out put.
Inserted Records13
Updated Records1
As per the logic it's not giving the correct output.
I tried with using TRIM function in the comparision logic to avoid spaces.
TRIM(emp_10.empno) = TRIM(tranche_balance_rec.empno)
AND TRIM(emp_10.ename) = TRIM(tranche_balance_rec.ename)
AND TRIM(emp_10.sal) = TRIM(tranche_balance_rec.sal)
AND TRIM(emp_10.deptno) = TRIM(tranche_balance_rec.deptno)
View 10 Replies
View Related
Aug 29, 2013
I have to load data file into a table. And the requirement is as below:
Input Data:
1234|20130815|20130822|This is a test, this is the the part
3456|20130823|20130809|This is a test
3456|20130823|20130809|This is a test
3456|20130823|20130809|This is a test
3456|20130823|20130809|Siva 1234
The data should be inserted only in two rows as below:
When Value in first 3 fields is same, 4th field should be appended to the existing value in table.
1234|20130815|20130822|This is a test, this is the the part
3456|20130823|20130809|This is a testThis is a testThis is a testSiva 1234
View 3 Replies
View Related
Sep 7, 2007
I'm dealing with an ORA-1000 error in a Pro*C application where all the cursors are correctly closed (or so it seems to me).
Here is the code for a simple program which reproduces the problem:
Each cursor is opened in a PL/SQL package:
CREATE OR REPLACE PACKAGE emp_demo_pkg AS
TYPE emp_cur_type IS REF CURSOR;
PROCEDURE open_cur(curs IN OUT emp_cur_type, dept_num IN NUMBER);
END emp_demo_pkg;
[Code]....
While testing the initialization parameter open_cursors is set to 50.
It's my understanding that Oracle doesn't close the cursors until it needs the space for another cursor, which in my test case seems to happen when I enter a value of 50 or bigger for "number of loops". To see how oracle is reusing the cursors, while the test program is running I run SQL*Plus and query v$sesstat for the session that's running the test with the following sentence:
select name, value
from v$sesstat s, v$statname n
where s.statistic# = n.statistic#
and sid = 7
and name like '%cursor%';
Even before I enter a value for number of loops I can see that the session opened 4 cursors and closed 2 of them:
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 4
opened cursors current 2
Entering a value of 5 for number of loops yields
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 11 <----- 7+
opened cursors current 8 <----- 6+
With a value of 30
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 36 <----- 25+ (apparently, Oracle reused at least 5 cursors)
opened cursors current 33 <----- 25+
With a value of 47
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 53 <----- 17+
opened cursors current 50 <----- 17+
Now I reached the upper limit set by the initialization parameter open_cursors.
Entering a value of 48, I get the ORA-1000 error.
ORA-01000: maximum open cursors exceeded
ORA-06512: at "SCOTT.EMP_DEMO
Since I open and close the cursor in the same loop iteration, I expect to find in every iterarion 1 explicit cursor and a number of implicit cursors (the PL/SQL call along with the so-called recursive cursors), but I don't expect the sum of all of them to be greater than 50. If my understanding is correct Oracle should be reusing the 50 cursors previously marked as "closeable", not raising the ORA-1000 error.
View 1 Replies
View Related
Jun 23, 2011
I read that rownum is applied after the selection is made and before "order by". So, in order to get the sum of salaries for all employees in all departments with a row number starting from 1, i wrote :
select ROWNUM,department_id,sum(salary) from employees group by department_id
If i remove rownum, it gives the correct output. Why can't rownum be used here ?
View 16 Replies
View Related
