I am trying to run an SQL query which refers/joins around 10 tables.In my case I would want to add an if-else or case condition in the WHERE clause of select query
For example
select a.column1, b.column1, c.column1, d.column1 from a, b, c, d WHERE a.column2 = b.column2 AND (if a.column3 = 10 then ( I want 2 conditions to be AND b.column2 = c.column2) added else after WHERE clause as AND...) AND b.column2 = d.column2 ) AND d.column2 = a.column2
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.
The below sql is giving different number of result sets while adding further columns in select clause.i.e After adding the columns 4,5,6 in the below query its giving different number of result set.In this case the result set count would be 5.
Before adding the columns 4,5,6,the result set count was 11.
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...
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.
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 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?
SELECT CASE WHEN currentstep.step_id IN (100) THEN currentstep.start_date ELSE (SELECT start_date FROM audt.os_historystep WHERE ID =
[code].....
here is the completed query
select EAG.AUDIT_NUMBER Audit_Nbr, ( SELECT CASE WHEN currentstep.step_id IN (100) THEN currentstep.start_date ELSE (SELECT start_date FROM audt.os_historystep
[code].....
when I try select from this query I get ORA-00904: "DATE1": invalid identifier.
I have learnt that indexes slow down the DML operations. My question is specific to an update statement. Is it going to be slower if Im trying to update an indexed column on my table or it is slower overall (even when a non-indexed column is getting updated in the table) How does it behave in case of inserts & delete operation.
I have a JSP that works with an Oracle 9i database.
On my local windows workstation where I developed the JSP the application processes very quickly working with the Oracle database. The JSP application on the Production Intranet web server connecting to the same Schema processes very slow sometimes during the day. During off hours the Production Intranet JSP with Oracle processes quicker.
There is only one user for the application and I dont understand why the same application using the same database runs so much slower sometimes on the production server compared to my local workstation.
After upgrading 11gR1 database (11.1.0.7.0) to 11gR2 (11.2.0.3.0), the datapump exports have been taking quite a bit longer. When database was 11gR1, a full expdp took approx. 40-45 minutes. After upgrade, it takes approx. 1 hour 40-50 minutes. These times were with parallel=4. I tried with parallel=8 and parallel=12, both of these took around 1 hour 5-10 minutes, better but still quite a bit slower than pre-11gR2 upgrade. I tried with exclude=statistics, index_statistics, indexes; it still took approx. 1 hour 40-45 minutes. This is a PeopleSoft database so there are many, many objects to be exported. The database was upgraded using dbua.
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.
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.
The query has a case statement in the where clause so that results can be filtered. If I pass "ut" for sso_id then the query returns 21 rows. If I remove the case statement and hard code "a.sso_id like lower('ut'||'%')" then the query returns 41 rows. The query should be returning 41 rows all the time.
Problem:
When passing "ut" as an SSOID parameter to the Procedure the query returns 21 rows.Taking the query and hard coding "a.sso_id like lower('ut'||'%')" the query returns 41 rows.
Result: query should be returning 41 rows when "ut" is passed an an SSOID parameter.
Returns 21 rows
procedure SSO (SSOID in varchar2 default null, Name in varchar2 default null, Campus in varchar2 default null, Department in varchar2 default null,
[code]...
Returns 41 rows
open Results for select a.sso_id, (a.name_last||', '||a.name_first) as name, b.site,
[code]...
Test Data CREATE TABLE ID ( SSO_ID VARCHAR2(60 BYTE), NAME_FIRST VARCHAR2(100 BYTE), NAME_LAST VARCHAR2(100 BYTE),
[code]...
Test Data CREATE TABLE NT ( LOWER_NT_ID VARCHAR2(60 BYTE), DEPARTMENT VARCHAR2(100 BYTE),
Below is the block which i am trying to test in scott schema. I dont want to substute IN clause values directly. So i have written cursor and have added in separate variable separeated by comma.But its not working.
declares varchar2(1000);s1 varchar2(1000);v number := 0;v1 varchar2(2000) := 'SCOTT';j number := 0;cursor hhis select ename from emp;beginselect count(*) into v from emp; for i in hh loops := s||''''||i.ename||''''; j := j+1;if j <> vthen s := s||',';end if;s1 := s1||s;s := null; end loop;dbms_output.put_line(S1); case when v1 in (s1) then dbms_output.put_line('Y'); else dbms_output.put_line('N'); end case;end;
I have the where cluase as below , I would like to know how does oracle decides which one to execute first,
WHERE S.PERSPECTIVE='S'and s.shipment_gid=sb.shipment_gid AND SB.BILL_GID=CBIL.INVOICE_GIDand inv.invoice_gid=cbil.invoice_gid AND S.SOURCE_LOCATION_GID=LC.LOCATION_GID and l.location_gid=lc.location_gid AND TRUNC(cbil.insert_date)=TRUNC(tc.tesco_cal_date)AND(lc.location_gid='N' OR lc.corporation_gid='TESCO.10719')AND s.source_location_gid=lc.location_gid AND tc.tesco_year='2013' AND tc.tesco_period=6AND tc.tesco_week_number=23
