where clause with union giving error

The most prableble cause for this error is TO_NUMBER (emp_id). Your emp_id column must have some values that are not numbers...contain some characters...due to which you are getting this error. Its just like :


SQL> select to_number('12312312') from dual;

TO_NUMBER('12312312')
=====================
             12312312

Elapsed: 00:00:00.72
SQL> ed
Wrote file afiedt.buf

  1* select to_number('A12312312') from dual
SQL> /
select to_number('A12312312') from dual
       *
ERROR at line 1:
ORA-01722: invalid number

Dear Pratik and Riaz both of you are correct.
In fact I tried inner part of sql using to_number as follows and it is not giving any error...
SELECT to_number(emp_id), name_first, name_middle, name_last, desig_des, t
            FROM (SELECT h.emp_id, h.name_first, h.name_middle, h.name_last,
                         d.desig_des, 'Y' t
                    FROM hr_emp h, hr_desig d
                   WHERE d.desig_id = h.desig_id
                     AND h.status = 'C'
                     AND h.emp_id NOT IN (SELECT emp_id
                                            FROM ta_hr_att_m)
                  UNION
                  SELECT h.emp_id, h.name_first, h.name_middle, h.name_last,
                         d.desig_des, 'N' t
                    FROM ta_hr_att_m a, hr_emp h, hr_desig d
                   WHERE h.emp_id = a.emp_id
                     AND d.desig_id = h.desig_id
                     AND a.rel_ter_lett_iss = 'N')
           WHERE SUBSTR (emp_id, 0, 1) IN
                                    ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9');

Acctually I am having problem when I am
giving condition  where TO_NUMBER (emp_id) >= 1 AND TO_NUMBER (emp_id) <= 100
with outer most select statement.
Rgds,
Suman

Since we dont have access to your data, it may not be possible to pin-point your problem.

If you select TO_NUMBER(emp_id) from your inner query and it is working without problems, then you can try having your clause without the TO_NUMBER().

For example :

SQL> select to_number('123') emp_id from dual;

    EMP_ID
----------
       123

SQL> select emp_id from (select to_number('123') emp_id from dual)
  2  where emp_id >= 0 and emp_id <= 200;

    EMP_ID
----------
       123


Though in 9i the above SQL (with to_number()) works, but I think it might give problems on 8i. Unfortunately all our databases are now on 9i, so I cant be sure of that.

On 9i :
  1  select emp_id from (select to_number('123') emp_id from dual)
  2* where to_number(emp_id) >= 0 and emp_id <= 200
SQL> /

    EMP_ID
----------
       123

Thanx all . I got my solution myself.
Thanx again.
I want to delete this question.
Please no more comment is required..

Rgds,
bhombal

Its great to know that you have found your solution. Can you please post your solution ?

Dear Pratik,
this is the solution which I found as follows...
1 SELECT   emp_id,
2         CONCAT (
3            CONCAT (CONCAT (CONCAT (name_first, ' '), name_middle), ' '),
4            name_last
5         ),
6         desig_des, t
7    FROM (SELECT emp_id, name_first, name_middle, name_last, desig_des, t
8            FROM (SELECT h.emp_id, h.name_first, h.name_middle, h.name_last,
9                         d.desig_des, 'Y' t
10                    FROM ta_hr_empinf_m h, ta_hr_desig_m d
11                   WHERE d.desig_id = h.desig_id
12                     AND h.status = 'C'
13                     AND h.emp_id NOT IN (SELECT emp_id
14                                            FROM ta_hr_att_m)
15                  UNION
16                  SELECT h.emp_id, h.name_first, h.name_middle, h.name_last,
17                         d.desig_des, 'N' t
18                  FROM ta_hr_att_m a, ta_hr_empinf_m h, ta_hr_desig_m d
19                 WHERE h.emp_id = a.emp_id
20                   AND d.desig_id = h.desig_id
21                   AND a.rel_ter_lett_iss = 'N')
22         WHERE SUBSTR (emp_id, 0, 1) IN
23                                  ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9'))
24    WHERE TO_NUMBER (emp_id) >= 1 AND TO_NUMBER (emp_id) <= 100
25    ORDER BY TO_NUMBER (emp_id)

After parsing when it fetches data any how line(24) is excuted for filtering and it getting error. May be it sounds absurd, because it sould execute inner query(inline view) first then should execute outer portion.
Experimentally I deleted all records which contained non-numeric value then , it worked fine.
In fact I added to_number(emp_id) in line(7), which I said previously. I executed this query by putting to_number at line(7) , then also it was giving error.
So I came to this conclusion.

Anyway, my assumption may be wrong. If you think some think different let me know.
My mail id : contact_suman@hotmail.com

Rgds,
Bhomabl

Can you try this:


SELECT   emp_id,
         CONCAT (
            CONCAT (CONCAT (CONCAT (name_first, ' '), name_middle), ' '),
            name_last
         ),
         desig_des, t
    FROM (SELECT emp_id, name_first, name_middle, name_last, desig_des, t
            FROM ((SELECT to_number(h.emp_id), h.name_first, h.name_middle, h.name_last,
                          d.desig_des, 'Y' t
                     FROM ta_hr_empinf_m h, ta_hr_desig_m d
                    WHERE d.desig_id = h.desig_id
                      AND h.status = 'C'
                      AND h.emp_id NOT IN (SELECT emp_id
                                             FROM ta_hr_att_m))
                  UNION
                  (SELECT to_number(h.emp_id), h.name_first, h.name_middle, h.name_last,
                          d.desig_des, 'N' t
                     FROM ta_hr_att_m a, ta_hr_empinf_m h, ta_hr_desig_m d
                    WHERE h.emp_id = a.emp_id
                      AND d.desig_id = h.desig_id
                      AND a.rel_ter_lett_iss = 'N'))
           WHERE SUBSTR (emp_id, 0, 1) IN
                                    ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9'))
   WHERE emp_id >= 0 AND emp_id <= 100
ORDER BY TO_NUMBER (emp_id)

Now if I change the sql statement as follows it returns desire rows...
SELECT   emp_id,
         CONCAT (
            CONCAT (CONCAT (CONCAT (name_first, ' '), name_middle), ' '),
            name_last
         ),
         desig_des, t
    FROM (SELECT emp_id, name_first, name_middle, name_last, desig_des, t
            FROM ((SELECT h.emp_id, h.name_first, h.name_middle, h.name_last,
                          d.desig_des, 'Y' t
                     FROM ta_hr_empinf_m h, ta_hr_desig_m d
                    WHERE d.desig_id = h.desig_id
                      AND h.status = 'C'
                      AND h.emp_id NOT IN (SELECT emp_id
                                             FROM ta_hr_att_m))
                  UNION
                  (SELECT h.emp_id, h.name_first, h.name_middle, h.name_last,
                          d.desig_des, 'N' t
                     FROM ta_hr_att_m a, ta_hr_empinf_m h, ta_hr_desig_m d
                    WHERE h.emp_id = a.emp_id
                      AND d.desig_id = h.desig_id
                      AND a.rel_ter_lett_iss = 'N'))
           WHERE SUBSTR (emp_id, 0, 1) IN
                                    ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')


             AND TO_NUMBER (emp_id) >= 0
             AND TO_NUMBER (emp_id) <= 100)
ORDER BY TO_NUMBER (emp_id)