This discussion is archived
7 Replies Latest reply: Dec 5, 2012 9:56 PM by ranit B RSS

Reg : a simple query -

ranit B Expert
Currently Being Moderated
Hi All,

I got stuck up with a simple requirement -

I have 2 tables - X and Y.
Suppose, X has 2 columns - Original_Literature_Number and Revised_Literature_Number
Y has 2 columns - Literature_Number and Literature_Name

Output i want is -
Original_Literature_Number Orig_Literature_Name Revised_Literature_Number Rev_Literature_Name

Hope this explanation is ok.
Please let me know if you need further inputs.

I tried something like this ...
SELECT x.original_literature_number,
       y.literature_name,
       x.translated_literature_number
  FROM x, y
 WHERE x.original_literature_number = y.literature_number;
My Database :
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE     10.2.0.3.0     Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Edited by: ranit B on Dec 6, 2012 11:07 AM
-- added code as suggested by SB
  • 1. Re: Reg : a simple query -
    sb92075 Guru
    Currently Being Moderated
    ranit B wrote:
    Hi All,

    I got stuck up with a simple requirement -

    I have 2 tables - X and Y.
    Suppose, X has 2 columns - Original_Literature_Number and Revised_Literature_Number
    Y has 2 columns - Literature_Number and Literature_Name

    Output i want is -
    Original_Literature_Number Orig_Literature_Name Revised_Literature_Number Rev_Literature_Name

    Hope this explanation is ok.
    Please let me know if you need further inputs.

    My Database :
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
    PL/SQL Release 10.2.0.3.0 - Production
    CORE     10.2.0.3.0     Production
    TNS for Solaris: Version 10.2.0.3.0 - Production
    NLSRTL Version 10.2.0.3.0 - Production
    why do you post in SQL & PL/SQL and do not post any SQL or PL/SQL?


    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: Reg : a simple query -
    jeneesh Guru
    Currently Being Moderated
    Self Join
    select *
    from x,y y1,y y2
    where x.Original_Literature_Number = y1.Literature_Number
    and x.Revised_Literature_Number = y2.Literature_Number
  • 3. Re: Reg : a simple query -
    ranit B Expert
    Currently Being Moderated
    jeneesh wrote:
    Self Join
    select *
    from x,y y1,y y2
    where x.Original_Literature_Number = y1.Literature_Number
    and x.Revised_Literature_Number = y2.Literature_Number
    Gives -
    --
    [Error] Execution (10: 5): ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_X'
    --

    It worked... Thanks Jeneesh :-)

    Edited by: ranit B on Dec 6, 2012 11:12 AM
  • 4. Re: Reg : a simple query -
    jeneesh Guru
    Currently Being Moderated
    Or using a single scan
    select  x.Original_Literature_Number,
         max(decode(x.Original_Literature_Number, y.Literature_Number,y.Literature_Name)) org_name,
         x.Revised_Literature_Number,
         max(decode(x.Revised_Literature_Number, y.Literature_Number,y.Literature_Name)) rev_name
    from x,y 
    where x.Original_Literature_Number = y.Literature_Number
    or x.Revised_Literature_Number = y.Literature_Number
    group by x.Original_Literature_Number,x.Revised_Literature_Number
    not tested..

    Provide sample data, if you are expecting something else..
  • 5. Re: Reg : a simple query -
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,

    try this..
    SELECT a.original_literature_number,
               a.translated_literature_number
                b.literature_name,
           FROM x a, y b
           WHERE a.original_literature_number = b.literature_number;
  • 6. Re: Reg : a simple query -
    jeneesh Guru
    Currently Being Moderated
    select *
    from x;
    
    ORIGINAL_LITERATURE_NUMBER REVISED_LITERATURE_NUMBER
    -------------------------- -------------------------
                             1                         2 
                             3                         4 
    
    select *
    from y;
    
    LITERATURE_NUMBER LITERATURE_NAME
    ----------------- ---------------
                    1 A               
                    2 B               
                    3 C               
                    4 D     
    
    select *
    from x,y y1,y y2
    where x.Original_Literature_Number = y1.Literature_Number
    and x.Revised_Literature_Number = y2.Literature_Number;
    
    ORIGINAL_LITERATURE_NUMBER REVISED_LITERATURE_NUMBER LITERATURE_NUMBER LITERATURE_NAME LITERATURE_NUMBER LITERATURE_NAME
    -------------------------- ------------------------- ----------------- --------------- ----------------- ---------------
                             1                         2                 1 A                               2 B               
                             3                         4                 3 C     
    
    select  x.Original_Literature_Number,
         max(decode(x.Original_Literature_Number, y.Literature_Number,y.Literature_Name)) org_name,
         x.Revised_Literature_Number,
         max(decode(x.Revised_Literature_Number, y.Literature_Number,y.Literature_Name)) rev_name
    from x,y 
    where x.Original_Literature_Number = y.Literature_Number
    or x.Revised_Literature_Number = y.Literature_Number
    group by x.Original_Literature_Number,x.Revised_Literature_Number;
    
    ORIGINAL_LITERATURE_NUMBER ORG_NAME REVISED_LITERATURE_NUMBER REV_NAME
    -------------------------- -------- ------------------------- --------
                             1 A                                2 B        
                             3 C                                4 D        
  • 7. Re: Reg : a simple query -
    ranit B Expert
    Currently Being Moderated
    Awesome Jeneesh... :-)
    Thanks a ton!!!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points