select col1, col2, col3, col4
from tab1, tab2, tab3
where conditions
union
[Code]...
Now, the col4 is a date column and I have to order by the entire result sets on it. I know I can do it by (order by col4) or by (order by 4) at the end of the entire query.
But the problem is that, the output is coming in dd-Mon-yyyy (i.e 31-Nov-2012).
I want every output in dd/mm/yyyy format so I need to use to_char function.
But in that case, I cant use the order by clause, because in that case it is getting arranged by character i.e by 1,2,3,4,5 like this.
I came across situation where a Nullable column is not using index for 'order by' clause. I added Not Null condition in the 'where' condition but it wasn't useful. I don't wanted to make composite index with not nullable column or with constant or modify column to 'Not Null'
So I carried out test cases and during which I found that in one case the sql statement does 'fast full scan' for data access but does not use index for 'order by' sorting
here are the steps
Initially I kept the column Nullable
SQL> create sequence s5; Sequence created.
SQL> create table t5 as select s5.nextval id,a.* from dba_objects a where rownum<1001; Table created.
SQL> set pages 100 SQL> select column_name,nullable from user_tab_columns where table_name='T5';
Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 5
Rows Row Source Operation ------- --------------------------------------------------- 1000 SORT ORDER BY (cr=16 pr=0 pw=0 time=4771 us) 1000 TABLE ACCESS FULL T5 (cr=16 pr=0 pw=0 time=1157 us)
Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 68 0.00 0.00 SQL*Net message from client 68 49.49 49.72 ********************************************************************************
select /*+ index(t i5) */ * from t5 t where id is not null order by id
Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 5
Rows Row Source Operation ------- --------------------------------------------------- 1000 TABLE ACCESS BY INDEX ROWID T5 (cr=150 pr=0 pw=0 time=5167 us) 1000 INDEX FULL SCAN I5 (cr=71 pr=0 pw=0 time=3141 us)(object id 4673065)
Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 69 0.00 0.00 SQL*Net message from client 69 22.89 28.04
Now I modified the 'id' column to Not Null
SQL> alter table t5 modify id not null;
SQL> set pages 100 SQL> select column_name,nullable from user_tab_columns where table_name='T5';
COLUMN_NAME N ------------------------------ - ID N OWNER Y OBJECT_NAME Y SUBOBJECT_NAME Y OBJECT_ID Y DATA_OBJECT_ID Y OBJECT_TYPE Y CREATED Y LAST_DDL_TIME Y TIMESTAMP Y STATUS Y TEMPORARY Y GENERATED Y SECONDARY Y
Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 5
Rows Row Source Operation ------- --------------------------------------------------- 1000 SORT ORDER BY (cr=16 pr=0 pw=0 time=2398 us) 1000 TABLE ACCESS FULL T5 (cr=16 pr=0 pw=0 time=1152 us)
Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 68 0.00 0.00 SQL*Net message from client 68 37.74 37.91 ********************************************************************************
Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 5
Rows Row Source Operation ------- --------------------------------------------------- 1000 TABLE ACCESS BY INDEX ROWID T5 (cr=150 pr=0 pw=0 time=4166 us) 1000 INDEX FULL SCAN I5 (cr=71 pr=0 pw=0 time=3142 us)(object id 4673065)
Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 68 0.00 0.00 SQL*Net message from client 68 8.28 8.45
Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 5
Rows Row Source Operation ------- --------------------------------------------------- 1000 SORT ORDER BY (cr=6 pr=0 pw=0 time=1342 us) 1000 INDEX FAST FULL SCAN I5 (cr=6 pr=0 pw=0 time=1093 us)(object id 4673065)
Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 68 0.00 0.00 SQL*Net message from client 68 1.88 1.89
Questions are
1) Why adding 'where id is not null wasn't enough for the index to get used in 'order by'? 2) While we got 'fast full scan' why index wasn't used for 'order by' clause? 3) Do we need the indexed column in where clause for being used in 'order by clause' too? 4) Do we need 'order by' clause if we are selecting only the indexed column with sequence generated values?
I am trying group by count on Monthly basis, entered_timestamp Column is of DATE datatype.
select count(*),to_char(entered_timestamp,'MM-YYYY') MONTHLY from payments where type = 'BOOK' group by to_char(entered_timestamp,'MM-YYYY') order by MONTHLY DESC
Above SQL doesn't gives the output in proper order ,
select to_char(exp_Date,'Mon-YYYY') dt, count(*) from exp_main where exp_type like 'Income%Photo%' group by to_char(exp_Date,'Mon-YYYY') order by exp_date
When I run this I get :not a GROUP BY expression
If I remove the order by, it works fine.
Is there a way to order by since the output of the query is character-sorted.
I did search online most have suggested to use order by column_name. But does not work for me.
I have a view that contain multiple tables with ( UNION all ) clause , is there any way that if i query from this view I can explicitly specify the table that i need the data from ?
Let say i have view that contain salaries of 2011
Create view sals_2011 as select * from sals_jan2011 union all sals_feb2011 ..... union all select * from sals_dec2011.
if i issued select * from sals_2011 where emp_id >500 and date < 01-feb-2011 the explain plan show me that full tables and indexes are in processing, while i know that i need only to scan sal_jan2011, and of course it is taking much longer time than selecting from the original table only.
I have requirement to create an XML structure through stored procedure. I need to Order some of the columns in ascending order before I format them into the xml structure. I am pretty novice to creating an output into XML format, but attached is the query I came up with (without order by). This works perfect, but now the requirement is to order by - cls_cd, and within cls_cd, again order by - cat_cd. I am not able to do this.
I have a requirement where I need to display a list of employees, performance rating and manager id. I want to display the employees first whose manager is as manager_id as input parameter. So lets say a manager logs in, he should see his reporters first and then the rest. How can I implement this in SQL? I am trying first clause with order by but not sure how to do that here.
I have got the following error while executing below Query.
ORA-01791 'Not a SELECTed expression'
select distinct sgbstdn_levl_code from sgbstdn,spriden where spriden_pidm = sgbstdn_pidm and spriden_id = '200076543' order by sgbstdn_term_code_eff desc;
The above Query is not working with Distinct & Order By clause are present and by joining two tables. I need the distinct values of levels in Descending order of Terms.
When i try to execute a query, which is organised as the below example, it retrieves data..
select * from ( select col1, col2, col3, col4....coln from TABLE_ONE left outer join TABLE_TWO -- some conditions and group by clause order by 1 asc ) where rownum <=1000;
Again if I use Column alias in the ORDER BY clause col1, the query won't retrieve data.
Also If I use ORDER BY 4 instead of ORDER BY 1, the query wont return data...
select * from ( select col1, col2, col3, col4....coln from TABLE_ONE left outer join TABLE_TWO -- some conditions and group by clause order by 4 asc ) where rownum <=1000;
The whole issue revolves around the inner ORDER BY Clause and external ROWNUM condition..If I eliminate any of the two, the query works fine...I am not sure if indexes have some role to play in it...
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 have a real problem with form, specifically one of its data blocks. In the order by property of the block i specify it to sort on a branch in ascending order(i tried descending as well) but for some reason the form ignores that and sorts it on the ROWNUM. I even removed the where clause, the order by clause and changed the query data source type to FROM clause and changed the data source name to pre-query. I then created the query string in the block's pre-query trigger and set query_data_source_name property to that query string and still the data in the block is not being sorted on the branch number but instead on the ROWNUM.
How to avoid sort operation by an order by clause without changing the sort area size.what hints or changes should be done in query so that order by clause work faster.
I have table called INFO and the column called CREATED_DATE . Now the datatype of CREATED_DATE is of varchar2 . Now If I need to query the table through select statement where I need to order the result based on CREATED_DATE , how can i achieve this ?
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production PL/SQL Release 11.1.0.6.0 - Production "CORE 11.1.0.6.0 Production"
I have Event_month, Event_date in SELECT statement. Results of the query has NULL values as well.. I am doing ORDER BY Event_month, and I want NULL values to be at the top..
SELECT d.event_code_dt, count(d.event_number) count1 FROM main_event a,sec_event d WHERE a.event_id = d.event_id(+) GROUP BY d.event_code_dt ORDER BY d.event_code_dt, count(d.event_number) desc
Results are like this
11-MAY-10 21-MAY-10 22-MAY-10 NULL
And I want results to be like this: NULL 11-MAY-10 21-MAY-10 22-MAY-10
Same thing happens with Event_month NULL Feb 2009 Aug 2009
i want to add the JCalendar.jar and JCalendarJinit.jar in order to use the date picker on my forms while using a windows environment,what attributes should i change or edit in the forms/server/formsweb.cfg file and what else am i required to add?
I have a question regarding a Date field on one of my form.
How do I make a Date field filter in a descending order when the form is opened? Also, can I add a 'when-mouse-doubleclick' trigger to sort the date field in ascending and descending order (this is a client requirement)?
Could it be that it's impossible to change the date format in the default_where clause?
The table column PROPOSAL_END in the database that I want to compare with, is in Format DD.MM.YYYY.
I tried:
set_block_property('Tours' , default_where, 'Number_of_places > 0 AND PROPOSAL_END <= ' || to_char(to_date(sydate,'DD.MM.YYYY'))); set_block_property('Tours' , default_where, 'Number_of_places > 0 AND PROPOSAL_END <= ' || to_char([-- A date item with the intial value $$date$$ the output is in Fomat DD.MM.YYYY by default --])); set_block_property('Tours' , default_where, 'Number_of_places > 0 AND PROPOSAL_END <= ' || to_char(to_date([-- A date item with the intial value $$date$$ the output is in Fomat DD.MM.YYYY by default --],'DD.MM.YYYY')));
It all does dot matter. Every time the generated select-statement shows the format DD-MMM-YY. How can I change that?
I have a varchar2 column which can hold numeric values, and also varchar2 values.
The following select returns only the records which have a number value in that column
select * from my_table order by decode( replace(translate(valoare,'1234567890.','00000000000'),'0',''), NULL, valoare, 0 ) != 0;
But how can i order that column, so that first are the numbers (also ordered), and then the varchar2 columns (so all the rows of that table are selected, both varchar2 values and numbers in that column)? Is it possible?
Is it possible within a CASE statement to put conditions on the date range that I want to pull? IE: am versus pm. The query has to pull specific time ranges for an AM run versus a PM run. ..... FROM table WHERE CASE WHEN TO_CHAR(SYSDATE,'AM') = 'AM' THEN table.date BETWEEN TRUNC(SYSDATE) AND SYSDATE ELSE table.date BETWEEN TRUNC(SYSDATE+12/24) AND SYSDATE
