Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2006
    Posts
    4

    Unanswered: SQL Union trouble

    I have an issue where I have two tables with records that I want to bring back as one result set. When I use

    select c1 from t1 union select c1 from t2

    the code works fine. When I try to add one more column to each select statement it returns the "query block has incorrect number of result columns" error. Both statements have two columns selected and they are both of the same data type as each other.

    Also note that I am not the DB admin and that I am using sql through an odbc connection to an oracle database to bring back data. Is there a way around this or can my union really only be used on one column from each table?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by Rweasel6
    ... Both statements have two columns selected and they are both of the same data type as each other.

    The above statement is incorrect, the Oracle error is clear:
    "query block has incorrect number of result columns"
    PS: Oracle does not lie!
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509

    Thumbs up

    When you union some selects, all the columns that are returned by both selects must be the exact same number of columns and each column must be the same data type. For example

    select c1,c2 from t1
    union
    select c1,c2 from t2;

    would return two columns. Now some information about union. "union" will merge the two selects and suppress any duplicates. "union all" will simply join the two selects. If you ran the following select

    select c1 from t1
    union
    select c1 from t1;

    and t1 contained 10 unique records for c1, then 10 rows would be returned using a "union". If you then ran the following select

    select c1 from t1
    union all
    select c1 from t1;

    Then the unioned select would return 20 rows. If you do not need to supress duplicates, then always use "union all". It is MUCH faster then "union".

    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jun 2006
    Posts
    4
    I have tried the union all

    select c1, c2 from t1
    UNION ALL
    select c1, c2 from t2

    and I still get the error. I am using cognos analyst and wondering if the error may have to do with the cognos program trying to read the data the SQL statement is bringing in although the error that comes up is an ORACLE error.

    again I am able to use UNION if I only have 1 column from each table selected such as

    select c1 from t1
    UNION ALL
    select c1 from t2

    only when I try to select more than one column do I get the error.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Type the following commands and paste the results here

    desc t1
    desc t2

    Are c2 in t1 and c2 in t2 the EXACT same datatype (both number, both varchar2, both date..... etc).
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jun 2006
    Posts
    4
    Not sure if I follow you with the desc. I typed that and it didnt work. Im not a SQL expert so Im not sure if there is supposed to be code in front of this.

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by Rweasel6
    ... I am using cognos analyst and wondering if the error may have to do with the cognos program

    Yes, the problem is with cognos

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Jun 2006
    Posts
    4
    The error must have to do with cognos as I have tried UNION all with two columns from each table that I know are the exact same type and I get the same error

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If COGNOS can't support a union, then change the union in a view.

    create view ti_v as
    select c1, c2 from t1
    UNION ALL
    select c1, c2 from t2;

    and in cognos, use "select c1,c2 from ti_v"
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    Quote Originally Posted by beilstwh
    If COGNOS can't support a union, then change the union in a view.

    create view ti_v as
    select c1, c2 from t1
    UNION ALL
    select c1, c2 from t2;

    and in cognos, use "select c1,c2 from ti_v"

    -- OR -- Dump cognos and get your money back, the word on the street is that cognos is a piece of ____ (garbage).


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •