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!

How to appen columns.,.

Discussion in 'SQL PL/SQL' started by Vicky, Jul 29, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    I understand that using Set operators, we can append records from two queries.,.,

    But, how can we append columns from the 2 queries,.?!
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    What do you try to make?
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    By Using Joins
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Vicky,
    It's not clear what you want to achieve as a result of:
    Please provide us an example or sample of input data and what you want as output (using Set operators concept ... if possible).
     
  5. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Sure..
    Suppose there is an existing Select query,

    SELECT SNO,EMP_ID,EMP_NAME,SALARY,DOB,MANAGER_ID,CITY
    FROM EMPLOYEES
    union all
    SELECT SNO,EMP_ID,EMP_NAME,SALARY,DOB,MANAGER_ID,CITY
    FROM EMP

    By using the above query, we can append records from the 2nd query to the records from the first query..

    But, wat if I want to APPEND columns to the columns returned from the 1st query,

    the query below is only for appending records, so, it never works

    SELECT SNO,EMP_ID,EMP_NAME,SALARY,DOB,MANAGER_ID,CITY
    FROM EMPLOYEES
    union all
    SELECT E_MAIL
    FROM EMP

    SO, how could we append columns to the columns of the 1st query,
    WITHOUT touching the 1st query, i.e.,without making any changes in the 1st query.,
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    using JOIN operation or if result - one line (one column), then it is possible to use subqueries.

    Note:
    It is better to use JOIN operation as such requests can be improved by the optimizer(CBO)
     
  7. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Vicky,

    There is no such equivalent UNION type of operator for "appending" columns the way you want to do it.
    What you need to do is simply add the column in the 1st query and add the 2nd table name in the FROM part and join it with the appropriate keys.

    I hope your request is just for personal knowledge as I don't see such a requirement in real life situations.

    However, if you want to keep the 1st query, there is an alternate way of doing it (which I don't recommend at all ... but just for the sake of your exploring nature ....:)):

    Code (SQL):
    SELECT e1.employee_id, e1.first_name, e1.salary, e1.hire_date, e1.manager_id, e2.email
    FROM
    (SELECT employee_id,first_name,salary,hire_date,manager_id FROM employees) e1,
    (SELECT employee_id, email FROM employees) e2
    WHERE e1.employee_id = e2.employee_id;
    Your 1st query is embedded as an in-line view (alias e1).

    You can try it with your data model ... my employees table is not like yours !
     
    Vicky likes this.
  8. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Yes, it seems that it not real task, and study process...
     
  9. jagadekara

    jagadekara Forum Guru

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

    This will give error as 'Query block has in correct number of result'

    Your question is not clear, provide required out put.

    I thought your requirement is like this...

    SELECT SNO,EMP_ID,EMP_NAME,SALARY,DOB,MANAGER_ID,CITY,null E_MAIL
    FROM EMPLOYEES
    union all
    SELECT null SNO,null EMP_ID,null EMP_NAME,null SALARY,null DOB,null MANAGER_ID,null CITY,E_MAIL
    FROM EMP;
     
    Vicky likes this.