SQL & PL/SQL :: Why Sub Query Factoring Produces Different Result
Aug 19, 2010
select 1 from dual
where 1 >= any (select 1 as col from dual
union all
select 1 from dual
union all
select 1 from dual);
Output:
------
1
-
1
[code]....
Why the sub query factoring produces different result.Is sub query factoring can be a problem and leads to different result?
View 27 Replies
ADVERTISEMENT
Sep 24, 2012
I need to use Recursive Subquery Factoring for a project of mine, specifically I need to use some code I have found in a function.
[URL]
The code I have so far is this:
create or replace
function recursive
(
pattern in raw
, solution in raw
) return number as
[code]....
But when trying to compile it in Oracle SQL Developer, I get the following two errors:
Error(9,1): PL/SQL: SQL Statement ignored
Error(10,30): PL/SQL: ORA-00907: missing right parenthesis
The errors refer to these two lines:
with x( s, ind ) as
( select sud, instr( sud, '' '' )
I have looked over and over the code again, and I do not see any missing parenthesis. Furthermore I tried using with in a smaller test function:
create or replace function test
(
p in number
)
return number as
v number;
begin
with t(a) as
[code]....
Which compiles just fine, so I'm not sure why that line is being ignored in this function. The whole idea here is to replace the sudoku string in the function with a variable that I built from the two parameters.
View 5 Replies
View Related
Apr 11, 2013
I have a stored procedure that stores a record containing a date field.
The syntax is
insert into audit_log values (lv_sequence, sysdate, REC_TYPE, p_pln_id, OPER_UPDATE, log_message);
This works except that the date stored in the record has a time of 00:00. This does not work well for an audit log.
How can I store a date that includes a time?
An interesting fact is...I used this same command in SQL Developer to store a record and the date stored did have a time???
View 7 Replies
View Related
Aug 7, 2009
I am looking to simplify the below query,
DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)
Since both the inner queries are same,I want to extract out to a local variable and then use it.
Say,
Array var = SELECT ID FROM B WHERE BID=0;
And then ,
DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)
How to do this using SQLPLUS?
View 8 Replies
View Related
Apr 29, 2011
I have a weird optimizer behaviour on a 10.2.0.4 db.When i add "+0" or "-0" to a number predicate, the optimizer produces 2 differents plans. I dont see why. Here are the statements:
A -First statement with suboptimal plan :
SELECT /*KO*/ TFXPPRODUCT.PRODUCTID,
TFXPPRODUCT.PRODUCTTYPE,
VFUTFIX.Datech,
TFXPPRODUCT.SHORTLABEL,
VFUTFIX.TAUCPN,
VFUTFIX.Tik,
[code]....
View 1 Replies
View Related
Feb 12, 2013
I want to run below query to get the result set that I am after. But It takes long time even with the indexes...Here in IM_Mapping table is having 1.7 mio records and T_Extract table about 35000 records. All the other tables having below 1000 records
SELECT DISTINCT T_Extract.SerGrp, T_Extract.SerCat, T_Extract.Component, T_Extract.Mod_Item ID,
" ##" AS IM, IM_Mapping.TrfCode, IM_Mapping.CatCode, IM_Mapping.CatDescr, IM_Mapping.SubCatCode, IM_Mapping.SubCatDescr,
LKP_Item.Group, LKP_Item.Modality Source
FROM ((LKP_Product
[code]....
T_Extract.Component
T_Extract.Mod_Item ID
T_Extract.SerCat
T_Extract.SerGrp
Just want to know if there is any possibility of running the query with all the Groups in one go
Even if I run group wise it took 30 min query still running without any results...Is there anyway of restructuring the query for better performance.
View 1 Replies
View Related
Aug 30, 2007
My scenario is something like this:
MyTable
Rownum Colval
1 23
2 34
3 45
I need to write a query which will get me output: 233445, i.e. all the three rows concatenated. How can it be done? I want to do it through sql only and not to use PL/SQL. Is this possible?
View 2 Replies
View Related
Jun 15, 2011
I have a sql query which fetch the data from 4 different tables. I want to write the output of that query into a excel or a CSV file without using TOad and all. Let me know is it possible via creating function or procedure.
View 4 Replies
View Related
Aug 29, 2011
I have a select that return the query like this:
EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI
434Ronaldo 11 25411414compras@gralha.br
434Ronaldo 11 21454117compras2@gralha.br
434Thiago 13 25418745thiago.alcarin@br.com.br
so,I need the query result in just one line...
EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI
434Ronaldo 11 25411414compras@gralha.br 434Ronaldo 11 21454117compras2@gralha.br
I saw some exemples of "pivot query" but I still looking for a solution.
View 10 Replies
View Related
Jan 13, 2011
when I am executing the below query getting diffrent count every time and not able to guess what is happening.
SELECT
count(1)
FROM table1 where table1.LAST_UPDATE_DATE >= current_date - interval '9' day
View 6 Replies
View Related
Mar 20, 2012
in my table there is a row as below.
CITY_ICITY_NST_PROV_CPSTL_CUPDT_USER_IUPDT_TSUPDT_PGM_I
342DC79a03654822-FEB-12 02.02.13.410000000 PMOMR
when i use below below query, there are no values returned.
select city_i from city_e where (trim(city_n))= 'DC' and st_prov_c=79 but while i use below query, im bale to get the result
select city_i from city_e where city_n='DC' and st_prov_c=79
View 5 Replies
View Related
Jul 11, 2012
I need a a query which will give the result of most expensive query i.e. which is taking most of I/O /memory.
View 3 Replies
View Related
Jul 21, 2012
I have a strange problem with query with like and %.
When I run this script:
ALTER SESSION SET NLS_SORT = 'BINARY_CI';
ALTER SESSION SET NLS_COMP = 'LINGUISTIC';
-- drop table test1;
CREATE TABLE TEST1(K1 NVARCHAR2(80));
INSERT INTO TEST1 VALUES ('gsdk');
[code]....
I get this:
K1
ŁFa
ła
Śab <- WRONG
Śrrrb <- WRONG
4 rows selected
When i change datatype in column to varchar2 this code work correct.
The execution plan:
PLAN_TABLE_OUTPUT
SQL_ID d3d64aupz4bb5, child number 2
select * from TEST1 where k1 like N'Ł%'
Plan hash value: 4122059633
Id Operation Name Rows Bytes Cost (%CPU) Time
0 SELECT STATEMENT 2 (100)
* 1 TABLE ACCESS FULL TEST1 1 82 2 (0) 00:00:01
[code]....
View 7 Replies
View Related
Dec 14, 2011
I have a query, when i run this this will give another sql statement. I want run this dynamically...
How to proceed?
View 10 Replies
View Related
Jul 24, 2012
CREATE TABLE tbl_emp
(
name VARCHAR2(20),
price NUMBER,
[Code]...
NAME PRICE DATAENTRD ROWRANK
aaa 9999 24.07.2012 05:56:00 1
aaa 10000 24.07.2012 05:55:58 2
I want to insert this result into another table, how can I do it??
Quote:TABLE
CREATE TABLE tbl_emp_result_set
(
name VARCHAR2(20),
price NUMBER,
dataEntrd date,
rowrank number
)
View 8 Replies
View Related
Apr 3, 2013
I have a view base on this query returned more rows returned.
select *
From (
Select c_code ,
from_date,
c_range ,
[code].........
View 5 Replies
View Related
May 25, 2013
11.2.0.3 got the send mail code from [URL]
CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
[Code].....
/I want to call send_mail to pass a query output to "p_message": as example: select sid, program,module,machine from v$session where program='XXX';
And have been struggling due to lack of experience in PLSQL.
View 18 Replies
View Related
Jun 28, 2012
I have a strange problem with query with like and %.
When I run this script:
ALTER SESSION SET NLS_SORT = 'BINARY_CI';
ALTER SESSION SET NLS_COMP = 'LINGUISTIC';
-- SELECT * FROM NLS_SESSION_PARAMETERS;
-- drop table test1;
CREATE TABLE TEST1(K1 NVARCHAR2(80));
[code]....
When i change datatype to varchar2 this code work correct.
The execution plan:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID d3d64aupz4bb5, child number 2
-------------------------------------
select * from TEST1 where k1 like N'Ł%'
[code]....
Note - dynamic sampling used for this statement (level=2)
View 2 Replies
View Related
Jul 21, 2011
Which background process is responsible for getting result of sql query issued to an oracle engine?
View 1 Replies
View Related
Oct 6, 2012
I want to load query result into .xls file with column names by using plsql.
Like this i need to generate 4 files and zip it then send mail with attachement of that zip file.
Can we automate in PLSQL? If it is possible pls share the script.
Ex: select ename,eno,dept,deptno,sal,hiredate from employee;
ENAME ENO DEPT DEPTNO SAL HIREDATE like this we need to store the query result into .xls file.
View 1 Replies
View Related
Oct 29, 2012
There are 4 rows in table with stat_flag 'Updated Record' and stat_date with todays date.
stat date has date & time both, for that reason just trying to format with yyyy.mm.dd
I am getting zero rows as result.
where STAT_FLAG = 'Updated Record' and to_date(stat_date,'yyyy.mm.dd') = to_date(sysdate(),'yyyy.mm.dd')
View 4 Replies
View Related
Apr 3, 2013
Is there any way to Create Form in which i give a query and in result it return the query result in detail block in form of fields just like we return in toad or pl/sql developer.
View 3 Replies
View Related
Jul 7, 2011
I have this query
SELECT wmsys.wm_concat (gp.prog_acronym)
FROM inf_program gp, ea_audit_program ap
WHERE ap.sys_prog_id = gp.sys_prog_id
AND ap.sys_audit_id =484
is there any way I can check the datatype of the result of the above query ? ,my dba added some patch to oracle , after the patch this query is returning a clob in java , it should return string and it used to return string before patch and in other databases it returns string, I can check the return type only from java side , is there any way oracle can say me the datatype ?
View 13 Replies
View Related
Jul 20, 2011
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.
View 1 Replies
View Related
Jul 2, 2012
My problem is I have 3 tables (TEST_TBL1, TEST_TBL2, TEST_TBL3). TEST_TBL2 and TEST_TBL3 are in remote database and I use database link to join them. The following query returns incorrect result (I seems that it ignore the where clause)
SELECT * FROM TEST_TBL1 JOIN TEST_TBL2@db_remote USING (KEY1) JOIN TEST_TBL3@db_remote USING (KEY2) WHERE KEY1=XXX OR KEY2=YYY;
I am on 11R1 (11.1.0.7)
FOR EXAMPLE:
Local database:
CREATE TABLE TEST_TBL1
(
KEY1 NUMBER(5) NOT NULL,
[Code].....
View 8 Replies
View Related
Mar 16, 2009
I need to compare data of .csv file with the SELECT query result or with any table data.
Is there any possibilities in ORACLE to fulfill this requirement?
View 3 Replies
View Related
Feb 23, 2012
How can I refer the result of inner query - max(t.col3) in the outer query. I tried the 'group by' but it is not performing well
select
t1.col5, t1.col6
from t1,
(select t.col1
t.col2
[code].......
View 8 Replies
View Related
Jul 17, 2005
I had droped one user from my oracle database..but when i run this below query i found that user exist
SQL>select username from dba_users;
but when i try to connect that user i get result
SQL> connect sunil/sunil
ERROR:
ORA-01017: invalid username/password; logon denied
Now tell me how to remove this user from result of this query
SQL>select username from dba_users;
View 11 Replies
View Related
Oct 15, 2012
In Oracle 11g/R2, I created replica of HR.Employees table & executed the following statement (+Although using SUM() function is non-logical in this case, but just testifying the result+)
STEP - 1
SELECT /+ RESULT_CACHE */ employee_id, first_name, last_name, SUM(salary)*
FROM HR.Employees_copy
WHERE department_id = 20
GROUP BY employee_id, first_name, last_name;
EMPLOYEE_ID FIRST_NAME LAST_NAME SUM(SALARY)
-------------------------------------------------------------------------------------------------------
202 Pat Fay 6000
201 Michael Hartstein 13000
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3837552314
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 130 | 4 (25)| 00:00:01 |
| 1 | RESULT CACHE | 3acbj133x8qkq8f8m7zm0br3mu | | | | |
| 2 | HASH GROUP BY | | 2 | 130 | 4 (25)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMPLOYEES_COPY | 2 | 130 | 3 (0)| 00:00:01 |
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
*690* bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
STEP - 2
INSERT INTO HR.employees_copy
VALUES(200, 'Dummy', 'User','Dummy.User@email.com',NULL, sysdate, 'MANAGER',5000, NULL,NULL,20);
STEP - 3
SELECT /*+ RESULT_CACHE */ employee_id, first_name, last_name, SUM(salary)
FROM HR.Employees_copy
WHERE department_id = 20
GROUP BY employee_id, first_name, last_name;
EMPLOYEE_ID FIRST_NAME LAST_NAME SUM(SALARY)
--------------------------------------------------------------------------------------------------
202 Pat Fay 6000
201 Michael Hartstein 13000
200 Dummy User 5000
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3837552314
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 195 | 4 (25)| 00:00:01 |
| 1 | RESULT CACHE | 3acbj133x8qkq8f8m7zm0br3mu | | | | |
| 2 | HASH GROUP BY | | 3 | 195 | 4 (25)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMPLOYEES_COPY | 3 | 195 | 3 (0)| 00:00:01 |
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
*714* bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
In the execution plan of STEP-3, against ID-1 the operation RESULT CACHE is shown which shows the result has been retrieved directly from Result cache. Does this mean that Oracle Server has Incrementally Retrieved the resultset?
Because, before the execution of STEP-2, the cache contained only 2 records. Then 1 record was inserted but after STEP-3, a total of 3 records was returned from cache. Does this mean that newly inserted row is retrieved from database and merged to the cached result of STEP-1?
If Oracle server has incrementally retrieved and merged newly inserted record, what mechanism is being used by the Oracle to do so?
View 2 Replies
View Related
Aug 28, 2012
I am unable to insert the result set of query into corresponding SQL Table type variable where as same functionality can be accomplished by PL/SQL table type variable. Can't we access the same by using SQL type variable?
Ex:
Step 1:
SQL Object type , Table type Objects creation :
drop type sql_emp_tab_type ;
drop type sql_emp_type ;
create or replace type sql_emp_type as object
(
empno number,
ename varchar2(20),
[code]...
Step 2: Accessing the table type object from PL/SQL block
SQL>
SQL> declare
2
3 tab_type_var sql_emp_tab_type := sql_emp_tab_type();
4
5 begin
6
7 tab_type_var.extend(10);
[code]...
Step 3: Instead of SQL Table type , if we define the corresponding PL/SQL table type variable
SQL> declare
2
3 --tab_type_var sql_emp_tab_type := sql_emp_tab_type();
4
5 type pl_sql_emp_type is record
6 (
7 empno number,
[code]...
View 3 Replies
View Related
