Following are 2 queries, which return same results as far as the input parameter is NOT NULL
select /*+ gather_plan_statistics */ * from PX_CJQ where decode(:x,null,1,object_id) = NVL(:x,object_id); select /*+ gather_plan_statistics */ * from PX_CJQ where object_id = NVL(:x,object_id);
However the execution plan differs a lot The FTS cost or rows accessed count also varies what could be the reason? The PX_CJQ is simply select * from dba_objects and has index on object_id which anyway is not being used in this case
variable x number exec :x:= 28 Query - 1 *************** *************** SQL> >select /*+ gather_plan_statistics */ * from PX_CJQ where decode(:x,null,1,object_id) = NVL(:x,object_id); SQL> >select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
[code]...
Query - 2 *************** *************** SQL> >select /*+ gather_plan_statistics */ * from PX_CJQ where object_id = NVL(:x,object_id); SQL> >select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT
I am trying to display the results from 2 queries, one is supposed to display the count of the employees, per department, who win over the average of the entire company and the other one is supposed to display the count of the employees, per department, who win under the average of the entire company.
I used a UNION ALL, but all it does is merge the results from the ones that win over and under the average into one row, is there a way to separate them? I tried assigning names to each salary using AS but it only displays the first I put in.
sql Original - sql Code ( SELECT DE.DEPARTMENT_NAME, COUNT (EM.EMPLOYEE_ID) AS MAYORES FROM DEPARTMENTS DE, EMPLOYEES EM WHERE DE.DEPARTMENT_ID = EM.DEPARTMENT_ID AND EM.SALARY > (SELECT AVG(EM.SALARY) FROM EMPLOYEES EM) GROUP BY DE.DEPARTMENT_NAME [code].....
When we are running a query it is giving us the result based on the conditions .But to know what exactly is happening in the database when we are running a query against it and how finally it returns the result.
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 have two update queries in the same Proc. One Seems to run just fine, the other I am getting this error:
ORA-01427: single-row subquery returns more than one row
The working Updates' structure is the same as the erroneous one. This works:
UPDATE P0525_STOREROOM_HOLDER H SET H.STRATIFICATION_ID = (SELECT X.STRATIFICATION_ID FROM EMISTRATIFICATION_XS X WHERE H.TOA = X.TOA AND H.STOREROOM = X.STOREROOM AND H.NSN = X.NSN AND X.ASSEMBLY = 'NO REQUIREMENT' );
This one gives me a single-row error:
UPDATE P0525_STOREROOM_HOLDER H SET H.STRATIFICATION_ID = (SELECT X.STRATIFICATION_ID FROM EMISTRATIFICATION_XS X WHERE H.TOA = X.TOA AND H.STOREROOM = X.STOREROOM AND H.NSN = X.NSN AND X.ASSEMBLY = 'ABOVE ALLOWANCE' AND H.NSN_QUANTITY > 0);
I have run a check on the data and there doesn't appear to be any duplicate values in the second update... Both Updates are supposed to be updating record sets not a single row (i.e. the stratification_id where the criteria matches...
My current system have 1 submit button with single form. This submit button will call *file_content.upload*.
htp.p('function on_submit() {'); htp.p('...the rest of my code here..'); htp.p('document.forms[0].submit();'); htp.p('return true;'); htp.p('}'); htp.p('<form enctype="multipart/form-data" method="post" action="file_content.upload">'); htp.p('...the rest of my code here..'); htp.p('<input type="button" id="btn_Submit" value="Submit" onclick="on_submit()"></form>');
My question is, i want to enhance this by adding additional 1 submit button with value "Save". But this new "Save" button will call/trigger different file. This new button will call *file_content.update*. How to accomplish this and how to differentiate these 2 button?
I have stacked into something: if there is a way to define a single row form page based on table in which I could query and update without using a tabular page (to call the single row form). I want only to use only one page. Does APEX has this functionality?
our system has always been running on mysql database and recently we have switched to oracle. As the current system is coded using mysql query syntax, when i run this program using oracle database, i got a error. The language that I'm using is JSP.
this is the error message:
The following query could not run on oracle. To convert these mysql queries to oracle compatible queries.
SELECT productID,productName FROM products order by productName;
select newsID,newsDate,newsHeadLine1 from news order by newsDate Desc limit 3
SELECT fuji_products.productID, productName_Display FROM products,products_availability where products_availability.productID=products.productID and (product_status='enabled' or product_status='all') AND category='12'
SELECT catID, catSub1 from category where catSub = '"+ prodCat +"' AND catSub1 is not null group by catSub1 order by catSub1
After setting up a data entry page,The logic in my page requires that all the records in my tabular form should be displayed .The tabular form is based on a collection, and the user selects rows through a checkbox.Any reload of the page due to pagination will break the logic, as some calculation and display events occur during page load, based on the previous page.
I need to display about 25 rows.I have changed the report attribute to display 50 rows, but it displays rows only up to the bottom of the screen, i.e. 11 rows.
Unfortunately I only just added some new rows and saw this behaviour else I would not have gone that way, as I expected APEX to display all rows if required.
There is an interface table and there is an normal transcational table..interface table is being compared with normal table and if match found the result is dumped into another normal table.
I am using two cursors one is to query the interface table and in a for loop pass the results to the second cursor..The interface table is having 5000 + rows and the transcation table is having more than 3.7 millions ..and the program is taking lots of time to execute..took almost 35-45 minutes..
I wonder how I can limit the queries in a table? This limitation would be that from a certain time, it allows queries. Before the opening hours, permitting no select.
Example: Allow SELECTs in table only after 16 p.m.
If multiple queries are run in parallel(at the same time) against a table or a set of tables (query referencing multiple tables), does it reduce the performance.
In other words is Oracle capable of reading (selecting from) the same table multiple times in parllel.
SELECT * FROM EMP A WHERE 1=(SELECT COUNT(DISTINCT(SAL)) FROM EMP B WHERE B.SAL>A.SAL); SELECT * FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM (SELECT DISTINCT(SAL) FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=2);
both queries will fetch second highest sal from emp table.which sorting is used by oracle in order by and group by clause.
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;
I was trying to update the AGENTS table with the data based on queries. But all the records updates with the same data. I can't understand why.
Write a PL script to populate these columns as follows:
TRAVEL_STATUS is one of three values: GLOBETROTTER: agent has visited five or more different locations in the course of his missions ROVER: agent has visited between one and four locations SLOB: agent has been on no missions.
CONTACTS is the number of targets that share the agent's home location.
some of the tables and columns are agents table: agent_id, location_id targets table: target_id, location_id missions_agents table: agent_id, mission_id missions_targets: target_id, mission_id missions table: mission_id, location_id
My code is
DECLARE status AGENTS.TRAVEL_STATUS%TYPE; contact AGENTS.CONTACTS%TYPE; CURSOR loc_cur IS SELECT a.AGENT_ID id,
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.
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
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
me in building a query. I want to show the result of the query just like pivot table. Test case CREATE TABLE CPF_YEAR_PAYCODE ( CPF_NO NUMBER(5), INC_DATE DATE, PAYCODE_TYPE CHAR(1 BYTE),
[code]...
I want that my query should look like the format as attached in the xls sheet.