• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 85
  • Last Modified:

Oracle query to return extra fields

I am looking for following query  in Oracle
    I have a table Tbl1
    I need fetch all the data from Tbl1
    But where Tbl1.Fld1 = Tbl2.Name  return Tbl2.UID as Tbl2UID
          where Tbl1.Fld2 = Tbl3.Name  return Tbl3.UID as Tbl3UID
        .. a fewmore till  Tbl1.Fld10 = Tbl10.Name  return Tbl10.UID as Tbl10UID
                   
    I may be able to write it with Case statement . But is there a more compact way of doing it?
0
Sam OZ
Asked:
Sam OZ
  • 3
  • 3
  • 3
  • +3
1 Solution
 
PortletPaulfreelancerCommented:
A case expression is the compact way of doing it. e.g.

select ...
, case when Tbl1.Fld1 = Tbl2.Name  then Tbl2.UID
           when Tbl1.Fld2 = Tbl3.Name  thenTbl3.UID
           when Tbl1.Fld10 = Tbl10.Name  thenTbl10.UID
  end as UID
0
 
PortletPaulfreelancerCommented:
by the way...
There is a difference between "case statement" and "case expression" in Oracle. A select query allows use of "case expressions".

"Case expressions" are part of standard SQL (and very common in select and/or update statements)
https://docs.oracle.com/cd/B28359_01/server.111/b28286/expressions004.htm#SQLRF20037

"Case statements" allow for branching in PL/SQL
https://docs.oracle.com/cloud/latest/db112/LNPLS/case_statement.htm#LNPLS01304
0
 
Geert GOracle dbaCommented:
looks like have not normalized your tables
which is a design flaw which will keep coming back to haunt you

make a separate Fields table
if you alreay have 10 columns ... or a multitied of 10, it'll be a lot of work to maintain the queries
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Sam OZAuthor Commented:
I doubt the case statement return what I am looking for . I have to use left join
https://stackoverflow.com/questions/7980052/how-to-do-left-join-with-more-than-2-tables   ( Need to change to oracle flavour)  - It works
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
Need to change to oracle flavour
If you mean the use of "(+)", I can tell you that it will fail in case of left/right joining more than 1 (distinct) table! In those cases you have to use ANSI SQL, like
select .... 
from table_A a 
join table_B b on (b.id_A = a.id) 
left join table_C c on (c.id_A = a.id and c.id_B = b.id) 
where ....

Open in new window

0
 
johnsoneSenior Oracle DBACommented:
The old (+) syntax had some caveats.  For example, this is legal:
SELECT * 
FROM   tablea, 
       tableb, 
       tablec 
WHERE  tablea.f1 = tableb.f1(+) 
       AND tablea.f1 = tablec.f1(+) 

Open in new window

You could outer join to mulitple tables as long as you didn't outer join to a table that was already outer joined.

From what I recall, this shouldn't be legal
SELECT * 
FROM   tablea, 
       tableb, 
       tablec 
WHERE  tablea.f1 = tableb.f1(+) 
       AND tableb.f1 = tablec.f1(+) 

Open in new window

However, it worked.  I will say that the only version I have to test on right now is 12.2.0.1.  I know it wouldn't have worked in older versions of Oracle.  If you look at the plan, it certainly shows the 2 outer joins, and it did generate the correct output using this simple setup:
create table tablea (f1 number);
insert into tablea values (1);
insert into tablea values (2);
insert into tablea values (3);
create table tableb (f1 number);
insert into tableb values (1);
insert into tableb values (2);
create table tablec (f1 number);
insert into tablec values (2);
insert into tablec values (3);
commit;

Open in new window

I would be curious is someone has a older version of Oracle they can run that test on and see what happens.
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
11.2.0.1.0:

SELECT * 
FROM   tablea, 
       tableb, 
       tablec 
WHERE  tablea.f1 = tableb.f1(+) 
       AND tableb.f1 = tablec.f1(+) 

Open in new window

works

SELECT * 
FROM   tablea, 
       tableb, 
       tablec 
WHERE  tablea.f1 = tableb.f1(+) 
       AND tablea.f1 = tablec.f1(+) 

Open in new window

works

alter table tablec add(f2 number);

insert into tablec values (2, 1);

commit;

select *
  from tablec,
       tableb,
       tablea
 where tableb.f1 = tablea.f1
   and tablec.f1(+) = tableb.f1
   and tablec.f2(+) = tablea.f1;

