ORACLE 11g = Switch between two queries in a subquery

I would like to use a subquery to return a column value. How would I construct this query?  From the sample data.
If reference flag = 'Yes'
    select title from lectures where lecture_id = referenced_lecture_Id
else
    select title from lecture where lecture_id = lecture_id

Lectures  Table
Lecture_Id    Title                  Referenced_Flag   Referenced_Lecture_Id
1             Stress Management        Yes               2
2             Stress Managment (CPAP)  No            



The lecture_id and referenced_lecture_id will be passed from the main query.
cookiejarAsked:
Who is Participating?
 
yuchingCommented:
Hi cookiejar, please try below
Here is your subquery

SELECT
  (CASE
   WHEN a.referenced_flag = 'Yes' AND a.lecture_id = MainQuery.referenced_lecture_id THEN Title
   WHEN a.referenced_flag = 'No' AND a.lecture_id = MainQuery.lecture_id THEN Title
   ELSE Null END) As SelectTitle
FROM lectures
0
 
slightwv (䄆 Netminder) Commented:
Can you provide more data?

Can you have more than one reference?

1             Stress Management        Yes               2
2             Stress Managment (CPAP)  Yes           3
3             Relaxation                            No      
0
 
cookiejarAuthor Commented:



1             Stress Management             Yes               2        REPLACED
2             Stress Managment (CPAP)   Yes               3        REPLACED
3             Relaxation                            No                          PRODUCTION

Yes, we can have more than one reference but the one that we want is the title that's  in PRODUCTION
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
So you cannot just select the 'No' values?

One more question: How do you distinguish between 'other' titles?

I assume below you would want 'Relaxation' and 'Some new title' returned?

1             Stress Management             Yes               2        REPLACED
2             Stress Managment (CPAP)   Yes               3        REPLACED
3             Relaxation                            No                          PRODUCTION
4             Some new title                     No  
0
 
cookiejarAuthor Commented:
Yes, we can select the 'No'.  I only want to return the title that's flagged for 'PRODUCTION'.   Once the new title is approved, it will be flagged as PRODUCTION and 'Relexation' would be flagged as REPLACED.  


1             Stress Management             Yes               2        REPLACED
2             Stress Managment (CPAP)   Yes               3        REPLACED
3             Relaxation                            No                          PRODUCTION
4             Some new title                     No                          QA  

For example, an employee may have an old lecture title that may be linked to a referenced lecture, then I want to show the new lecture title on areport.  An employee may have the new lecture title which does not require me to find the referenced lecture, so I show the lecture title that currently in their record.  

For example
John Doe      Stress Management  is what is inn their record but I  want to display:
John Doe      Relaxation

For example
Jane Doe    Relaxation  is what is in their record, I want to
display
Jane Doe    Relaxation


0
 
slightwv (䄆 Netminder) Commented:
Just select 'No' and PRODUCTION?

I'm not following your requirements.  I assume you have SEVERAL sets of production values in this table.  How do you differentiate between them?

Above 'Some new title' was supposed to be from a different 'group'.
0
 
cookiejarAuthor Commented:
I guess all I need is an example or is it possible  to construct a sub  query where
in case referenced_flag = 'Yes' then
      select title from lectures where
              lecture_id = main query referenced_lecture_id
     or if referenced_flag = 'No'
        select title from lectures where
                lecture_id = main query lecture_id          
0
 
slightwv (䄆 Netminder) Commented:
Anything and everything is possible.  The 'how' to do it depends on what tools you have available to you.  For example: Worst case, can you create a function?

I'm still not fully understanding the requirements.

Based on your last example:
1             Stress Management             Yes               2        REPLACED
2             Stress Managment (CPAP)   Yes               3        REPLACED
3             Relaxation                            No                          PRODUCTION
4             Some new title                     No                          QA  

You have a unique identifier:  'No' and 'PRODUCTION'.

From your pseudo-code you repeated in:  http:#a35462236 and the data in this post, you have two 'No' values.  What one gets returned?  The pseudo-code doesn't account for the PROID/QA flag.


You also added the prod/qa flag.  What data type is that?

If you can have several 'Yes' values and no 'No' values, can you select the MAX id?  If not, which one do you want?

Until I get a better understanding of the exact requirements I can't really help.  I doubt any other Expert will be able to unless they get a lucky guess.

Below is a couple of quick examples.  Please add to it to show better requirements and/or get it to fail.


drop table tab1 purge;
create table tab1 (
Lecture_Id number,Title varchar2(30), Referenced_Flag varchar2(3), Referenced_Lecture_Id number
);
insert into tab1 values(1,'Stress Management','Yes',2);
insert into tab1 values(2,'Stress Managment (CPAP)','Yes',3);
insert into tab1 values(3,'Finally relaxed','No',null);
commit;


select title from tab1 where referenced_lecture_id is null;

select title from (
select title, row_number() over(order by lecture_id desc) myrownum
from tab1
)
where myrownum=1
/

Open in new window

0
 
htonkovCommented:
Hi!
Based on original question my first guess for the answer would be:

select title from lectures where 
(lecture_id_from_main_query = referenced_lecture_Id and reference flag = 'Yes' 
or  lecture_id_from_main_query = lecture_id  and reference flag = 'No')

Open in new window


But from the discussion later... I'd say: there is something wrong in your original concept! For example why do you have column "referenced flag" if you have column Referenced_Lecture_Id (if its null than refernced flag is "No")
Are you sending same values to referenced_lecture_Id and lecture_id from main query?

Regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.