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


ADVERTISEMENT

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

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

Forms :: ORA-01403 Value Will Be Sitting According To Post Trigger Of Previous Text Field

Jul 26, 2010

I have sort button that have the following code

go_block('TEL_OTHER_INFO');
set_block_property('TEL_OTHER_INFO',order_by,:system.cursor_item);
execute_query;

I have in the same form display item that its value will be sitting according to the post trigger of the previous text field when i press the sort button i get frm-40735 post-text-item unhandled exception Ora-01403 error.

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

SQL & PL/SQL :: Query For Previous Day And Day Before

Jan 15, 2013

The main condition in SQL is like this.

SELECT TO_DATE (TO_CHAR (doc_date, 'MON-YY'), 'MON-YY') "INV_MTH",
SUM (inv_amt) INV_TOTAL
FROM table_x
WHERE doc_date BETWEEN TRUNC (SYSDATE, 'YYYY')
AND LAST_DAY (
ADD_MONTHS (TRUNC (SYSDATE, 'YYYY'), 11)
);

My Output from if run in JAN as of now 16 Jan.

INV_MTHINV_TOTAL
Jan-136260830.42

I want an sql until previous day of that month for example 15 Jan and another sql until day before previous day of that month for example 14 Jan.

View 15 Replies View Related

PL/SQL :: Compare A Row Of A Table With Its Previous Row / Rows In A Query

Oct 1, 2013

create table a(sourcerow number(2),  test_level number(2),  dpn varchar2(1),  qty number(5)); T

he insert scripts are as follows:  

insert into a  values(1,3,'Y',5); insert into a values(2,2,'Y',4);  insert into a values(3,3,'N',3); insert into a  values(4,4,'Y',3);  insert into a  values(5,1,'N',6);  insert into a values(6,2,'N',5);  insert into a  values(7,2,'Y',4);  insert into a  values(8,3,'N',2);  insert into a values(9,4,'Y',2);  insert into a  values(10,1,'Y',3); .  SQL>select * from v$version;  Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production 

My logic should have the output as follows without 5th column:  sourcerowleveldpnqtyremark13Y522Y4This row in main table shouldn't be displayed in the query result as it has

dpn='N'44Y3*3=9In this row qty=9 will bedisplayed.

It will compare the value of level column with its previous row level col,if it is the parent of the current(ex.current is 4 and previous is 3 parent of 4),then it will check the dpn of previous row ,if dpn='N' then qty of parent will be multiplied with qty of current row and displayed under qty column.this row will not be displayed as dpn='N'this row will not be displayed as dpn='N'72Y4*6=24in its previous row level value is same so it will check the previous to previous row  where level is 1(parent of current row) and dpn='N' ,then it will multiply the qty of that row with current row and display the value in qty column.this row will not be displayed as dpn='N'94Y2*2=4In this row qty=4 will be displayed.It will compare the value of level column with its previous row level col,if it is the parent of the current(ex.current is 4 and previous is 3 parent of 4),then it will check the dpn of previous row ,if dpn='N' then qty of parent will be multiplied with qty of current row and displayed under qty column.101Y3It will not check for the previous rows as level 1 doesn't have any parent. 

View 5 Replies View Related

SQL & PL/SQL :: Query To Return Results From Three Previous Non-consecutive Days?

Dec 10, 2011

I have a need to query a real time production database to return a set of results that spans a three day period. When the three days are consecutive it's easy but sometimes there is a 1 or two day gap between the days. For example I'm querying results from a group of people that work between Tuesday and Saturday. On a Wednesday I need t produce a set of results that spans Tuesday of the current week, and Saturday and Friday of the previous week; on Thursday I need to produce a set of results that that spans Wednesday and Tuesday of the current week and Saturday of the previous week.I'm using SQL Developer to execute the code.

View 7 Replies View Related

TimesTen In-Memory :: Query To Retrieve Previous Day Data

Jun 13, 2012

I would like to have a query which should fetch previous day records from column which is having timestamp data type.

select mdn from user_table where updatetimestamp > trunc(sysdate) - INTErVAL '24' HOUR;

But this gives output not for previous day, but all records which are 24 hrs less than current day. How to get records for previous day based on column having timestamp data type.

View 4 Replies View Related

SQL & PL/SQL :: Union Query Need To Add Extra Dummy Field To One Side Of Query

Dec 8, 2005

