Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Select statement with an alternate select if one column value NULL?

660131Feb 2 2009 — edited Feb 3 2009
Hi, using oracle 10, Im trying to do a select where if one column's value is null, it will return a different value for that column from another select. Unsure of how exactly to do this.

the current query is:
SELECT group_nbr
                       ,sect
                       ,ext_id, 
                       ,indv_name
                       ,dob
                       ,oi_name
                       ,type
                       
FROM thistable tt
WHERE type = 'E'
AND date_created >= sysdate - 22
AND NVL(b_updated,TO_DATE('1800-01-01','YYYY-MM-DD')) > p_updated AND oi_name IS NOT NULL
ORDER BY ext_id, indv_name;
what I want to do is if ext_id is null, is replace it with the value from this from the info for the row it was on:
SELECT  COV_NUMBER||'-'||TAG_NUMBER
FROM  thistable tt,
      infotable1 it1,
      infortable2 it2
Where (it2.last_name = SUBSTR(tt.indv_name, 1, INSTR(tt.indv_name, ',') - 1) AND   it2.first_name = SUBSTR(tt.indv_name, INSTR(tt.indv_name, ',') + 2) AND it2.birthdate = tt.dob)
AND    it2.indv_id = it1.indv_id
AND it1.term_date > sysdate
AND it1.relation_type IN (0,1)
Basically if ext_id is null, use the persons first name, last name (have to seperate) and dob from thistable to grab the id from infotable2, and use the id from infotable2 to get the cov+tag from infotable1 in one select. Is this possible?
This post has been answered by Frank Kulash on Feb 3 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 3 2009
Added on Feb 2 2009
10 comments
3,888 views