Open in new window

fails with ORA-01417

And that's exactly what I've mentioned above! Neither tablea nor tableb has been outer joined! And I guess it will fail in 12c, too
0
 
johnsoneSenior Oracle DBACommented:
Going way back to Oracle 7 and 8 here, I'm pretty sure that
SELECT * 
FROM   tablea, 
       tableb, 
       tablec 
WHERE  tablea.f1 = tableb.f1(+) 
       AND tableb.f1 = tablec.f1(+)  

Open in new window

would have failed.  TABLEA is outer joined to TABLEB and then TABLEB is outer joined to TABLEC on the join column.  As I recall, that was a failure.

With your modified version that fails in 11.2, it does not fail in 12c.
SQL> select *
  2    from tablec,
  3         tableb,
  4         tablea
  5   where tableb.f1 = tablea.f1
  6     and tablec.f1(+) = tableb.f1
  7     and tablec.f2(+) = tablea.f1;

        F1         F2         F1         F1
---------- ---------- ---------- ----------
                               2          2
                               1          1

Open in new window

0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
Thanks for the quick test. At last, Oracle made this one possible :-)

Cheers
0
 
PortletPaulfreelancerCommented:
I hadn't understood the question to be about joins, but the following might assist
    create table Tbl1 (id int, Fld1 varchar2(20));
    insert into Tbl1 values (1, 'name1');
    insert into Tbl1 values (2, 'name2a');
    insert into Tbl1 values (3, 'name3a');
    
    create table Tbl2 (Name varchar2(20), ID int);
    insert into Tbl2 values ('name2a',1);
    insert into Tbl2 values ('name2b',2);
    
    create table Tbl3 (Name varchar2(20), ID int);
    insert into Tbl3 values ('name3a',1);
    insert into Tbl3 values ('name3b',2);
     
    select
          tbl1.id
        , tbl1.fld1
        , coalesce(tbl2.name,tbl3.name) name
        , coalesce(tbl2.id,tbl3.id) uids
    from Tbl1
    left join Tbl2 on tbl1.fld1 = tbl2.name
    left join Tbl3 on tbl1.fld1 = tbl3.name
    order by tbl1.id

    | ID |   FLD1 |   NAME |   UIDS |
    |----|--------|--------|--------|
    |  1 |  name1 | (null) | (null) |
    |  2 | name2a | name2a |      1 |
    |  3 | name3a | name3a |      1 |

Open in new window

also available at: http://sqlfiddle.com/#!4/831a9/1
0
 
slightwv (䄆 Netminder) Commented:
Sam,

As you can see, there may be several ways to solve your problem but there is also confusion on exactly what you want to achieve.

Can you create a really simple test case with sample data and expected results?

This way we can see what you need from the data and can generate tested SQL.
0
 
Sam OZAuthor Commented:
Sorry in  taking long to reply.  I am not sure the left join really work . The test case is  with 3 table situation ( Tbl1,Tbl2,Tbl3)
Tbl1
     fld1    fld2     fld3      fld4 ....... more fields
      t1         t2        t3    
      t11       t21      t31
      t12       t22       t32

Tbl2
    Name      UID
      t1             t1UID
      t15          t15UID

Tbl3
   Name          UID
     t21              t21UID
     t27             t27UID

 
   

Result should be
     fld1    fld2     fld3      fld4 ....... more fields       tbl2UID           Tbl3UID
      t1         t2        t3                                                     t1UID               <Blank>
      t11       t21      t31                                                    <Blank>           t21UID
      t12       t22       t32                                                 <Blank>             <Blank>
0
 
johnsoneSenior Oracle DBACommented:
Why won't a left outer join work?  Given your sample data, run this query:
SELECT t1.fld1, 
       t1.fld2, 
       t1.fld3, 
       t2."UID" tlb2uid, 
       t3."UID" tbl3uid 
FROM   tbl1 t1 
       LEFT OUTER JOIN tbl2 t2 
                    ON t1.fld1 = t2.NAME 
       LEFT OUTER JOIN tbl3 t3 
                    ON t1.fld2 = t3.NAME 
ORDER  BY t1.fld1; 

Open in new window

That produces this output:
FLD1       FLD2       FLD3       TLB2UID    TBL3UID
---------- ---------- ---------- ---------- ----------
t1         t2         t3         t1UID
t11        t21        t31                   t21UID
t12        t22        t32

Open in new window

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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 3
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now