Home » SQL & PL/SQL » SQL & PL/SQL » Help Writing a Complex 'SELECT' Query (Oracle 11, Windows)
 Help Writing a Complex 'SELECT' Query [message #607559] |
Sun, 09 February 2014 14:12  |
johnjacobt
Messages: 11
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 #607563 is a reply to message #607561] |
Sun, 09 February 2014 21:24  |
johnjacobt
Messages: 11
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 |
johnjacobt
Messages: 11
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 |
johnjacobt
Messages: 11
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 |
John Watson
Messages: 8931
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> |
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 17:38:15 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
