We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

ORACLE 11g = Switch between two queries in a subquery

cookiejar
cookiejar asked
on
Medium Priority
405 Views
Last Modified: 2012-05-11
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.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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      

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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  

Author

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


CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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'.

Author

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          
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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

Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.