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')
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
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
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.
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
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.
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
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
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.
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
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.
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>
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�)
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.
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.
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.
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.
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
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?
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.
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.
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
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,
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.
