?
Solved

Selecting multiple rows which meet condition  oracle 9 sql

Posted on 2008-11-03
3
Medium Priority
?
447 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1600 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

TCP/IP Network Protocol Cheat Sheet

TCP/IP is a set of network protocols which is best known for connecting the machines that make up the Internet. The truth is that TCP/IP is one of the oldest network protocols and its survival is mainly based on its simplicity and universality.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

762 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