Forum Stats

  • 3,751,256 Users
  • 2,250,338 Discussions
  • 7,867,361 Comments

Discussions

Problem in fetching data for a single column

Anindya Gayen
Anindya Gayen Member Posts: 96
edited Jul 31, 2013 8:46AM in SQL & PL/SQL

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.

Anindya Gayen

Best Answer

  • BluShadow
    BluShadow Member, Moderator Posts: 41,312 Red Diamond
    Accepted Answer

    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;
    
    Anindya Gayen
«1

Answers

  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    edited Jul 29, 2013 1:19PM

    > (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.


  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,904 Red Diamond
    edited Jul 29, 2013 1:21PM


    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.

  • 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

  • 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
    
  • 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.

  • michaelrozar17
    michaelrozar17 Member Posts: 782
    edited Jul 30, 2013 3:28AM

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

  • 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

  • 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 

  • 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.

  • 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?

This discussion has been closed.