Default In (order By) Select Query Different In 11g From 10g?
Nov 12, 2013
if when you are querying a table in 11g and you use the order by clause and there is more than one occurrences with the same values in the order by, if the 11g default is different than from 10g.
For instance.
DECLARE MHBulk CURSOR FOR select invoice_nbr, customer_nbr, post_century, post_yymmdd, from CUSTOMERS where customer_nbr = 1234 order by post_century, post_yymmdd;
If you have more than one occurrence of the same customer_nbr, post_century, and post_yymmdd
it looks like the default order for how 11g retrieves the records is a bit different than the 10g default.
I M USING APEX 4.1 AND CREATED SELECT LIST ON PAGE, I WANT TO SHOW MIN VALUE OF THE SELECT LIST FOR THAT I WROTE IN THAT SELECT LIST PROPERTIES UNDER DEFAULT TAG MIN; AND CHOOSE PL/SQL EXPRESSION BUT ITS GIVING ERROR "Error computing item default value for page item P1_PRODUCT."
BUT IF I HARDCORE THE VALUE CONTAINING IN MY DATA LIKE PRODUCT ID = 1, I HARDCODED IN DEFAULT VALUE 1 AND SELECT PL/SQL EXPRESSION IT WORKS.
BUT ITS NOT DONE LIKE THIS I WANT TO SELECT BY DEFAULT MIN VALUE OF THE SELECT LIST, SO THAT THE DATA SHOULD BE DISPLAYED ACCORDING TO THAT.
THE EXACT REQUIREMENT IS TO ENTER THE SELECT LIST DEFAULT VALUE IN SESSION SO THAT DATA IS TO BE DISPLAYED.
Select clause or Order by clause? First the columns specified in the select clause are fetched and then ordered or is it the vice -versa? In a query if a psuedo-column rownum is selected, then if we use order by on the rownum column, what happens?
What would be the output of the following query and why?Which part of the query is first executed, is it select clause or order by clause?
select empno,ename,rownum from emp order by rownum desc;
CREATE TABLE test_A(member_id NUMBER(2) PRIMARY KEY, MEMBER_name VARCHAR2(20), MEMBER_parent NUMBER(2) DEFAULT NULL); INSERT ALL INTO test_A VALUES (1, 'mem1', NULL) INTO test_A VALUES (2, 'mem2', NULL) INTO test_A VALUES (3, 'mem3', NULL) INTO test_A VALUES (4, 'mem4', 1) INTO test_A VALUES (5, 'mem5', 1)
[code]....
As the actual data is huge, I need to know the best (least expensive) way to select each parent and then all its direct child ordered by member_id the output should look like:
Basically I have a report that as part of it's source sql takes in an element from a select list.
E.G. SELECT * FROM table_name WHERE table_column = : P1_SELECTLIST
This works fine when the user selects an option from the select list however the first time the page runs it always returns data not found. The options correctly appear in the select list as they should without any nulls although it seems the select list is still initializing as null. I have been told to try and add a computation before the header on the select list item, although I'm not sure if this would work/how to do this.?
currently i m going through some dumps for my OCA-11g prep.I came across one sentence :A view cannot have an ORDER BY clause in the SELECT statement.well this statement is false and the explanation given was :
Query operations containing ORDER BY clause are also permitted, so long as the ORDER BY clause appears outside the parentheses.
The following is an example of what I mean: CREATE VIEW my_view AS (SELECT*FROM emp) ORDER BYempno.
but when i tried running the query like this :CREATE VIEW my_view AS SELECT*FROM emp ORDER BYempno ,it worked w/o giving parentheses.
I want added one new column in the below report which will shows only the items purchased on Cash basis but when i am running the query the column not showing any data.
1.-) i got a page that contains 2 regions, lets say :master (HTML text) and a detail (tabular form updateable report) in my tabular form i got a hidden item that should take the value from the master form. how do i do this ?
2.-) i am calling a form from another form, sending a couple of fields as a link parameters. the second form is called as expected, but i need that records on the second form that match the parameters get displayed. how do i achieve that ?
Does ascending index ensures that query without order by will have the result set sorted?
E.g. the query is
select * from table_t where odm_type='I' and odm_uid>nvl(OpUidParm,-1); column odm_type has index created like this (default is ASC): create index ODM_UID_I on table_t (ODM_UID);
Will such a query always return the first record having the minimal odm_uid in all Oracle versions?
Is there a technique to getting a Top-N query to work as a sub-select in a larger query -or- is there another way to generate Top-N like results that works as a sub-select?
Background:
We have a large query that is being used to build an export from a legacy HR system to a new one. Amount the data needed in the export is the employees primary phone number.
The legacy HR system allows multiple phone numbers to be stored in a simple table structure:
SELECT emp_id, phone_type, phone_number FROM employee_phones
The new HR system does allow for multiple phone numbers, however they need a primary phone number identified and stored with the employee master information. (Subsequent phone numbers get stored in alternate table.)
From a business perspective, we have decided that if they have a HOME phone in the legacy system that should be the primary in the new system, if no HOME phone, then WORK, if no WORK then CELL.
That can be represented as:
SELECT * FROM employee_people_phones WHERE emp_id = '46021' ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')
SELECT * FROM (SELECT * FROM employee_people_phones WHERE emp_id = '46021' ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results WHERE ROWNUM = 1
SELECT phone_number FROM (SELECT phone_number FROM employee_people_phones WHERE emp_id = '46021' ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results WHERE ROWNUM = 1
phone_number ------------------- 1111111111
However, when the Top-N query is added as a sub-select in a larger query using the employee id from the larger query (WHERE emp_id = export.emp_id), it fails saying that �export.emp_id� is not a valid id.
(SELECT phone_number FROM (SELECT phone_number FROM employee_people_phones WHERE emp_id = export.emp_id ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results WHERE ROWNUM = 1)
1.Any way around this? Is it possible to put a Top-N (with a WHERE clause using data from the main query) in a sub-select?
2.Any alternatives (other than Top-N) to delivering a ROWNUM=1 result with a �custom� ORDER BY statement?
Other Notes: Yes, we know we could do two queries in the data conversion first deliver the bulk data to the target table, and then update with the phone numbers. However, for multiple reasons, that is less than desirable.
Name Null Type --------------------------- -------- ------------- RPTNO NOT NULL NUMBER RPTDATE NOT NULL DATE RPTD_BY NOT NULL VARCHAR2(25) PRODUCT_ID NOT NULL NUMBER
describe rptbody
Name Null Type ------------- -------- ------------- RPTNO NOT NULL NUMBER LINENO NOT NULL NUMBER COMMENTS VARCHAR2(240) UPD_DATE DATE
The fact is that we store some header in RPTHEAD and store real data in RPTBODY, the question is that if I use below SQL to query all data for a 'PRODUCT_ID'.
SELECT t0.LINENO, t0.COMMENTS, t0.RPTNO, t0.UPD_DATE FROM RPTBODY t0 , RPTHEAD rpthead WHERE ( t0.RPTNO = rpthead.RPTNO AND t0.UPD_DATE>=to_date('1970/01/01 00:00:00','YYYY/MM/DD hh24:mi:ss') AND rpthead.PRODUCT_ID IN ('4647') )
I do not want to have 'ORDER by' clause since data set is too large, the sorting takes long time, is there any way to get the result rows in the order sorted by RPTNO? We have the index for RPTNO on RPTBODY.
When I run a query form the the Query Window in Visuial Studios 2012 all the date fields truncated to 'mm/dd/yyyy', but i need the full date returned. I am able to get full date from TO_char(MyDateField, 'yyyy-mm-dd hh24:mi:ss'), but if I do TO_DATE(MyDateField, 'yyyy-mm-dd hh24:mi:ss') it only returns 'mm/dd/yyyy'. I'm sure this is a simple setting in Visual studios but I cant find it to save my life. Is there there a way to have the full date returned by default?
We're using Oracle 10g for development purposes. I have 2 same schema with approximately same data. I'm running same query on 2 schemas and I see that the first schema runs the query around 20sec and the 2.schema less than 1 sec. I thought first that there may be missing constraints or indexes but all are the same.
I checked the plan for the 2 schemas and I see that the plan is different.
Here is the query: SELECT ccc.ComponentId AS "ComponentId", ccp.Code AS "ParentCode", ccc.Code AS "ChildCode" FROM CatalogueComponent ccp INNER JOIN CatalogueComponent ccc ON ccp.ComponentId = ccc.ParentComponentId WHERE ccc.ComponentId IN (20934777, 1594747)
I'm sending also the 2 output of PLAN results from 2 different schemas.
what should I do to fix the problem with the NAFBCA schema.
SELECT DISTINCT PATIENT_ID , ( SELECT ROWNUM,APPOINTMENT_ID FROM AppointmentDetailsHistory WHERE APPOINTMENT_STATUS_ID = 2 AND VISIT_TYPE_ID NOT IN (7) AND PATIENT_ID = ADH.PATIENT_ID
[code]...
MY SELECT query failing actually i am trying to convert sql server select into oracle.My sql server query is like this.
SELECT DISTINCT PATIENT_ID , ( SELECT TOP 1 APPOINTMENT_ID FROM EMRAppointmentDetailsHistory WHERE APPOINTMENT_STATUS_ID = 2 AND VISIT_TYPE_ID NOT IN (7) AND PATIENT_ID = ADH.PATIENT_ID ORDER BY PATIENT_ID, LAST_UPDATED_DATE ASC) AS FIRST_APPOINTMENT FROM EMRAppointmentDetailsHistory ADH WHERE ADH.APPOINTMENT_STATUS_ID = 2 AND ADH.VISIT_TYPE_ID NOT IN (7)
We're using Oracle 10g for development purposes.I have 2 same schemas with approximately same data.I'm running same query on 2 schemas and I see that the first schema runs the query around 20sec and the 2.schema less than 1 sec. I thoughtfirst that there may be missing constraints or indexes but all are the same.I checked the plan for the 2 schemas and I see that the plan is different.
Here is the query: SELECT ccc.ComponentId AS "ComponentId", ccp.Code AS "ParentCode", ccc.Code AS "ChildCode" FROM CatalogueComponent ccp INNER JOIN CatalogueComponent ccc ON ccp.ComponentId = ccc.ParentComponentId WHERE ccc.ComponentId IN (20934777, 1594747)
I'm sending also the 2 output of PLAN results from 2 different schemas.
