Can Rewrite Following Query Without Using Row_number() Function
Jan 13, 2011
can I rewrite the following query without using the 'ROW_NUMBER() OVER ' part.The query is supposed to pull out the records whose CODE is not NULL and has most recent date for UPDATE_DATE . The reason I wanted to do this is, When I embed this query in between many other queries along with JOINs, My oracle server is unable to execute. So, I thought its better to supplant 'ROW_NUMBER() OVER ' logic with something else and try it. .
CURRENT QUERY:
SELECT a.* FROM
(SELECT b.*, ROW_NUMBER() OVER (PARTITION BY b.PIDM
ORDER BY b.UPDATE_DATE DESC) AS Rno
FROM
(
SELECT *
FROM SHYNCRO WHERE CODE IS NOT NULL
)b
)a
WHERE a.Rno = 1
SQL Error: ORA-30353: expression not supported for query rewrite 30353. 00000 - "expression not supported for query rewrite" *Cause: The select clause referenced UID, USER, ROWNUM, SYSDATE, CURRENT_TIMESTAMP, MAXVALUE, a sequence number, a bind variable, correlation variable, a set result,a trigger return variable, a parallel table queue column, collection iterator, etc.
I want to create a materialized view for the last 10 days with the enable query rewrite option.
e.g. i want to create a view with the list of employees who joined the company in the last 10 days.
create materialized view M_Employee refresh fast on commit enable query rewrite as select joining_date , name from employee where joining_datde < TRUNC(sysdate) - 10
I seem to get the error SQL Error: ORA-30353: expression not supported for query rewrite
30353. 00000 - "expression not supported for query rewrite" *Cause: The select clause referenced UID, USER, ROWNUM, SYSDATE.
This error is self explanatory , but is there any work around to have a query liek this to list the employees based on sysdate.
I don't want to generate row_number for unique values 'C' and 'E' in below query.
SELECT NAME, ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY NAME) FROM (SELECT 'A' NAME FROM DUAL UNION ALL SELECT 'A' FROM DUAL UNION ALL SELECT 'A' FROM DUAL
[code].....
Means row_number should be NULL for unique values.
To rewrite this sql which is in ANSI 92 standard to ANSI 89 standard.
SELECT "PROJECT"."X_SAMPLED_DATE", SAMPLE"."SAMPLE_NUMBER" FROM "SHIRE_PRD"."LimsUser"."SAMPLE" "SAMPLE" INNER JOIN "SHIRE_PRD"."LimsUser"."PROJECT" "PROJECT" ON"SAMPLE"."PROJECT"="PROJECT"."NAME" WHERE ("SAMPLE"."SAMPLE_TYPE"='EM' OR "SAMPLE"."SAMPLE_TYPE"='WATER') AND "SAMPLE"."STATUS"<>'X' AND("PROJECT"."X_SAMPLED_DATE">={ts '2011-05-01 00:00:00'} AND "PROJECT"."X_SAMPLED_DATE"<{ts '2011-06-01 00:00:00'}) ORDER BY "SAMPLE"."PRODUCT"
I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.
How do I write this MSSQL statement so it works in Oracle?
update b1 set b1.b1_app_status = r3.application_status from conv_app_status_update a, statyp r3, b1perm b1 where a.spc = r3.serv_code and a.task_des = r3.r3_act_type_des and a.task_status =r3.r3_act_stat_des and a.process_code = r3.r3_process_code and r3.application_status is not null and a.spc = b1.serv_code and a.id1 = b1.id1 and a.id2 = b1.id2 and a.id3 = b1.id3
desc tbl_female Name Null Type ------------------- ---------- ----------------- ANI NOT NULL VARCHAR2(12) CHAT_ID VARCHAR2(25) AGE VARCHAR2(5) RECORD_FILE VARCHAR2(25) SUB_DATE_TIME DATE STATUS VARCHAR2(15) LOGIN_STATUS VARCHAR2(15) CIRCLE_NAME VARCHAR2(10) INTEREST VARCHAR2(10) HOBBIES VARCHAR2(10) OLD_ID VARCHAR2(25) SORTED NUMBER FAIL NUMBER TIME NUMBER FLAG NUMBER POINT NUMBER SF NUMBER ACTIVE NUMBER
and Query depending on this table sometimes provide a row or sometimes not. Query is as follows which we are using
SELECT ANI INTO SUBANI FROM (SELECT ANI FROM TBL_FEMALE WHERE point=(select max(point) from tbl_female where circle_name in('pb','hr','hp','jk','pb','bir','dl','mp','mum','mh','gj') and sf=0 and login_status='LOGIN' and status in ('A','B') ) and circle_name in('pb','hr','hp','jk','pb','bir','dl','mum','mh') and sf=0 and login_status='LOGIN' and status in ('A','B') ORDER BY RANDOM.RAND_MAX(20)) WHERE ROWNUM = 1;
A B ---------------------- 2*3 2*4*5 4*5 column B contain no data.
I want to create a function which can be used in a select query,and the output should come like that :-
A B ---------------------- 2*3 6 2*4*5 40 4*5 20
Means column B contains the resultant value of column A.And the above output should come through a select statement.You can use any function inside the select statement.
I am using oracle 11G database,I have to check length of name column value from employee table and if length(name) > 39 then value should be substr(name,0,39) else value should be name only. i tried below code
select CASE when length(name) > 39,substr(name,0,39) else name END
from employee but its not working ..can I do this using decode too ? ,,which one would be better or this is not a right way ?
Orcl Ver: 11g R2. I am leveraging the Oracle's pipelined table function.It's working fine for static SQL.
create or replace package test13_pkg as type r_disc_req is record(disc_line_id number,
req_id number); type t_disc_req is table of r_disc_req; function F_GetDiscReq return t_disc_req pipelined; procedure P_ProcessDiscReq;end; CREATE OR REPLACE PACKAGE BODY test13_pkgAS FUNCTION F_GetDiscReq RETURN t_disc_req PIPELINED IS lo_disc_req r_disc_req; BEGIN FOR r_row IN (SELECT disc_line_id, req_id FROM edms_disc_lines_stg WHERE ROWNUM < 10) LOOP lo_disc_req.disc_line_id := r_row.disc_line_id; lo_disc_req.req_id := r_row.req_id; PIPE ROW (lo_disc_req);
To display highest marks,least marks,average marks,total marks of the student name entered.
desc stud; Name Null? Type ----------------------------------------- -------- ---------------------------- SID NUMBER NAME VARCHAR2(20) M1 NUMBER M2 NUMBER
I am interested if there maybe exists any function that would return all source tables that are present in the given sql. For example function('select 'abc' from table_1, table2') would return a list containing 'table_1' and 'table_2'.
I've used a date in execute immediate query in function, but at the time passing the date as input parameter and getting the result i'm getting following error.
SQL> select getstockqty(1,to_date('31/03/2012','dd/mm/yyyy')) from dual; select getstockqty(1,to_date('31/03/2012','dd/mm/yyyy')) from dual * ERROR at line 1: ORA-01858: a non-numeric character was found where a numeric was expected ORA-06512: at "MIS.GETSTOCKQTY", line 11
One of our query is not using function based index, the required priv is granted to the user executing the query and also tables stats are gathered? what could be the reason for the query to not to pick the FBIndx? the table is a huge one having million of records, is it that CBO thinks that not picking FB indx is the best execution plan? let me know how can we make the query use the FB indx, also there is a restriction that we cannot force it using hints.
Am calling the Function Batch to insert an update statemtnt into Batch_statement table in the DOWNLOAD_FUNC .But its failing with the error
SQL Error : ORA-14551: cannot perform a DML operation inside a query
Below Is the
FUNCTION BATCH(numTABLE_ID IN NUMBER, varSTMT IN VARCHAR2) RETURN NUMBER IS BEGIN INSERT INTO BATCH_STATEMENT(QUEUE_ID,TABLE_ID,STATEMENT,QUEUE_SEQUENCE_ID) VALUES (numQUEUE_ID,numTABLE_ID,varSTMT,1); RETURN 1;
I have a query that uses a function to find the business days between two dates.It sums the total number of days between two dates per employee to find the total days for the past 30, 90, or 365 days.
The problem is that the query takes 21 second to return the last 30 days.Over 70 second to return the last 90 days and over 140 second to return the last 365 days.Do you know how I could tune the query to return faster? Below is the query for the last 30 days:
select dwt_emp_id, SUM((SELECT GET_BDAYS(DWT_DATE,DWT_CREATE_DATE) FROM DUAL)) from dwt_dvt_work_time where dwt_create_date > sysdate - 30 and dwt_hours > 4 and dwt_usr_uid_created_by <> -1 group by dwt_emp_id order by dwt_emp_id
Here's the function: CREATE FUNCTION get_bdays (d1 IN DATE, d2 IN DATE) RETURN NUMBER IS total_days NUMBER(11,2); holiday_days NUMBER(11,2); [code]....
I'm attempting to use dynamic SQL to execute a function that returns a user-defined collection. The problem is that I don't know how to use dynamic SQL to handle user-defined types...or if this is even possible?
The code I have is as follows:
CREATE OR REPLACE PACKAGE qi_test IS
TYPE typ_qi_data IS RECORD( iQIFlag NUMBER(1), iIPFlag NUMBER(1), iRiskIndicator NUMBER(1), iDenominator NUMBER(8), iNumerator NUMBER(8) [code]........
I want to be able to execute the above function using dynamic SQL. Initially tried:
DECLARE f2_data qi_test.typ_qi_data_tab; BEGIN EXECUTE IMMEDIATE 'begin :1 := qi_test.get_f2_data; end;' USING OUT f2_data; [code]......
...but this just produces "PLS-00457: expressions have to be of SQL types". So it looks like I can't do it this way if the returned data type is user defined. I know it would be easier in this instance to just use something like:
f2_data := qi_test.get_f2_data;
...rather than EXECUTE IMMEDIATE, but it's the principle that I need to get right as it forms part of a much bigger piece of work.
SELECT rp.id r_paper_id, COUNT(*) created, COUNT( CASE WHEN a.approved_yn = 'Y'
[code]....
But it fails, saying that 'single-row subquery returns more than one row' when I introduce the 'unique_users' clause. The remaining fields of the output are correct.
when am trying to use nvl for one condition it is taking lot of time to execute but when am removing nvl function then the query executing in 2 min. condition is given below
