Solved

Selecting multiple rows which meet condition  oracle 9 sql

Posted on 2008-11-03
3
443 Views
Last Modified: 2013-12-19
Please see the attached which displays a) the current output and b) the desired output.

The data is looking at a specific question and answers given to the question in an electronic form for three different clients. This particular question is looking at the ethnicity recorded for a client. Each answer is recorded in 4 pieces of data:

CAT_DESC
START_DATE
END_DATE
NOTES

Each answer may have multiple instances of the above data (see client_id 202579), but the start and end dates of the categories cannot overlap.

Id like the output to only list open categories and the associated start date and notes, i.e. where there is no end date. Any help with this is appreciated.
output-example.xls
0
Comment
Question by:tonMachine100
  • 2
3 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 22867785

Try something like this:

Select * From Mytable A
 Where Exists (
 Select Null From Mytable B
  Where B.Qst_Type              = A.Qst_Type
    And B.Qst_Code              = A.Qst_Code
    And B.Qst_Id                = A.Qst_Id
    And B.Ans_Qst_Code          = A.Ans_Qst_Code
    And B.Client_Id             = A.Client_Id
    And B.Ans_Id                = A.Ans_Id
    And B.Avd_Seq               = A.Avd_Seq 
    And B.Anv_Name              = 'End_Date'
    And B.Answer Is Null        = A.Answer Is Null
/

Open in new window

0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 400 total points
ID: 22868006
Ooops, typo:

Select * From Mytable A
 Where Exists (
 Select Null From Mytable B
  Where B.Qst_Type              = A.Qst_Type
    And B.Qst_Code              = A.Qst_Code
    And B.Qst_Id                = A.Qst_Id
    And B.Ans_Qst_Code          = A.Ans_Qst_Code
    And B.Client_Id             = A.Client_Id
    And B.Ans_Id                = A.Ans_Id
    And B.Avd_Seq               = A.Avd_Seq 
    And B.Anv_Name              = 'END_DATE'
    And B.Answer Is Null
/

Open in new window

0
 

Author Closing Comment

by:tonMachine100
ID: 31512642
thats spot on. thanks for your help!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question