7 Replies Latest reply: Dec 5, 2012 11:56 PM by ranit B RSS

    Reg : a simple query -

    ranit B
      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
          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
            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
              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
                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
                  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
                    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
                      Awesome Jeneesh... :-)
                      Thanks a ton!!!