Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2006
    Posts
    119

    Unanswered: Data conversion problem

    Hi,
    I am trying to a UNION ALL select from two tables: Person and Employee
    Person has the attributes Firstname, Lastname. Employee has the attributes Firstname, Lastname, Salary.
    I do:
    <PRE>
    SELECT FIRSTNAME, LASTNAME, NULL AS SALARY FROM PERSON
    UNION ALL
    SELECT FIRSTNAME, LASTNAME, SALARY FROM EMPLOYEE

    </PRE>
    Works fine in Oracle 9 but not in Oracle 8.
    In Oracle 8, I get:
    ORA-01790: expression must have same datatype as corresponding expression

    Any ideas greatly appreciated.
    Alex

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    what are you trying to accomplish with this query?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jan 2006
    Posts
    119
    I am trying to select all persons and employees in the query. It's more complicated than this, but I have simplified it to the simpliest query which causes the error

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    SELECT FIRSTNAME, LASTNAME, SALARY FROM EMPLOYEE
    unon all
    SELECT FIRSTNAME, LASTNAME, to_number(NULL) AS SALARY FROM PERSON
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    is there a relationship between the two?
    ie: are all employees in the person table?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Jan 2006
    Posts
    119
    No there is no relationship between them.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >No there is no relationship between them.
    Maybe some body needs to learn about data normalization before creating tables.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jan 2006
    Posts
    119
    Hi Beilstwh,
    Your idea works for Numbers and most Oracle datatypes. Problem is, it does not work for LONG i.e. TO_LONG(null) doesn't work.
    Any ideas?
    Cheers
    ALex

  9. #9
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by Breako
    Hi Beilstwh,
    Your idea works for Numbers and most Oracle datatypes. Problem is, it does not work for LONG i.e. TO_LONG(null) doesn't work.
    Any ideas?
    Cheers
    ALex
    If there was a TO_LONG() function I'm sure it would work.

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    which is the LONG column????

    anyways, just fudge it.
    PHP Code:
    SELECT FIRSTNAMELASTNAMESALARY FROM EMPLOYEE
    unon all
    SELECT FIRSTNAME
    LASTNAME'0' AS SALARY FROM PERSON 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Jan 2006
    Posts
    119
    Thanks for all those who replied. Unfortunately this seems to be an Oracle 8 limitation.

Posting Permissions

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