I have inherited a query that union alls 2 select statements, I added a further field to one of the select statements ( a date field). However I need to add another dummy field to the 2nd select statement so the union query marries up I have tried to do this by simply adding a

select
'date_on'
to add a field called date on populated by 'date_on' (the name of the column in the first query)

however when I run the union query i get the error Ora-01790 expression must have same datatype as corresponding expression.

View 6 Replies View Related

SQL & PL/SQL :: Past 13 Month Ends

Aug 13, 2010

Past 13 MonthEnds from a input date

(i.e.: 6/13/2010 is supplied, then query should be able to retrieve 5/31/2010, 4/30/2010, 3/31/2010, 2/26/2010, 1/29/2010, 12/31/2009?.....5/29/2009)

If the month end date falls on a weekend (Sat or Sun), then it should have the prior Friday's date.

View 18 Replies View Related

SQL & PL/SQL :: Month Ends Not Coming Correct?

Sep 7, 2011

I am using following query to get last 3 month ends. Here If month end falls on weekend(sat/sun) then it should have prior date

Where
COB- Table name
COB_DTE_ID_C- Date in numeric format (YYYYMMDD)
COB_DAY_N- Days
COB_MO_C- Month Number

I am not getting how to modify below query without much case when statements so that if I pass monthend date (20110531), It should give me

20110228
20110331
20110429

Its giving me

20110331
20110429
20110530

select * from (
select DISTINCT MAX (COB_DTE_ID_C) OVER (PARTITION BY COB_MO_C)
from COB
where COB_DTE_ID_C < 20110531
and COB_DTE_ID_C > to_char(add_months(to_date(20110531,'YYYYMMDD'),-3),'YYYYMMDD')
and upper(COB_DAY_N) NOT IN ('SATURDAY','SUNDAY')
ORDER BY 1
) where rownum < 4

View 13 Replies View Related

Output Should Starts As Between?

Jun 13, 2013

percentage_pointsmarksage95336.590346.575376.550496.595347903777549750337 if i fired the statementselect percentage_points from table1 where marks=33 and age=6.5 then output should beuser has score  percentage between 90 and 95i can write case condition between 1 to 100 percentages but any other condition which easy 

View 1 Replies View Related

PL/SQL :: ORA-03127 - No New Operations Allowed Until Active Ends?

May 15, 2013

I'm a software developer, not an Oracle DBA

Our product runs a lot of stored procedures in the background to do various things. These stored procedures obviously include a ton of select statements, insert statements, etc. Some of them get pretty complex. Once in a while, we run across the following error: "ORA-03127: no new operations allowed until the active operation ends(3127)." Once this happens, pretty much everything breaks with this error for a while. Eventually (LOOOONG time), this error "resolves itself" and things start working again. Conceptually, I understand that there seems to be some blocking operation on the DB, but because we run a LOT of stored procedures and SQL statements, it's extremely difficult to pin this down.

View 7 Replies View Related

SQL & PL/SQL :: How To Read Field From Query In Another Query

Sep 19, 2010

I am having a Select query(below Query1) and I want to use one column(sum(col4)) from this Select query to be displayed in another Select query(Query 2). how to display this.

Query 1 :-
select a.col1,a.col2,b.col3,sum(b.col4)
from tab a, tab b
where a.key1=b.key1 and a.key2=b.key2
group by a.col1,a.col2,b.col3

Query 2 :-
select a.col1,a.col2,b.col3,sum(b.col6)
from tab a, tab b
where a.key1=b.key1 and a.key2=b.key2
group by a.col1,a.col2,b.col3,b.col5

View 4 Replies View Related

SQL & PL/SQL :: Date Format Picture Ends Before Converting Entire Input String

Jun 29, 2011

I am trying to insert a row in a table and getting the below error.

SQL> insert into tbl_force_charging(ANI, date_time, durations,src, circleid)
2 values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR'
)
3 ;
values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR')
*
ERROR at line 2: ORA-01830: date format picture ends before converting entire input string

Table Structure is

Name Null? Type
----------------------------------------- -------- ---------------------------
ANI VARCHAR2(10)
DATE_TIME DATE
DURATIONS VARCHAR2(10)
SRC VARCHAR2(10)
CIRCLEID VARCHAR2(10)
SQL>

View 11 Replies View Related

Reports & Discoverer :: Week Day Starts From Monday

Feb 20, 2012

I have m display financial year calender(April-2011 to March 2012) and i want to display monday as a first week day.

View 3 Replies View Related

Security :: Encryption Method (password Starts With 06)?

Aug 29, 2013

Our Audit Company has given us a recommendation:"Old DB Link encrypted Passwords: The password of the Oracle databases links are encrypted using DES (password starts with 05). This encryption methord is known and users can decrypt the passwords using a simple SQL query. Please recreate the database links to use the new encryption method (password starts with 06)."What does it mean and how can we perform this recommendation?

View 2 Replies View Related

SQL & PL/SQL :: ORA-01830 / Date Format Picture Ends Before Converting Entire Input String

Feb 26, 2013

when i run this query i am facing date format error.

select sbrueregister.UEIMSI,sbrueregister.fapid,sbrfapslid.slid,sbrfapslid.ACTIVATION_TS,sbrfapslid.DEACTIVATION_TS from SBRFAPSLID INNER JOIN sbrueregister ON sbrfapslid.fapid=sbrueregister.fapid where sbrfapslid.slid='1234567890' and sbrueregister.registeredat between TO_DATE('2013-02-1.12.0. 10. 123000000','YYYY-MM-DD HH.MI.SS.SSSSSS')and TO_DATE('2013-02-1.12.9.10.123000000', 'YYYY-MM-DD HH.MI.SS.SSSSSS');

ORA-01830: date format picture ends before converting entire input string

View 1 Replies View Related

SQL & PL/SQL :: JSP Limitations - Date Format Picture Ends Before Converting Entire Input String

Feb 21, 2010

I am using

SELECT TO_CHAR(TO_DATE((:BILL_VALUE),'J'),'Jsp') FROM sys.dual;

its givinig right result for

1023411->One Million Twenty-Three Thousand Four Hundred Eleven

but if i will change it to 10234111 then this query giving an error

ORA-01830: date format picture ends before converting entire input string.

Is there any limitation or is there any other built in function to get the number to a word.

View 2 Replies View Related

Server Administration :: Foreground Process Starts Before PMON

Feb 7, 2012

Details of my site is as below:

database version: 11.2.0.1.0
OS version:
Microsoft Windows Server 2003
Standard Edition
Service Pack 2

the day before yesterday when my database bounce i m facing with the following error.

ksuapc : ORA-1033 foreground process starts before PMON

View 2 Replies View Related

SQL & PL/SQL :: Updated Values For Column Starts With 1000 Onwards

Jun 9, 2010

I am having one table with 4 columns where some 1500 records are there. Now I created one new column with number data type and i want value to be updated for that column starts with 1000 onwards.

View 4 Replies View Related

PL/SQL :: Query To Split Field Value

Jul 31, 2013

In employees table I am having employee_name column data as follows 

Aaron, Mrs. Jamie (Jamie)Aaron, Mrs. Jenette (Jenette)Abbott, Ms. Rachel (Rachel) Breton, Mr. Jean  Britz, Mrs. Sarie (Sarie) --> Now, I want to display the employee name like "Mrs. Jamie" (with out Surname and with out bracket included text),-->

How to achieve this by SQL query.

View 3 Replies View Related

PL/SQL :: Data File Which Starts With T As Trailer To Be Skipped While Loading Into Table Using SQL LOADER?

Sep 11, 2012

I have following in the data file

572ACTS ERD LLC SUE CCCC
T R 1010

I want to skip the last line of the data file which starts with 'T' as trailer to be skipped while loading into table using SQL LOADER?

View 2 Replies View Related

Forms :: Query Not In Database Field

Nov 27, 2010

i have a single form three database field

i want when i open a form in exceute mode then only a single field it does not show any data from database

means query not allowed in that particular field

how it is posible

View 1 Replies View Related

Forms :: Trigger To Check Field Value After Query?

Mar 8, 2010

What is the trigger which should be used to check certain field value after posting the query.

Example:

I have executed the query and the records are fetched. There is one field I want to check if it is null then it should be enabled, else, keep it disabled.

View 2 Replies View Related

SQL & PL/SQL :: Build Query To Retrieve Records Between Dates With Same Field?

Mar 17, 2010

i have a doubt in building a query.

I have a table with fields

job_no activity Date
101 anchorage 20/01/2010
102 berthing 25/01/2010
103 sailing 29/01/2010

If i want to know the status of the ship on the date '22/01/2010' It has to show as 'anchorage', becoz on '25/01/2010' only it came to berthing from anchorage. How to write a query to achieve this.

View 12 Replies View Related







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