Home » SQL & PL/SQL » SQL & PL/SQL » Help Writing a Complex 'SELECT' Query (Oracle 11, Windows)
icon5.gif  Help Writing a Complex 'SELECT' Query [message #607559] Sun, 09 February 2014 14:12 Go to next message
johnjacobt
Messages: 9
Registered: July 2010
Location: India
Junior Member
Hi There,

I'm trying to write a query that fetches information about attachments in HP ALM by referring TestInstances, TestRun and TestSteps tables. For those who are not familiar with HP ALM...
I'm describing the data structure of these tables (table and field names are changed for easy understanding).

>>1. Key fields in 'TestInstances' table are I_InstanceID, I_HasAttachments and I_TestSetID
1.1 I_InstanceID is the primary key in this table

>>2. Key fields in 'TestRuns' table are R_RunID, R_InstanceID, R_RunTime and R_HasAttachments
2.1 R_RunID is the primary key in this table
2.2 R_InstanceID is a foreign key to I_InstanceID in TestInstances table
2.3 All I_InstanceID in TestInstances table need not necessarily have an entry in TestRuns table
2.4 One I_InstanceID in TestInstances may have multiple entries in TestRuns table with different R_RunID

>>3. Key fields in 'TestSteps' table are S_StepID, S_RunID and S_HasAttachments
3.1 S_StepID is the primary key in this table
3.2 S_RunID is a foreign key to R_RunID in TestRuns table
3.3 All R_RunID in TestRuns table need not necessarily have an entry in TestSteps table
3.4 One R_RunID in TestRuns may have multiple entries in TestSteps table with different S_StepID

Input to the query I want to write is = a set of I_TestSetID in TestInstances table (which I already have)
Output from the desired query is -
1. All I_InstanceID having I_TestSetID values as given in Input
2. I_HasAttachments corresponding to I_InstanceID
3. Count of R_RunID against each I_InstanceID (Maybe 0 or positive integer)
4. Only latest R_RunID corresponding to each I_InstanceID (latest can be found using MAX(R_RunID) GROUP BY I_InstanceID)
5. R_HasAttachment value for latest R_RunID
6. R_RunTime value for latest R_RunID
7. Count of S_StepID against each R_RunID (Maybe 0 or positive integer)
8. Count of S_HasAttachment against each R_RunID (Maybe 0 or positive integer and can differ from Count of S_StepID)

Friends, Could one of you give it a try and help me out?
Thanks in advance!


P.S: This one had been driving me crazy for 3 days.

Re: Help Writing a Complex 'SELECT' Query [message #607560 is a reply to message #607559] Sun, 09 February 2014 14:50 Go to previous messageGo to next message
Michel Cadot
Messages: 58641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
P.S: This one had been driving me crazy for 3 days.


And you expect us to solve it with no table and data?
Please have a look at your previous topic.

Re: Help Writing a Complex 'SELECT' Query [message #607561 is a reply to message #607560] Sun, 09 February 2014 15:18 Go to previous messageGo to next message
BlackSwan
Messages: 22554
Registered: January 2009
Senior Member
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Help Writing a Complex 'SELECT' Query [message #607563 is a reply to message #607561] Sun, 09 February 2014 21:24 Go to previous messageGo to next message
johnjacobt
Messages: 9
Registered: July 2010
Location: India
Junior Member
DDL for creating tables:

CREATE TABLE TestInstances (
I_InstanceID INT NOT NULL,
PRIMARY KEY (I_InstanceID),
I_TestSetID INT NOT NULL,
I_HasAttachments VARCHAR2(2)
);

CREATE TABLE TestRuns (
R_RunID INT NOT NULL,
PRIMARY KEY (R_RunID),
R_InstanceID INT NOT NULL,
FOREIGN KEY (R_InstanceID)
REFERENCES TestInstances(I_InstanceID),
R_RunTime DATE DEFAULT (sysdate),
I_HasAttachments VARCHAR2(2)

);

CREATE TABLE TestSteps (
S_StepID INT NOT NULL,
PRIMARY KEY (S_StepID),
S_RunID INT,
FOREIGN KEY (S_RunID)
REFERENCES TestRuns(R_RunID),
S_HasAttachments VARCHAR2(2)
);

Note: I'm not familiar with DDL,DML and I don't have a test database where I can check the above. If there are any mistakes, please point out or attempt to correct it.

@Michel Cadot, You did a great job pointing me to my previous topic. It was good reading that old topic posted more than 3 years ago.
And I understand by now that the DDLs and DMLs will help you help me. Thanks!

Update: Will soon post the DMLs too.

[Updated on: Sun, 09 February 2014 21:27]

Report message to a moderator

Re: Help Writing a Complex 'SELECT' Query [message #607564 is a reply to message #607563] Sun, 09 February 2014 21:50 Go to previous messageGo to next message
johnjacobt
Messages: 9
Registered: July 2010
Location: India
Junior Member
DML for Inserting Data:

INSERT INTO TestInstances (I_InstanceID, I_TestSetID, I_HasAttachments) VALUES (2210, 1190, NULL);
INSERT INTO TestInstances (I_InstanceID, I_TestSetID, I_HasAttachments) VALUES (2211, 1190, NULL);
INSERT INTO TestInstances (I_InstanceID, I_TestSetID, I_HasAttachments) VALUES (2212, 1191, NULL);
INSERT INTO TestInstances (I_InstanceID, I_TestSetID, I_HasAttachments) VALUES (2213, 1192, 'N');
INSERT INTO TestInstances (I_InstanceID, I_TestSetID, I_HasAttachments) VALUES (2214, 1193, 'Y');
INSERT INTO TestInstances (I_InstanceID, I_TestSetID, I_HasAttachments) VALUES (2215, 1194, 'Y');
INSERT INTO TestInstances (I_InstanceID, I_TestSetID, I_HasAttachments) VALUES (2216, 1194, NULL);
INSERT INTO TestInstances (I_InstanceID, I_TestSetID, I_HasAttachments) VALUES (2217, 1194, 'N');
INSERT INTO TestInstances (I_InstanceID, I_TestSetID, I_HasAttachments) VALUES (2218, 1195, 'N');
INSERT INTO TestInstances (I_InstanceID, I_TestSetID, I_HasAttachments) VALUES (2219, 1195, 'Y');

INSERT INTO TestRuns (R_RunID, R_InstanceID, R_RunTime, I_HasAttachments) VALUES (3380, 2210, '2014-01-30', NULL);
INSERT INTO TestRuns (R_RunID, R_InstanceID, R_RunTime, I_HasAttachments) VALUES (3381, 2210, '2014-01-30', 'Y');
INSERT INTO TestRuns (R_RunID, R_InstanceID, R_RunTime, I_HasAttachments) VALUES (3382, 2212, '2014-01-29', 'Y');
INSERT INTO TestRuns (R_RunID, R_InstanceID, R_RunTime, I_HasAttachments) VALUES (3383, 2212, '2014-01-31', 'N');
INSERT INTO TestRuns (R_RunID, R_InstanceID, R_RunTime, I_HasAttachments) VALUES (3384, 2216, '2014-01-30', NULL);
INSERT INTO TestRuns (R_RunID, R_InstanceID, R_RunTime, I_HasAttachments) VALUES (3385, 2216, '2014-01-30', NULL);
INSERT INTO TestRuns (R_RunID, R_InstanceID, R_RunTime, I_HasAttachments) VALUES (3386, 2217, '2014-01-30', NULL);
INSERT INTO TestRuns (R_RunID, R_InstanceID, R_RunTime, I_HasAttachments) VALUES (3387, 2218, '2014-01-30', 'N');
INSERT INTO TestRuns (R_RunID, R_InstanceID, R_RunTime, I_HasAttachments) VALUES (3388, 2218, '2014-01-30', NULL);
INSERT INTO TestRuns (R_RunID, R_InstanceID, R_RunTime, I_HasAttachments) VALUES (3389, 2218, '2014-01-30', 'Y');

INSERT INTO TestSteps (S_StepID, S_RunID, S_HasAttachments) VALUES (4470, 3380, 'Y');
INSERT INTO TestSteps (S_StepID, S_RunID, S_HasAttachments) VALUES (4471, 3380, NULL);
INSERT INTO TestSteps (S_StepID, S_RunID, S_HasAttachments) VALUES (4472, 3383, NULL);
INSERT INTO TestSteps (S_StepID, S_RunID, S_HasAttachments) VALUES (4473, 3386, 'Y');
INSERT INTO TestSteps (S_StepID, S_RunID, S_HasAttachments) VALUES (4474, 3386, 'N');
INSERT INTO TestSteps (S_StepID, S_RunID, S_HasAttachments) VALUES (4475, 3386, NULL);
INSERT INTO TestSteps (S_StepID, S_RunID, S_HasAttachments) VALUES (4476, 3389, 'Y');
INSERT INTO TestSteps (S_StepID, S_RunID, S_HasAttachments) VALUES (4477, 3389, 'N');
INSERT INTO TestSteps (S_StepID, S_RunID, S_HasAttachments) VALUES (4478, 3389, NULL);
INSERT INTO TestSteps (S_StepID, S_RunID, S_HasAttachments) VALUES (4479, 3389, NULL);

Update: Will post the desired result next.

[Updated on: Sun, 09 February 2014 21:51]

Report message to a moderator

Re: Help Writing a Complex 'SELECT' Query [message #607565 is a reply to message #607564] Sun, 09 February 2014 22:24 Go to previous messageGo to next message
johnjacobt
Messages: 9
Registered: July 2010
Location: India
Junior Member
Input to the query I want to write is a set of I_TestSetID in TestInstances table that is found by
SELECT * FROM TestInstances WHERE I_TestSetID IN (1190,1191,1192,1194,1195)

Output from the desired query is -
1. All I_InstanceID having I_TestSetID values as given in Input
2. I_HasAttachments corresponding to I_InstanceID
3. Count of R_RunID against each I_InstanceID (Maybe 0 or positive integer)
4. Only latest R_RunID corresponding to each I_InstanceID (latest can be found using MAX(R_RunID) GROUP BY I_InstanceID)
5. R_HasAttachments value for latest R_RunID
6. R_RunTime value for latest R_RunID
7. Count of S_StepID against each R_RunID (Maybe 0 or positive integer)
8. Count of S_HasAttachments against each R_RunID that has S_HasAttachments = 'Y' (Maybe 0 or positive integer and can differ from Count of S_StepID)

Data expected as a result of desired query using data said above:
2210,NULL,2,3381,'Y' ,'2014-01-30',0,0
2211,NULL,0,NULL,NULL,NULL ,0,0
2212,NULL,2,3383,'N' ,'2014-01-31',1,0
2213,'N' ,0,NULL,NULL,NULL ,0,0
2215,'Y' ,0,NULL,NULL,NULL ,0,0
2216,NULL,2,3385,NULL,'2014-02-01',0,0
2217,'N' ,1,3386,NULL,'2014-02-01',3,1
2218,'N' ,3,3389,'Y' ,'2014-02-03',3,0
2219,'Y' ,0,NULL,NULL,NULL ,0,0

I hope the required amount of information is now available. Please help!
Re: Help Writing a Complex 'SELECT' Query [message #607575 is a reply to message #607565] Mon, 10 February 2014 02:13 Go to previous messageGo to next message
John Watson
Messages: 4413
Registered: January 2010
Location: Global Village
Senior Member
Build it up slowly. Here is a start, looking at only your first three projected columns. You'll need to think about outer joins next to sort out the selection, and then more joins and aggregations.
orclz> select i.i_instanceid,i.i_hasattachments,count(r.r_runid) from 
testinstances i join testruns r on (i.i_instanceid=r.r_instanceid) 
group by i.i_instanceid,i.i_hasattachments;

I_INSTANCEID I_ COUNT(R.R_RUNID)
------------ -- ----------------
        2210                   2
        2212                   2
        2216                   2
        2217 N                 1
        2218 N                 3

orclz>
icon14.gif  Re: Help Writing a Complex 'SELECT' Query [message #607675 is a reply to message #607575] Mon, 10 February 2014 23:08 Go to previous message
johnjacobt
Messages: 9
Registered: July 2010
Location: India
Junior Member
Thanks, John.

[Updated on: Mon, 10 February 2014 23:08]

Report message to a moderator

Previous Topic: ORA-4091- Mutating Trigger Error
Next Topic: Error in PL/SQL Procedure using UTL_HTTP
Goto Forum:
  


Current Time: Fri Aug 01 05:10:07 CDT 2014

Total time taken to generate the page: 0.15912 seconds