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

PL/SQL :: Using Recursive Subquery Factoring In A Function?

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

PL/SQL :: Sysdate Produces 00:00 Time

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

Storing Select Query Result Into Array And Using It In Another Query?

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

Performance Tuning :: Adding +0 And -0 To A Number Predicate Produces Different Plans

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

PL/SQL :: Query To Get Result Set

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

Concatenating Result Of Query

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

SQL & PL/SQL :: Query Result Into Excel Or CSV

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

SQL & PL/SQL :: Query Result In One Line

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

SQL & PL/SQL :: Different Result On Every Execution Of Query?

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

SQL & PL/SQL :: Query Yields No Result

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

Result Of Expensive Query

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

SQL & PL/SQL :: Wrong Result For Query With Like And %?

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

SQL & PL/SQL :: How To Execute One Query Result In Another At Runtime

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

SQL & PL/SQL :: Inserting Result Set Of Query Into Another Table

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

SQL & PL/SQL :: Query Result More Row Return In Oracle

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

PL/SQL :: Send Query's Result Via Utl_stmp?

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

Globalization :: Wrong Result For Query With Like And %?

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

Which Background Process Is Responsible For Getting Result Of SQL Query

Jul 21, 2011

Which background process is responsible for getting result of sql query issued to an oracle engine?

View 1 Replies View Related

SQL & PL/SQL :: Query Result Into XLS File With Column Names?

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

SQL & PL/SQL :: Query Where Condition Has 4 Rows In Table But Result Is Zero Row

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

Forms :: Generate Query Result In Oracle 10g

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

SQL & PL/SQL :: Check Datatype Of Result From Select Query?

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

SQL & PL/SQL :: Show Result Of Query Just Like Pivot Table?

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

PL/SQL :: Incorrect Query Result Over Database Link

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

Comparing External File Data With Sql Query Result

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

SQL & PL/SQL :: How To Refer Result Of Inner Query (analytic Function) In Outer One

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

Server Administration :: Dropped User In Query Result?

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

PL/SQL :: Oracle 11g / R2 Query Result Cache - Incremental Update

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

SQL & PL/SQL :: Inserting Result Set Of Query Into Corresponding SQL Table Type Variable?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved