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


ADVERTISEMENT

SQL & PL/SQL :: Use Only SQL Joins Not Minus Or Subquery Or Any Function

Aug 2, 2011

i have two tables having some fields .In table table1 there is 2 lakh rows and in table table2 there is 3 lakh rows.In both table 2 lakh rows are common.I have to find those 1 lakh rows which are distinct but the condition for this i have to use only sql joins not minus or subquery or any function.

when i doing this.select t2.id,t2.name,t2.loc from table1 t1,table2 t2 where t1.id <>t2.id;

View 8 Replies View Related

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 View Related

WITH Recursive Query Without Using WITH?

Sep 8, 2012

I need to do a PL/SQL program that prints the same that this WITH clause query, without using WITH or CONNECT BY, i was thinking about a solution with cursors?

WITH recursiveBOM
(assembly_id, assembly_name, parent_assembly) AS
(SELECT parent.assembly_id,
parent.assembly_name,
parent.parent_assembly
FROM bill_of_materials parent

[code].....

View 8 Replies View Related

SQL & PL/SQL :: Recursive Table Query?

May 5, 2013

I have the following table structure...............

Main_Head table name

main_head_id ,pk
head_desc,
head_id ,
sub_head_id

keys
col table ref col
sub_head_id main_head head_id

the table is recursive table self join
-----------------------------------------
now i want to write the query which return all head_desc which have same head_id

View 12 Replies View Related

SQL & PL/SQL :: Update With Recursive Query

Aug 5, 2010

I have this SQL select to give me all the nodes of a tree starting at a particular node:

SELECT tree.node_id, LEVEL depth_level
FROM tree_of_nodes nodes
START WITH nodes.node_id = 1000
CONNECT BY PRIOR nodes.node_id = nodes.parent_id

I need to update a column called dept_level, which is essentially the same as Oracle's LEVEL. Something like this statement is what I need, but obviously this statement doesn't work:

update tree_of_nodes
set depth_level = LEVEL
START WITH nodes.node_id = 1000
CONNECT BY PRIOR nodes.node_id = nodes.parent_id

I've tried inline views and other approaches and have not found a method that works.

View 3 Replies View Related

Dynamically Build (recursive) Xml SQL Statement

May 3, 2013

We have an requirement to create xml data for entire database (selected tables) which are in hierarchy.Procedure should read node_mapping table having parent and child tables relationship info and build XML Select statement.

Currently it is building SQL statement whenthere are one parent having multiple childrens i.e Dept having emp, emp_act, emp_rsch..but when child node are having childrens then it is not working - it has to repeatedly call this procedure (recursive) and build below given SQL statement.

1. To change procedure to build xml sql statement when there are multiple childrens to child nodes (hierarchy)
2. To format the output in xml data

We are using ORACLE 11G and WINDOWS 7

CREATE TABLE node_mapping
(
NODE_ID NUMBER(5) PRIMARY KEY,
PARENT_NODE VARCHAR2(100),
CHILD_NODE VARCHAR2(100),
PARENT_NODEID VARCHAR2(50),
CHILD_NODEID VARCHAR2(50)
[code]....

View 1 Replies View Related

SQL & PL/SQL :: Overcome Recursive Triggers In Oracle?

Jul 14, 2012

How can we overcome recursive triggers in oracle SQL?

View 7 Replies View Related

SQL & PL/SQL :: ORA-00604 - Error Occurred At Recursive Level

Mar 14, 2011

I am using Oracle 10g Database as back end and Developer 6i as front end.I have a procedure which is called upon SAVE. Means before COMMIT;This procedure holds few updates and delete statments.

This prcedure is throughing sql error as follows: ORA-00604: error occurred at recursive SQL level

The error occur when an delete statment is issued which as follows: DELETE FROM DUMMY_TAB001;

I issue the same statement from SQL it is running fine. But when my from runs on this delete statment encounter it through the above oracle error ORA-00604.

View 7 Replies View Related

JDeveloper, Java & XML :: Recursive Hierarchy Query To XML?

Mar 5, 2013

To use dynatree (URL] I want the result to be in the xml form.but the result is not what I want.

SELECT
XMLELEMENT("div",xmlattributes('tree' AS "id"),
(SELECT DBMS_XMLGEN.getXMLType(
DBMS_XMLGEN.newContextFromHierarchy('
SELECT LEVEL,
case
when CONNECT_BY_ISLEAF = 0 then

[code]....

View 6 Replies View Related

SQL & PL/SQL :: Oracle 10.2.0.4 On Solaris 10 - Recursive Query Using Hierarchy

Nov 9, 2011

We are on oracle 10.2.0.4 on solaris 10. My question is on a sql query. Is it possible to rewrite a query to avoid the connect by and prior constructs and use joins? For example i have the following query:

SELECT empno,
ename,
job,
mgr,
hiredate
FROM emp
START WITH empno in (7566,7698)
CONNECT BY PRIOR empno = mgr

How can it be rewritten using a two table join (self join)? I am not sure if it can be done and whether it is possible.

View 4 Replies View Related

SQL & PL/SQL :: Copy Into Same Table / Recursive Rename Of Duplicates

May 11, 2011

In MS Windows, if I copy a file and paste it into the same folder, I get a copy with the text 'Copy of' in front of the file name. If I paste it again, I get another copy with a different version number.

E.g.
sqlnet.log
Copy of sqlnet.log
Copy (1) of sqlnet.log
Copy (2) of sqlnet.log

I was wondering if I could copy existing rows into a table and do the same thing?

So, for example if I had this table:

create table tst_srch (srch_is varchar(100), user_name varchar(100), srch_name varchar(100));

insert into tst_srch values (1,'USER1','SRCHA');
insert into tst_srch values (2,'USER1','SRCHB');
insert into tst_srch values (3,'USER1','SRCHC');
insert into tst_srch values (4,'USER1','SRCHD');
insert into tst_srch values (5,'USER2','SRCHC');
insert into tst_srch values (6,'USER2','SRCHD');
insert into tst_srch values (7,'USER2','SRCHD_1');

Could I write a procedure like copy_searches('USER1','USER2') that would copy all USER1's searches to USER2 - including renaming any duplicates.

So it would create these new rows:

8,USER2,SRCHA
9,USER2,SRCHB
10,USER2,SRCHC_1
11,USER2,SRCHD_2

I've looked at various insert statements, merge and match statements and exception handling in procedures .

View 6 Replies View Related

SQL & PL/SQL :: Recursive Query - Each Field Starts Where Previous Ends

Dec 6, 2011

I have a table:

create table FIELDS
(
FIELD_NAME VARCHAR2(30) not null,
PRG_FIELD NUMBER not null,
LENGTH NUMBER
);

with

INSERT INTO FIELDS VALUES('FIELD1', 1, 3);
INSERT INTO FIELDS VALUES('FIELD2', 2, 3);
INSERT INTO FIELDS VALUES('FIELD3', 3, 4);
INSERT INTO FIELDS VALUES('FIELD4', 4, 2);
INSERT INTO FIELDS VALUES('FIELD5', 5, 1);

I need to insert in a table:

create table STUFF
(
FIELD_NAME VARCHAR2(30) not null,
FSTART NUMBER not null,
LENGTH NUMBER
);

And the output I want is:

INSERT INTO STUFF VALUES('FIELD1',0,3);
INSERT INTO STUFF VALUES('FIELD2',3,3);
INSERT INTO STUFF VALUES('FIELD3',6,4);
INSERT INTO STUFF VALUES('FIELD4',10,2);
INSERT INTO STUFF VALUES('FIELD5',12,1);

So each field starts where the previous (ordered by PRG_FIELD asc) ends.

I think the query should use both lag and connect by but I haven't had any luck writing it. The problem is that all the examples I've seen around, using connect by prior, utilize 2 fields with different names, es connect by prior emp_id = mgr_id. Instead I should do something like connect by prior prg_field = prg_field-1 but that doesn't seem to work.

PS: I don't necessarily need to do this, I have a guy manually writing the inserts, this is just an exercise I would like to figure out

View 1 Replies View Related

SQL & PL/SQL :: ORA-00036 Error (maximum Number Of Recursive Levels)

Aug 19, 2010

Ive just tried running a simple

update BASIC set 'column name' = NULL

Which works fine if i specify a where clause that returns a low amount of values, but im trying to run this update for the whole column (1000's or records).

Ive had no experience of this error before and am unsure of where to start, ive had a quick read around but see something of removing triggers?

The full error is :

ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "new.su_Table", line 61

View 22 Replies View Related

Security :: ORA-00604 / Error Occurred At Recursive SQL Level 1

Nov 3, 2010

I'm trying to drop a user but it gives me below error message-

SQL> drop user <username> cascade;

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

However when i check from dba_queue_tables or user_queue_tables it doest show any queue tables.

View 6 Replies View Related

PL/SQL :: ORA-00604 - Error Occurred At Recursive SQL Level String

Jun 22, 2013

Oracle Version :- 11.2.0.2 I found a error in a trigger(Statement Level) ORA-00604:error occurred at recursive SQL level string. Before Finding this issue,Once the DB Response was slow . Will this be the issue Of DB Slow response. The Above trigger fires for each entry in an transaction table. The code is Patched and was executed . The above issue was found during another issue and not the DB Slow response.  My Doubt is Whether DB response slow issue would be because of this. Now after fixing this Slow response was not reported. 

View 0 Replies View Related

PL/SQL :: Trigger Error Maximum Number Of Recursive SQL Levels

Aug 29, 2012

I have getting error will insert into table i.e

i have a trigger as below

create or replace trigger INS_ERRORS
before insert on MIG_STG_ERRORS
for each ROW
declare
V VARCHAR2(22);
[code]........ 

when i insert into MIG_STG_ERRORS getting error message like 00036. 00000 - "maximum number of recursive SQL levels (%s) exceeded"

*Cause:    An attempt was made to go more than the specified number of recursive SQL levels.
*Action:   Remove the recursive SQL, possibly a recursive trigger.

View 4 Replies View Related

SQL & PL/SQL :: Query To Fetch Steps Of Test Having Recursive Calls To Other Tests

Jul 17, 2012

I am working on the quality center oracle database to write a query that fetches all steps of a test case including the ones having calls to tests. Structure of table is explained below. I've made up an example and attached it as an image to this post. This image also has the expected result of the query I want to write.

Table Name: STEPS (This table contains steps belonging to tests. Some steps are simply calls/links to other tests)

Columns:
STEP_ID (primary key - integer)
STEP_NAME (char)
STEP_DESC (char)
ORDER (integer)
TEST_ID (reference to table TEST.test_id)
[code].......

Referring to the example (see attached image), I'm looking to write a query that gives me all steps (including steps from called tests) of the test - "Empty trash from mailbox" in the correct order.

To start with I can write the following query to get steps of the test - "Empty trash from mailbox".
SELECT * FROM steps WHERE test_id = (SELECT test_id FROM test WHERE test_name = 'Empty trash from mailbox')

View 7 Replies View Related

Server Administration :: ORA-00604 / Error Occurred At Recursive SQL Level 1

Oct 7, 2010

When trying deploy an mapping, the database link get this error:

ORA-06550: PACKAGE BODY, line 0, column 0:
ORA-04052: error occurred when looking up remote object db1.someC@db1@LOC_db
ORA-00604: error occurred at recursive SQL level 1
ORA-12170: TNS:Connect timeout occurred

I can use tnsping towards the db. I can use

elect * from v$instance@db1@loc_db

View 1 Replies View Related

PL/SQL :: Trigger - Error - ORA-00036 - Maximum Number Of Recursive SQL Levels?

Aug 10, 2013

From front end the user will enter VACANCY_ID in the label box and once he saves that transaction, in background VACANCY_CD filed(sequence) willautomatically generated

.below is my code:{code}create table tmp1(vac_id number,vac_cd number);create sequence tmp1_seq MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;select tmp1_seq.nextval from dual; ---initiating seqselect tmp1_seq.currval from dual; --checking the current val--trigger creationCREATE OR REPLACE TRIGGER TRIGGER1 AFTER  INSERT ON TMP1 FOR EACH ROW   DECLARE seqval

[code]...

Error: ORA-00036: maximum number of recursive SQL levels (50) exceededORA-00036: maximum number of recursive SQL levels (50) exceeded 

View 6 Replies View Related

SQL & PL/SQL :: From Analytical To Subquery

Jun 10, 2010

I was reading a tutorial for analytical function and i found something like this

sum(princial) keep(dense_rank first order by d_date) over partition by (userid, alias, sec_id, flow, p_date)

How to translate this into simple queries / subquery? i am aware that analytical function are faster but i would like to know how this can translate to using query without analytical function.

View 12 Replies View Related

SQL Subquery And Aggregation

Nov 6, 2010

I have a database containing the following after entering the following sql command

SELECT TITLES.TITLE_ID AS TITLE_ID, (PRICE * SALES),
TITLES.ROYALTY_RATE AS ROYALTLY_RATE,
AUTHOR_TITLES.ROYALTY_SHARE AS ROYALTY_SHARE,
AUTHORS.FNAME AS FNAME, AUTHORS.LNAME AS LNAME
FROM TITLES, AUTHOR_TITLES, AUTHORS
WHERE TITLES.TITLE_ID = AUTHOR_TITLES.TITLE_ID
AND AUTHORS.AU_ID = AUTHOR_TITLES.AU_ID

TIT (PRICE*SALES) ROYALTLY_RATE ROYALTY_SHARE FNAME LNAME
--- ------------- ------------- ------------- --------------- ---------------
T01 12446,34 ,05 1 Sarah Buchman
T02 190841,7 ,06 1 Sarah Buchman
T03 1025396,65 ,07 1 Christian Kells
T04 168882,99 ,08 ,6 Hallie Hull
T04 168882,99 ,08 ,4 Klee Hull
T05 1400008 ,09 1 Klee Hull
T06 225834 ,08 1 Wendy Heydemark
T07 35929790 ,11 ,5 Wendy Heydemark
T07 35929790 ,11 ,5 Klee Hull
T08 40950 ,04 1 Kellsey
T09 69750 ,05 1 Kellsey

TIT (PRICE*SALES) ROYALTLY_RATE ROYALTY_SHARE FNAME LNAME
--- ------------- ------------- ------------- --------------- ---------------
T10 1 Wendy Heydemark
T11 752042,77 ,07 ,3 Hallie Hull
T11 752042,77 ,07 ,3 Klee Hull
T11 752042,77 ,07 ,4 Kellsey
T12 1299012,99 ,09 1 Wendy Heydemark
T13 313905,33 ,06 1 Sarah Buchman

17 rows selected.

What I need to do is create a subquery and use Aggregation to list the author receiving the greatest royalties on revenue. so i used the command to get the royalties

SELECT TITLES.TITLE_ID AS TITLE_ID, (PRICE * SALES),
AUTHORS.FNAME AS FNAME, AUTHORS.LNAME AS LNAME,
((PRICE * SALES) * TITLES.ROYALTY_RATE * AUTHOR_TITLES.ROYALTY_SHARE) AS ROYALTIES
FROM TITLES, AUTHOR_TITLES, AUTHORS
WHERE TITLES.TITLE_ID = AUTHOR_TITLES.TITLE_ID
AND AUTHORS.AU_ID = AUTHOR_TITLES.AU_ID

TIT (PRICE*SALES) FNAME LNAME ROYALTIES
--- ------------- --------------- --------------- ----------
T01 12446,34 Sarah Buchman 622,317
T02 190841,7 Sarah Buchman 11450,502
T03 1025396,65 Christian Kells 71777,7655
T04 168882,99 Hallie Hull 8106,38352
T04 168882,99 Klee Hull 5404,25568
T05 1400008 Klee Hull 126000,72
T06 225834 Wendy Heydemark 18066,72
T07 35929790 Wendy Heydemark 1976138,45
T07 35929790 Klee Hull 1976138,45
T08 40950 Kellsey 1638
T09 69750 Kellsey 3487,5

TIT (PRICE*SALES) FNAME LNAME ROYALTIES
--- ------------- --------------- --------------- ----------
T10 Wendy Heydemark
T11 752042,77 Hallie Hull 15792,8982
T11 752042,77 Klee Hull 15792,8982
T11 752042,77 Kellsey 21057,1976
T12 1299012,99 Wendy Heydemark 116911,169
T13 313905,33 Sarah Buchman 18834,3198

17 rows selected.

So how do I add up the royalties values associated with each author and find the max? for example I add klee hulls's royalties from each book and get 2,123,336.32(doing it by hand on calculator) what is the sql to find the max royalties for each author? P.S the answer should be KLEE HULL with 2,123,336.32

View 6 Replies View Related

Single Row Subquery Returns More Than One Row

Dec 6, 2012

I am returning the ORA-01427 after running the query below. why I am returning the error and how to address it.

select b.value , b.name, p.value ......
(case when p.value <> 'G2' then null else (select c.oldvalue from ad_changelog c
where c.record_id = b.c_bpartner_id and c.ad_table_id = 291
and c.ad_column_id = 4216 ) end) as oldtradeName
from c_bpartner b, zz_receipt r, zz_recp_alloc a, m_product p, ad_user us
where a.c_bpartner_id = b.c_bpartner_id
and a.zz_receipt_id = r.zz_receipt_id
and us.ad_user_id = r.createdby
and p.m_product_id = a.m_product_id

View 2 Replies View Related

Single-row Subquery Returns More Than One Row?

Dec 7, 2009

Just trying to update a table in which the sales amount is inserted when the sales amount is null.

I have

UPDATE ph2_customer_temp
SET sales_amount = (
SELECT sl.sales_amount
FROM PH2_CUSTOMER_TEMP pct
join
sales_limit sl
on substr(pct.site_code,1,2) = sl.state
where pct.credit_limit is null )

View 2 Replies View Related

Limiting Subquery To First Record?

Oct 1, 2007

restricting a subquery's results to the first record. If I use the following:

SELECT WU.DISCREP, M.PART, M.ETA
FROM DB.MICAP M
LEFT JOIN DB.WRITEUPS WU ON M.WRITEUPID=WU.WRITEUPID
LEFT JOIN DB.WUC WUC ON WU.WUCID=WUC.WUCID
WHERE (WU.AIRCRAFTID=205) AND (WU.CORRECTED=0)
ORDER BY WU.PACER DESC, WUC.WUCCODE

I get

-------------------------------------------------------
#5 GEN KICKED OFFLINECABLE02-MAR-08
#5 IDG HARNESS REQUIRES WIREWIRE24-MAY-08
#1 GEN WENT OFFLINECABLE02-MAR-08
AERIAL REFUEL DOORS W/N OPENLINEAR DEL29-SEP-07
AERIAL REFUEL DOORS W/N OPENCHECK VLV30-SEP-07
WEATHER RADAR TILT KNOB HASRADAR COMP05-OCT-07

But I need the highlighted line eliminated. I've tried DISTINCT subqueries in the WHERE M.WRITEUPID IN (SELECT DISTINCT... various experiments with joins, etc.

View 4 Replies View Related

Single-row Subquery Returns More Than One Row

Jan 10, 2012

I can't seem to wrap my head around this problem I'm having with a query. I need to update all rows in my ps_ntsr_gf_stufile tables with the concatenated values from the ps_classes_tbl table where a.CLASS_NBR = b.CLASS_NBR. I tried to limit it to emplid from the ps_stdnt_enrl table but no luck.

 UPDATE ps_ntsr_gf_stufile a
    SET a.CLASS_NAME = (SELECT CONCAT('SUBJECT', 'CATALOG_NBR')
    FROM PS_CLASS_TBL b
      WHERE a.CLASS_NBR = b.CLASS_NBR
      AND a.STRM = '1118'
      AND a.INSTITUTION = 'NT752')
      WHERE a.EMPLID IN (SELECT distinct EMPLID FROM PS_STDNT_ENRL); 

I'm still getting the ORA-01427 error.

View 1 Replies View Related

SQL & PL/SQL :: Error ORA-00904 While Using Subquery?

Jan 8, 2013

I'm facing an issue "ORA-00904". Below is the test case. Both the queries are different. I'm only focussed to find out the reason.

CREATE TABLE ACCT
(
ACCTNBR NUMBER(10)
) ;

[Code].....

In first query I'm able to refer to table alias A but in second query I'm not able to refer it. The only difference is that in second query I've not used outer table at second level. Is it the desired behaviour?

View 8 Replies View Related

SQL & PL/SQL :: Single Row Subquery Returns More Than One Row

Mar 20, 2012

/* (SELECT A.TOTAL_ALLOCATED_AMT FROM CLS_ALLOCATION_HDR@LAMS_PROD A WHERE A.APPROVAL_DATE BETWEEN BCTH.TRAN_FROM_DATE AND BCTH.TRAN_TO_DATE
AND A.CUSTOMER_ID=BCTH.CUSTOMER_ID AND ALLOCATION_ID IN (SELECT ALLOCATION_ID FROM CLS_ALLOCATION_DTL@LAMS_PROD B WHERE ENTITY_TYPE='REC' AND
B.ALLOCATION_ID=A.ALLOCATION_ID AND ENTITY_ID IN (SELECT RECEIPT_NO FROM CLS_RECEIPT_DTL@LAMS_PROD C WHERE B.ENTITY_ID=C.RECEIPT_NO AND
RECEIPT_STATUS='A'))AND ALLOCATION_STATUS='A')*/

when i am trying to run this query i am getting an error'single row subquery returns more than one row'.

View 2 Replies View Related

SQL & PL/SQL :: Single-row Subquery Returns More Than One Row

Jul 30, 2010

My problem : I Wrote a package(a function), this function just return a varchar2 , but in this function I wrote a cursor , when I call this function in this manner :

SELECT secu.u_menu_id,
secu.menu_id,
menu.prompt,
secu.u_type,
secu.u_id,
[code]....

SQL return : Ora-01427 Single-row subquery returns more than one row

Yes , in this function it has cursor that more than one row. But I let this function return varchar2 ,

View 13 Replies View Related

SQL & PL/SQL :: Single Row Subquery Returns More Than One Row?

Apr 28, 2012

I have written the below sql select loc,(select ename from emp where emp.deptno = dept.deptno) from dept

It results in the below error.

[Error] Execution (1: 13): ORA-01427: single-row subquery returns more than one row

I have modified the SQL and got the required output
select em.ename,dep.loc
from
(select loc,deptno from dept) dep ,
(select ename,deptno from emp) em
where dep.deptno=em.deptno(+)

I have written the below sql to fetch all loc for emp which got executed

SELECT ENAME, (SELECT LOC FROM DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO) LOC
FROM EMP

But as i need all locations irrespective of any emp in the locaton so i tried to put emp side (+) which resulted in error.

View 3 Replies View Related







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