1 2 Previous Next 17 Replies Latest reply: Jul 31, 2013 7:46 AM by Anindya Gayen RSS

    Problem in fetching data for a single column

    Anindya Gayen

      Hi,

       

      I have a requirement where I have to fetch three records (intf.INTERFACE_DEF_ID , b2b.B2B_TPA_DEF_ID , osb.OSB_INF_DEF_ID) for the select statement to a particular column(val_id)

       

      The below query is giving error.

       

      Please help..

       

      select

      (intf.INTERFACE_DEF_ID or b2b.B2B_TPA_DEF_ID or osb.OSB_INF_DEF_ID) as val_id

      FROM

      ABC.INTERFACE_DEF intf,

      ABC.B2B_TPA_DEF b2b,

      ABC.OSB_INF_DEF osb;

       

       

      Thanks.

        • 1. Re: Problem in fetching data for a single column
          SomeoneElse

          > (intf.INTERFACE_DEF_ID or b2b.B2B_TPA_DEF_ID or osb.OSB_INF_DEF_ID) as val_id

           

          This doesn't make any sense.  What are you expecting this to do?

           

          You're also missing joins (probably) between your tables.


          • 2. Re: Problem in fetching data for a single column
            Frank Kulash


            Hi,

             

            Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.

            Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.

            Always say which version of Oracle you're using (for example, 11.2.0.2.0).

            See the forum FAQ: https://forums.oracle.com/message/9362002

             

            Maybe you want the COALESCE function

             

            COALESCE (a1, a2, a3, ...)

             

            returns the first argument that is not NULL.

            • 3. Re: Problem in fetching data for a single column
              Anindya Gayen

              Hi,

               

              Thanks for your reply..

              I am explaining my problem in details..

              I have three tables in schema ABC as follows

              ABC.INTERFACE_DEF,

              ABC.B2B_TPA_DEF,

              ABC.OSB_INF_DEF.

               

              There are no joins between them. They have respective Ids like INTERFACE_DEF_ID in INTERFACE_DEF ,

              B2B_TPA_DEF_ID in B2B_TPA_DEF and OSB_INF_DEF_ID in OSB_INF_DEF.

               

              Suppose the values of INTERFACE_DEF_ID in INTERFACE_DEF are 1,2.

              B2B_TPA_DEF_ID in B2B_TPA_DEF  are 3,4

              and OSB_INF_DEF_ID in OSB_INF_DEF are 12,13.

               

              I want a select query where in a single column ( for e.g.- val_id) will contain all the values of INTERFACE_DEF_ID, B2B_TPA_DEF_ID and OSB_INF_DEF_ID.

               

              So my val_id will have all the id values 1,2,3,4,12,13.

               

              Is it possible to get that??

               

              Your reply is highly valuable.

               

               

              I am using Oracle version 11.2.0.3.0

              • 4. Re: Problem in fetching data for a single column
                michaelrozar17

                Try..

                select INTERFACE_DEF_ID val_id from INTERFACE_DEF
                union all
                select B2B_TPA_DEF_ID from B2B_TPA_DEF
                union all
                select OSB_INF_DEF_ID from OSB_INF_DEF
                
                • 5. Re: Problem in fetching data for a single column
                  Anindya Gayen

                  Hi,

                   

                  Thank you for the reply.

                  But my main intention is to get the records in val_id column, i am not getting that.

                   

                  The code I am using is:

                   

                  select (

                  (select intf.INTERFACE_DEF_ID FROM

                  ABC.INTERFACE_DEF intf) union all (select b2b.B2B_TPA_DEF_ID FROM

                  ABC.B2B_TPA_DEF b2b) union all (select osb.OSB_INF_DEF_ID FROM

                  ABC.OSB_INF_DEF osb))val_id

                  FROM

                  ABC.INTERFACE_DEF intf,

                  ABC.B2B_TPA_DEF b2b,

                  ABC.OSB_INF_DEF osb;

                   

                   

                   

                  For which I am getting an error as:

                  ORA-01427: single-row subquery returns more than one row

                  01427. 00000 -  "single-row subquery returns more than one row"

                  *Cause:   

                  *Action:

                   

                   

                  Pls help.

                  • 6. Re: Problem in fetching data for a single column
                    michaelrozar17

                    Can you please try running the code i posted and let us know the results you get.

                    • 7. Re: Problem in fetching data for a single column
                      Anindya Gayen

                      Yes it is giving the result as

                       

                      Interface_def_id

                      1

                      2

                      3

                      4

                      12

                      13

                       

                      My column name should be val_id.

                       

                      Pls help

                      • 8. Re: Problem in fetching data for a single column
                        michaelrozar17

                        I have given the alias VAL_ID to column INTERFACE_DEF_ID in the first SELECT query. Did you miss that by any chance while executing?

                        select INTERFACE_DEF_ID VAL_ID from INTERFACE_DEF 

                        union all 

                        select B2B_TPA_DEF_ID from B2B_TPA_DEF 

                        union all 

                        select OSB_INF_DEF_ID from OSB_INF_DEF 

                        • 9. Re: Problem in fetching data for a single column
                          Anindya Gayen

                          Hi Michael,

                           

                          Thank you yes it working.

                           

                          Now i want to create a view from this..

                           

                          In using the following code I am getting error:

                           

                          create view abc_v_6

                          val_id

                          AS

                          select(select intf.INTERFACE_DEF_ID FROM

                          ABC.INTERFACE_DEF intf union all select b2b.B2B_TPA_DEF_ID FROM

                          ABC.B2B_TPA_DEF b2b union all select osb.OSB_INF_DEF_ID FROM

                          ABC.OSB_INF_DEF osb)val_id

                          FROM

                          ABC.INTERFACE_DEF intf,

                          ABC.B2B_TPA_DEF b2b,

                          ABC.OSB_INF_DEF osb;

                           

                           

                           

                          Error I am getting is:

                           

                          Error at Command Line:1 Column:19

                          Error report:

                          SQL Error: ORA-00905: missing keyword

                          00905. 00000 -  "missing keyword"

                          *Cause:   

                          *Action:

                           

                           

                           

                          Please help.

                          • 10. Re: Problem in fetching data for a single column
                            michaelrozar17

                            create view abc_v_6

                            val_id

                            AS

                            select(select intf.INTERFACE_DEF_ID FROM

                            ABC.INTERFACE_DEF intf union all select b2b.B2B_TPA_DEF_ID FROM

                            ABC.B2B_TPA_DEF b2b union all select osb.OSB_INF_DEF_ID FROM

                            ABC.OSB_INF_DEF osb)val_id

                            FROM

                            ABC.INTERFACE_DEF intf,

                            ABC.B2B_TPA_DEF b2b,

                            ABC.OSB_INF_DEF osb;

                            Can you tell why you want to use the above highlighted query which yielded an error previously?

                            • 11. Re: Problem in fetching data for a single column
                              Anindya Gayen

                              Hi Michael,

                               

                              With the following code I tried to create but it was giving error, so tried to do in the above way, but still is giving error.

                               

                                 create view abc_v_6

                                  val_id

                                  AS

                                  select INTERFACE_DEF_ID val_id FROM

                                  ABC.INTERFACE_DEF union all select B2B_TPA_DEF_ID FROM

                                  ABC.B2B_TPA_DEF union all select OSB_INF_DEF_ID FROM

                                  ABC.OSB_INF_DEF

                               

                              error it was giving is:

                               

                              Error at Command Line:19 Column:4

                              Error report:

                              SQL Error: ORA-00905: missing keyword

                              00905. 00000 -  "missing keyword"

                              *Cause:   

                              *Action:

                               

                              Kindly let me know where is the mistake and what to do to solve it out.

                               

                               

                              Actually there will be a view with a val_id as the column where the data will be the the id's from the three tables.

                               

                              Thanks.

                              • 12. Re: Problem in fetching data for a single column
                                SomeoneElse

                                > Error at Command Line:19 Column:4

                                 

                                It's difficult to tell since you posted only 7 lines.

                                • 13. Re: Problem in fetching data for a single column
                                  Anindya Gayen

                                  Oh sorry..

                                  Kindly help me in creating the view.

                                   

                                  The code i executed is

                                   

                                   

                                   

                                  create view abc_v_6

                                      val_id

                                      AS

                                      select INTERFACE_DEF_ID val_id FROM

                                      ABC.INTERFACE_DEF union all select B2B_TPA_DEF_ID FROM

                                      ABC.B2B_TPA_DEF union all select OSB_INF_DEF_ID FROM

                                      ABC.OSB_INF_DEF

                                      FROM

                                      ABC.INTERFACE_DEF intf,

                                      ABC.B2B_TPA_DEF b2b,

                                      ABC.OSB_INF_DEF osb;

                                   

                                   

                                  Error:

                                   

                                  Error starting at line 18 in command:

                                  create view abc_v_6

                                      val_id

                                      AS

                                      select INTERFACE_DEF_ID val_id FROM

                                      ABC.INTERFACE_DEF union all select B2B_TPA_DEF_ID FROM

                                      ABC.B2B_TPA_DEF union all select OSB_INF_DEF_ID FROM

                                      ABC.OSB_INF_DEF

                                      FROM

                                      ABC.INTERFACE_DEF intf,

                                      ABC.B2B_TPA_DEF b2b,

                                      ABC.OSB_INF_DEF osb

                                  Error at Command Line:19 Column:4

                                  Error report:

                                  SQL Error: ORA-00905: missing keyword

                                  00905. 00000 -  "missing keyword"

                                  *Cause:   

                                  *Action:

                                   

                                   

                                  Thanks.

                                  • 14. Re: Problem in fetching data for a single column
                                    BluShadow

                                    Well that errors because it's just syntactically wrong all over the place.  Essentially the SELECT statement isn't even valid.

                                     

                                    create view abc_v_6 AS

                                       select INTERFACE_DEF_ID val_id FROM ABC.INTERFACE_DEF union all

                                       select B2B_TPA_DEF_ID FROM ABC.B2B_TPA_DEF union all

                                       select OSB_INF_DEF_ID FROM ABC.OSB_INF_DEF;

                                    1 2 Previous Next