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