Solved

Selecting multiple rows which meet condition  oracle 9 sql

Posted on 2008-11-03
3
441 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now