1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Combining different table with different column numbers.

Discussion in 'SQL PL/SQL' started by prabhur, Mar 17, 2015.

  1. prabhur

    prabhur Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Hi,
    I want to combine different table with different number of column and checking different conditions.

    SELECT A.S_NO,A.P_NO,A.LLABEL,B.S_NO,B.C_NAME, B.M_CODE,D.CP_NO ,D.x1, D.y1, D.z1 FROM P12 A INNER JOIN P34 B ON A.S_NO = B.CP_IDX INNER JOIN P170 D ON A.S_NO=D.TU_NO INNER JOIN P170 D ON B.S_NO=D.TU_NO

    UNION
    SELECT A.S_NO,A.P_NO,A.LLABEL,B.S_NO,B.C_NAME, B.M_CODE,D.CP_NO,D.x1, D.y1, D.z1 FROM P12 A INNER JOIN P50 B ON A.S_NO = B.END_IDX INNER JOIN P170 D ON A.S_NO=D.TU_NO INNER JOIN P170 D ON B.S_NO=D.TU_NO

    This query is working because same number of columns.

    --------------------------------------------

    SELECT A.S_NO,A.P_NO,A.LLABEL,B.S_NO,B.C_NAME, B.M_CODE,D.CP_NO ,D.x1, D.y1, D.z1 FROM P12 A INNER JOIN P34 B ON A.S_NO = B.CP_IDX INNER JOIN P170 D ON A.S_NO=D.TU_NO INNER JOIN P170 D ON B.S_NO=D.TU_NO

    UNION
    SELECT A.S_NO,A.P_NO,A.LLABEL,B.S_NO,B.C_NAME, B.M_CODE,D.CP_NO,D.x1, D.y1, D.z1 FROM P12 A INNER JOIN P50 B ON A.S_NO = B.END_IDX INNER JOIN P170 D ON A.S_NO=D.TU_NO INNER JOIN P170 D ON B.S_NO=D.TU_NO

    union
    SELECT A.S_NO,A.P_NO,A.LINE_ID,A.LLABEL,D.CP_NO,D.x1, D.y1, D.z1 FROM P12 A INNER JOIN P170 D ON A.S_NO=D.TU_NO


    Its not working different no of columns because union accept only same no of column.


    Give some idea for this task.
     
  2. eras

    eras Active Member

    Messages:
    23
    Likes Received:
    9
    Trophy Points:
    90
    Location:
    Lithuania
    Re: Coimbing different table with different column numbers.

    Code (SQL):

    SELECT A.S_NO,A.P_NO,A.LLABEL,B.S_NO,B.C_NAME, B.M_CODE,D.CP_NO ,D.x1, D.y1, D.z1 FROM P12 A INNER JOIN P34 B ON A.S_NO = B.CP_IDX INNER JOIN P170 D ON A.S_NO=D.TU_NO INNER JOIN P170 D ON B.S_NO=D.TU_NO

    UNION
    SELECT A.S_NO,A.P_NO,A.LLABEL,B.S_NO,B.C_NAME, B.M_CODE,D.CP_NO,D.x1, D.y1, D.z1 FROM P12 A INNER JOIN P50 B ON A.S_NO = B.END_IDX INNER JOIN P170 D ON A.S_NO=D.TU_NO INNER JOIN P170 D ON B.S_NO=D.TU_NO

    UNION
    SELECT A.S_NO,A.P_NO,A.LINE_ID,A.LLABEL,D.CP_NO,D.x1, D.y1, D.z1, NULL, NULL FROM P12 A INNER JOIN P170 D ON A.S_NO=D.TU_NO
     
     
  3. prabhur

    prabhur Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Re: Coimbing different table with different column numbers.

    Hi,
    It shows error message - expression must have same datatype as corresponding expression
     
  4. eras

    eras Active Member

    Messages:
    23
    Likes Received:
    9
    Trophy Points:
    90
    Location:
    Lithuania
    Re: Coimbing different table with different column numbers.

    it's not because of "null, null"
    it's because of:
    ..........
    SELECT A.S_NO,A.P_NO,A.LLABEL
    union
    SELECT A.S_NO,A.P_NO,A.LINE_ID

    i guess a.llabel is not the same type as a.line_id, try use to_char(...)
     
  5. prabhur

    prabhur Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Re: Coimbing different table with different column numbers.

    SELECT A.S_NO,A.P_NO,A.LLABEL,B.S_NO,B.C_NAME, B.M_CODE,D.CP_NO ,D.x1, D.y1, D.z1 FROM P12 A INNER JOIN P34 B ON A.S_NO = B.CP_IDX INNER JOIN P170 D ON A.S_NO=D.TU_NO INNER JOIN P170 D ON B.S_NO=D.TU_NO

    UNION
    SELECT A.S_NO,A.P_NO,A.LLABEL,B.S_NO,B.C_NAME, B.M_CODE,D.CP_NO,D.x1, D.y1, D.z1 FROM P12 A INNER JOIN P50 B ON A.S_NO = B.END_IDX INNER JOIN P170 D ON A.S_NO=D.TU_NO INNER JOIN P170 D ON B.S_NO=D.TU_NO

    UNION
    SELECT A.S_NO,A.P_NO,to_char(A.LINE_ID),A.LLABEL,D.CP_NO,D.x1, D.y1, D.z1, NULL, NULL FROM P12 A INNER JOIN P170 D ON A.S_NO=D.TU_NO

    I changed with to_char() , but same error
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Re: Coimbing different table with different column numbers.

    Hi,

    I think you need like this...

    SELECT A.S_NO,
    A.P_NO,
    A.LLABEL,
    B.S_NO,
    B.C_NAME,
    B.M_CODE,
    D.CP_NO ,
    D.x1,
    D.y1,
    D.z1,
    NULL LINE_ID
    FROM P12 A
    INNER JOIN P34 B
    ON A.S_NO = B.CP_IDX
    INNER JOIN P170 D
    ON A.S_NO=D.TU_NO
    INNER JOIN P170 D
    ON B.S_NO=D.TU_NO

    UNION

    SELECT A.S_NO,
    A.P_NO,
    A.LLABEL,
    B.S_NO,
    B.C_NAME,
    B.M_CODE,
    D.CP_NO,
    D.x1,
    D.y1,
    D.z1,
    NULL LINE_ID
    FROM P12 A
    INNER JOIN P50 B
    ON A.S_NO = B.END_IDX
    INNER JOIN P170 D
    ON A.S_NO=D.TU_NO
    INNER JOIN P170 D
    ON B.S_NO=D.TU_NO

    UNION

    SELECT A.S_NO,
    A.P_NO,
    A.LLABEL,
    NULL S_NO,
    NULL C_NAME,
    NULl M_CODE,
    D.CP_NO,
    D.x1,
    D.y1,
    D.z1,
    A.LINE_ID
    FROM P12 A
    INNER JOIN P170 D
    ON A.S_NO=D.TU_NO

    Because you already have LLABEL col in all 3 queries, but you used to_char(line_id) in 3rd query.
     
  7. prabhur

    prabhur Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Re: Coimbing different table with different column numbers.

    Hi,

    SELECT A.S_NO,
    A.P_NO,
    A.LLABEL,
    B.S_NO,
    B.C_NAME,
    B.M_CODE,
    D.CP_NO ,
    D.x1,
    D.y1,
    D.z1,
    NULL LINE_ID
    FROM P12 A
    INNER JOIN P34 B
    ON A.S_NO = B.CP_IDX
    INNER JOIN P170 D
    ON A.S_NO=D.TU_NO
    INNER JOIN P170 D
    ON B.S_NO=D.TU_NO

    UNION

    SELECT A.S_NO,
    A.P_NO,
    A.LLABEL,
    B.S_NO,
    B.C_NAME,
    B.M_CODE,
    D.CP_NO,
    D.x1,
    D.y1,
    D.z1,
    NULL LINE_ID
    FROM P12 A
    INNER JOIN P50 B
    ON A.S_NO = B.END_IDX
    INNER JOIN P170 D
    ON A.S_NO=D.TU_NO
    INNER JOIN P170 D
    ON B.S_NO=D.TU_NO

    UNION

    SELECT A.S_NO,
    A.P_NO,
    A.LLABEL,
    NULL S_NO,
    NULL C_NAME,
    NULl M_CODE,
    D.CP_NO,
    D.x1,
    D.y1,
    D.z1,
    A.LINE_ID
    FROM P12 A
    INNER JOIN P170 D
    ON A.S_NO=D.TU_NO


    I checked with above its showing error message ORA-00923: FROM keyword not found where expected. I think table name is need for NULL S_NO, NULL C_NAME, NULL M_CODE,
     
  8. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Re: Coimbing different table with different column numbers.

    No need table name. Can you post the full error message, is it showing line number?
     
  9. prabhur

    prabhur Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    SQL> SELECT A.S_NO,A.P_NO,A.LLABEL,
    2 B.S_NO,B.C_NAME, B.M_CODE,
    3 D.CP_NO ,D.x1, D.y1, D.z1,NULL A.LINE_ID FROM P12 A
    4 INNER JOIN P34 B ON A.S_NO = B.CP_IDX
    5 INNER JOIN P170 D ON A.S_NO=D.TU_NO
    6 INNER JOIN P170 D ON B.S_NO=D.TU_NO
    7 WHERE D.P_NO=171
    8 UNION
    9 SELECT A.S_NO,A.P_NO,A.LLABEL,
    10 B.S_NO,B.C_NAME, B.M_CODE,
    11 D.CP_NO,D.x1, D.y1, D.z1, NULL A.LINE_ID FROM P12 A
    12 INNER JOIN P50 B ON A.S_NO = B.END_IDX
    13 INNER JOIN P170 D ON A.S_NO=D.TU_NO
    14 INNER JOIN P170 D ON B.S_NO=D.TU_NO
    15 WHERE D.P_NO=171
    16 UNION
    17 SELECT A.S_NO,A.P_NO,A.LLABEL, NULL S_NO, NULL C_NAME, NULL M_CODE, D.CP_NO,D.CP_EASTING
    as x1, D.x1, D.y1, D.z1, A.LINE_ID FROM P12 A IN
    NER JOIN P170 D ON A.S_NO=D.TU_NO WHERE D.P_NO=171;
    D.CP_NO ,D.x1, D.y1, D.z1,NULL A.LINE_ID FROM P12 A

    *
    ERROR at line 3:
    ORA-00923: FROM keyword not found where expected

    I 3 line it is showing error
     
  10. prabhur

    prabhur Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    SELECT A.S_NO,A.P_NO,A.LLABEL,
    2 B.S_NO,B.C_NAME, B.M_CODE,
    3 D.CP_NO ,D.x1, D.y1, D.z1,A.LINE_ID FROM P12 A
    4 INNER JOIN P34 B ON A.S_NO = B.CP_IDX
    5 INNER JOIN P170 D ON A.S_NO=D.TU_NO
    6 INNER JOIN P170 D ON B.S_NO=D.TU_NO
    7 WHERE D.P_NO=171
    8 UNION
    9 SELECT A.S_NO,A.P_NO,A.LLABEL,
    10 B.S_NO,B.C_NAME, B.M_CODE,
    11 D.CP_NO,D.x1, D.y1, D.z1, A.LINE_ID FROM P12 A
    12 INNER JOIN P50 B ON A.S_NO = B.END_IDX
    13 INNER JOIN P170 D ON A.S_NO=D.TU_NO
    14 INNER JOIN P170 D ON B.S_NO=D.TU_NO
    15 WHERE D.P_NO=171
    16 UNION
    17 SELECT A.S_NO,A.P_NO,A.LLABEL, NULL S_NO, NULL C_NAME, NULL M_CODE, D.CP_NO,D.CP_EASTING
    as x1, D.x1, D.y1, D.z1, A.LINE_ID FROM P12 A IN
    NER JOIN P170 D ON A.S_NO=D.TU_NO WHERE D.P_NO=171


    When I am remove Null in 3 line NULL A.LINE_ID , Null in 11 line NULL A.LINE_ID . Its working
     
  11. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Yes,

    I gave you with out A.line_id, then why you modified like that?

    You didn't execute the query which I suggested right?
     
    prabhur likes this.
  12. eras

    eras Active Member

    Messages:
    23
    Likes Received:
    9
    Trophy Points:
    90
    Location:
    Lithuania
    prabhur,

    syntax NULL A.LINE_ID is incorrect, because You are trying to select NULL and to assign alias A.LINE_ID - it's wrong way. In Your case, You can assign alias as LINE_ID or "A.LINE_ID" (consider double quotes). For more information see "Oracle Database Object Names and Qualifiers"
     
  13. prabhur

    prabhur Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Hi Jagadekara,

    Your query is working fine. Result came as expected. Thanks very much.
    Regards,
    Prabhu
     
  14. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    It's Ok Prabhur.