?
Solved

ORACLE 11g = Switch between two queries in a subquery

Posted on 2011-04-25
9
Medium Priority
?
390 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.
0
Comment
Question by:cookiejar
9 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35461530
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
 

Author Comment

by:cookiejar
ID: 35461568



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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35461586
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:cookiejar
ID: 35461917
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35461936
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
 

Author Comment

by:cookiejar
ID: 35462236
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35463160
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
 
LVL 11

Accepted Solution

by:
yuching earned 1000 total points
ID: 35464219
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
 
LVL 3

Assisted Solution

by:htonkov
htonkov earned 1000 total points
ID: 35508982
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month9 days, 23 hours left to enroll

569 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