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
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
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
May 29, 2013
I suspect it uses TABLE, CAST and/or MULTISET functions in a query .. but reading the docs on those, I can't seem to piece together how to do it.
When I piece together a sample/test query, I usually do something like this:
WITH data AS
( SELECT 123 id, 'abc' col1 FROM dual UNION ALL
SELECT 234, 'def' FROM dual UNION ALL
SELECT 345, 'xyz' FROM dual
)
SELECT *
FROM data -- usually more complex query here
However, I saw somebody do this without using the UNION ALL .. they did something with TABLE ( ... ) ? it seemed "simpler" than the UNION ALL method I've used previously
View 10 Replies
View Related
Feb 16, 2013
While executing parallel query we are getting the below errors.
(SQLState = HY010) - java.sql.SQLException: [tibcosoftwareinc][Oracle JDBC Driver][Oracle]ORA-01002: fetch out of sequence"
(SQLState = HY000) - java.sql.SQLException: [tibcosoftwareinc][Oracle JDBC Driver][Oracle]ORA-12842: Cursor invalidated during parallel execution
Database Version: Release 11.2.0.1.0 (Non-RAC)
SQL Query:
SELECT /*+ PARALLEL 4 */
DISTINCT
AI.STORE_ID STORE_ID, AI.DUE_DATE DUE_DATE, AI.INSTRUCTION_ID INSTRUCTION_ID,
DECODE (ASR.ADT_INSTR_KEY, NULL, 'F', ASR.ADT_INSTR_KEY, 'S') QCCHECK
FROM NSOAPP.DA_INSTRUCTION_INFO AI,
[code]....
View 4 Replies
View Related
Feb 18, 2013
I am to trying to fetch session id of a previously submitted process of a search button......so that i can display the search results in a different page.....so is there any sql query or pl/sql procedure to fetch the session id.
View 1 Replies
View Related
Jul 26, 2013
I am trying to write a proper query to fetch data from database. Scenario:
I need to retrieve employees who are not working in multiple departments. scott@TESTCRM> select * from emp1;
EMPNO DEPTNO
---------- ----------
7654 30 7698 30 7788 20 7788 30 7876 20 7900 10 7900 30 7902 20 7934 10
scott@TESTCRM>
Ouput Expected is
EMPNO DEPTNO
---------- ----------
7654 30 7698 30 7876 20 7902 20 7934 10
View 9 Replies
View Related
Mar 10, 2012
Main Aim : To find all those id's who have taken all the tests within a rolling window of 45 days.
I have a table "MBS_FIRST_DATE" with the following data :This table has the patients who have the test along with the first date..This table is derived such that it has only one record with the first date of the test irrespective of the test.
create table MBS_FIRST_DATE
(
medical_record_number VARCHAR2(600),
requested_test_name VARCHAR2(39),
result_date DATE
[code]..
Process :will be explaining with a patient id :
1) Consider the patient 1001274 from mbs_first_date table.
2) This patient has an date of July 08th 2008 & test SBP from first table. (keep this test an an anchor).
3) For the patient above loop through the all_recs table with test & result date ordered for the patient. (excluding SBP)
4) The first record we have is CHL with 08/05/2009 (May 8th 2009)..
5) Since this record is not within 45 days from SBP date for the patient..we go to the next record of SBP for the patient.
6) The next record for SBP is 11/05/2009 (May 11th 2009) .
7) Consider the CHL date again which is with 08/05/2009 (May 8th 2009)..
Since both are within 45 days ..store both the values keeping SBP date as an anchor date as it's the test that's having minimum date from table 1. Even though there is one more CHL date which is within 45 days from SBP we don't care about it.
9) Go to the next test for the same patient which is DBP..
10) The DBP first date is July 08th 2008..
11) Since it's not within 45 days from previously stored SBP date (11/05/2009) ignore the record.
12) GO to the next record which is 10/05/2009..as this is within 45 days from SBP & already CHL (stored date) is within 45 days..Grab all the 3 dates as all are within 45 days from anchor date (SBP date).
SO the o/p will be
1001274 SBP 11/05/2009
1001274 CHL 08/05/2009
1001274 DBP 10/05/2009
Code which I wrote :I know some where I am missing the loop
declare
V_ID1 VARCHAR2(200) := '';
V_TEST1 VARCHAR2(200) := '';
V_DATE1 DATE := NULL;
[code]...
View 2 Replies
View Related
Mar 15, 2011
I have 3 tables, Emp(Emp_id,emp_name),dept(dept_no,dept_name),emp_dept(emp_id,dept_no). Emp tabl ehas some 20 employes id who belongs to different departments.There are few employee who belongs to multiple departments as well. I want to fetch records of emp_id, emp_name, dept_no in the following format.
Name id dept_no
Ram 101 10
20
30
Ani 201 10
20
View 1 Replies
View Related
May 25, 2013
Lets say I have a table in ORACLE database like:
ACC_ID | ACC_AMT
111 | 10000
111 | 12000
111 | 14000
222 | 25000
222 | 30000
333 | 18000
333 | 27000
333 | 13000
333 | 15000
I want to get the output as:
ACC_ID_1 | ACC_AMT_1 | ACC_ID_2 | ACC_AMT_2 | ACC_ID_3 | ACC_AMT_3
111 | 10000 | 222 | 25000 | 333 | 18000
111 | 12000 | 222 | 30000 | 333 | 27000
111 | 14000 | null | null | 333 | 13000
null | null | null | null | 333 | 15000
I need each different ACC_ID with ACC_AMT in different columns. The table may have other different ACC_ID also, but I will fetch only what I need. What is the best way to do this?
So far I have tried this:
SELECT
(CASE WHEN ACC_ID=111 THEN ACC_ID END) AS ACC_ID_1,
(CASE WHEN ACC_ID=111 THEN ACC_AMT END) AS ACC_AMT_1,
(CASE WHEN ACC_ID=222 THEN ACC_ID END) AS ACC_ID_2,
(CASE WHEN ACC_ID=222 THEN ACC_AMT END) AS ACC_AMT_2,
(CASE WHEN ACC_ID=333 THEN ACC_ID END) AS ACC_ID_3,
(CASE WHEN ACC_ID=333 THEN ACC_AMT END) AS ACC_AMT_3
FROM <TABLE_NAME>
But I am not getting the desired result.
View 22 Replies
View Related
Sep 28, 2011
I got a issue with a query to fetch records between two dates for fixed timings
Date
From 29-09-2011 to 04-10-2011
Time
From 00:00:00hrs to 08:00:00hrs
I tried the below queries, it doesnt work
select a.detectorid,sum(b.totalvolume),a.updatetime,a.averagespeed from traffic_data a left outer join volume_data b on a.traffic_id=b.traffic_data_id
where pollinterval=1 and detectorid=�AIDC_0154� and updatetime between to_date(�29-aug-2011:00:00:00�,�DD-MON-YYYY:HH24:MI:SS�)
[code]...
View 1 Replies
View Related
Sep 16, 2012
I need to fech parent records only when no child record with status 'N' exists. There are only two possible values for status column of child table 'Y' / 'N'.
Below are table structures and insert statements for data.
CREATE TABLE MASTER
(
COL1 NUMBER,
[Code]....
COMMIT;Query I framed is below
select * from master where exists (select null from child where child.col2 = master.col1
group by child.col2 having count(distinct col3) =1 )
Output in above case would be 3 as for 1 there's one record with status as 'N' and for 2 there's no child record. I am on 10g.
View 2 Replies
View Related
Jan 9, 2013
I'm trying to setup a dev machine to run APEX with RESTful services ahead of using Oracle's Cloud Database. Using Personal Edition, APEX 4.2.1 and the 2.0 Listener is up and running in standalone more. The problem is when hitting 'Test' on a selected REST Resource Handler, it comes up with an APEX 404 not found error, such as on the example HR REST handlers and a new test one setup.
I've followed all of the APEX and Listener instructions, run apex_rest_config.sql, ensured the various users are unlocked and tried both http and https (even though I've added the no SSL flag in the Listener config per standalone instructions.
View 3 Replies
View Related
Jun 2, 2010
There are several stages for sql processing in 10g2 database concept document.The following stages are necessary for each type of statement processing:
■ Stage 1: Create a Cursor
■ Stage 2: Parse the Statement
■ Stage 5: Bind Any Variables
■ Stage 7: Run the Statement
■ Stage 9: Close the Cursor
Optionally, you can include another stage:
■ Stage 6: Parallelize the Statement
Queries (SELECTs) require several additional stages, as shown in Figure 241:
■ Stage 3: Describe Results of a Query
■ Stage 4: Define Output of a Query
■ Stage 8: Fetch Rows of a Query
Stage 3: Describe Results of a Query The describe stage is necessary only if the characteristics of a query's result are not known; for example, when a query is entered interactively by a user. In this case, the describe stage determines the characteristics (datatypes, lengths, and names) of a query's result.
Stage 4: Define Output of a Query In the define stage for queries, you specify the location, size, and datatype of variables defined to receive each fetched value. These variables are called define variables. Oracle performs datatype conversion if necessary.
I still don't understand what's Stage 3: Describe Results of a Query and Stage 4: Define Output of a Query.
View 2 Replies
View Related
Feb 27, 2013
creating Oracle SQL query to fetch the information using PIVOT option.We are populating audit table using triggers. For every update, there will be two rows into audit table, one row with all OLD values and another with all NEW values. Also every updated is uniquely identified by Sequence No. Example for phone audit is mentioned below :
CREATE TABLE test_audit_phone
(
emplid VARCHAR2(10),
seqno NUMBER,
action VARCHAR2(3),
office NUMBER,
mobile NUMBER
);
Insert some rows into table.
INSERT INTO test_audit_phone VALUES ('100',1,'OLD',1111,9999)
/
INSERT INTO test_audit_phone VALUES ('100',1,'NEW',2222,9999)
/
INSERT INTO test_audit_phone VALUES ('100',2,'OLD',2222,9999)
/
INSERT INTO test_audit_phone VALUES ('100',2,'NEW',2222,8888)
/
Table will look like the following :
SQL> SELECT * FROM sysadm.test_audit_phone ;
EMPLID SEQNO ACT OFFICE MOBILE
---------- ---------- --- ---------- ----------
100 1 OLD 1111 9999
100 1 NEW 2222 9999
100 2 OLD 2222 9999
100 2 NEW 2222 8888
Now we have to present data in different format. For each field, display OLD and NEW values in column format.
EMPLIDFIELDOLDNEW
----- ------ ---- -----
100OFFICE11112222
100MOBILE99998888
Challenges :
1) Make pivoting with old and new values
2) For each field we have to show old and new values
3)if old and new values are same, dont show in report.
View 8 Replies
View Related
Apr 26, 2011
I want to export all db using data pump. I got this error when using it:
Export: Release 10.2.0.4.0 - Production on Thu Nov 25 11:46:48 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export the entire database ...
. exporting tablespace definitions
EXP-00068: tablespace TISDATA is offline
EXP-00008: ORACLE error 1187 encountered
ORA-01187: cannot read from file 203 because it failed verification tests
ORA-01110: data file 203: '/u03/app/oracle/product/10.2.0/oradata/unidev/TISTEMP01.dbf'
EXP-00000: Export terminated unsuccessful
View 2 Replies
View Related
Jul 22, 2013
I have an application connected to Oracle 11g that sends its own querys to the db based on what the user is clickng on. The applicaiton is connected via one user id and I was wondering, is there a way that I can capture the tiem each query starts, the sql itself, and the amount of time it took to fetch the data?
View 7 Replies
View Related
Mar 24, 2011
FRM-40501: ORACLE error: unable to reserve record for update or delete.
ORA-24374: define not done before fetch or execute and fetch
My master-detail form has single canvas. For both blocks, master and detail, two tables joined together in each. One table to be updated, second table has some info for reference (query only).
I am getting these errors when in detail block the item from LOV is selected for existing record. This does not happen for new record inserted in detail block.
View 1 Replies
View Related
Nov 1, 2012
create or replace PROCEDURE newprocedur(outname OUT VARCHAR2,outroll OUT NUMBER) AS
CURSOR c1 IS
select Name,Rollno,Section from emp;
BEGIN
Open c1;
fetch c1 into outname,outroll;
Here out of 3 columns in cursor is it possible to fetch only two columns using FETCH like i did above?
View 1 Replies
View Related
Mar 16, 2011
Now, I am trying to come with an alternative. My first thought was to use materialized views with Fast Refresh - refresh only changed rows. This might not even work because there are many contraints when using Fast refresh. I am in the process of learning their data model so I do not know much.
Have you ever encountered a problem like this?
View 7 Replies
View Related
Nov 8, 2012
Please explain :
What is the difference between : User calls and executes in
AWR Load Profile :
User calls: 1,373.51 0.80
Executes: 8,558.06 4.98
As i understand , than User calls is : the number of DML (insert , update, Delete and Select ) cursor : parse,execute , fetch , and execute is ?
Why executes > User calls ?
Or may be one pl/sql package procedure may execute many SQL ? How to explain this ?
View 2 Replies
View Related
Mar 24, 2011
My Oracle DB 10g R2 running on AIX 5.3 OS. When I am shutting down the oracle I see that it is waiting for complete the active calls and I determined that these active calls/processes are from remote machines.
When I try to run this ps -ef as shown below, it shown me number of oracle process with LOCAL=NO
/home/oracle> ps -ef | grep 91334
oracle 85210 87320 0 12:47:56 pts/35 0:00 grep 91334
oracle 91334 1 0 12:45:11 - 0:00 oracleIDB (LOCAL=NO)
What are the possible scenarios for this LOCAL=NO? How do I find that this process is from which machine or IP address?
View 2 Replies
View Related
Jul 23, 2013
I have a function that is being called three time using UNION and wanted to know if this can be improved to just one call while incorporating all the table joins.
select field1,fdate,fname,username,stepnum from (
SELECT M.FIELD1,
TO_CHAR (M.FIELD_DATE, 'MM/DD/YYYY HH24MISS') AS FDATE,
M.FIELDNAME AS FNAME,
M.USERNAME,
View 10 Replies
View Related
Jul 19, 2013
create table call(id number(15) primary key, calling_no varchar2(15), called_no varchar2(15), calldate date, calltime timestamp, duration number(10) ,calltype varchar2(10)); 1. CALL DETAILS OF 9891826547insert into call (id,calling_no,called_no,calldate,calltime,duration,calltype) values (1, 9891826547, 9891210785, to_date('01-Jun-13','dd-Mon-yy'), to_date('19:12:00','hh24:mi:ss'),10,'OUT'); insert into call (id,calling_no,called_no,calldate,calltime,duration,calltype) values (2,9891826547, 9899985476, to_date('01-Jun-13','dd-Mon-yy'), to_date('22:10:12','hh24:mi:ss'),50,'OUT'); insert into call (id,calling_no,called_no,calldate,calltime,duration,calltype) values (3, 9891826547, 9818415767, to_date('02-Jun-13','dd-
[code]...
View 3 Replies
View Related
Oct 3, 2012
The steps for the subject line mentioned. and pre-reqs or post-checks if any.
View 3 Replies
View Related
Apr 27, 2010
I have a requirement to calculate the maximum number of concurrent calls from the following data:
Create_date connect_date_time disconnect_date_time duration ...
12/01/10 13:20:26 1263253551 1263254153 602
...
I have attempted to use the analytic function to keep a running total of the count of active calls based on the connect and disconnect times given for each record row.
e.g.
SELECT
count(*) calls,
avg(duration)/60 average_duration_mins,
max(duration)/60 max_duration_mins,
sum(duration)/60 total_mins,
(SUM(DURATION)/60)*0.04 total_cost_4c_per_min
[code]....
View 7 Replies
View Related
