problem trying to use UNION in the sql query
-
Hi all, I have two sets of query which if run alone, runs without any errors but when i try to use UNION between these two queries, it gave me error like this:
Error converting data type varchar to numeric.
The sql queries are
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
a.RESIDSTS, d.ROOM_DESC, d.ROOM_PHONE
from SMASTER a
join NMASTER b on a.ID_NUM = b.ID_NUM
join BMASTER c on a.ID_NUM = c.ID_NUM
join RMASTER d on a.LOC_CDE = d.LOC_CDE and a.BLDG_CDE = d.BLDG_CDE and a.ROOM_CDE = d.ROOM_CDE
where LOWER(b.LAST_NAME) like '%ram%'
and a.RESIDSTS = 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))
UNION
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
a.RESIDSTS as STATUS, '' , ''
from SMASTER a
join NMASTER b on a.ID_NUM = b.ID_NUM
join BMASTER c on a.ID_NUM = c.ID_NUM
join RMASTER d on a.ID_NUM = a.ID_NUM
where LOWER(b.LAST_NAME) like '%ram%'
and a.RESIDSTS <> 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))but when i run
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
a.RESIDSTS, d.ROOM_DESC, d.ROOM_PHONE
from SMASTER a
join NMASTER b on a.ID_NUM = b.ID_NUM
join BMASTER c on a.ID_NUM = c.ID_NUM
join RMASTER d on a.LOC_CDE = d.LOC_CDE and a.BLDG_CDE = d.BLDG_CDE and a.ROOM_CDE = d.ROOM_CDE
where LOWER(b.LAST_NAME) like '%ram%'
and a.RESIDSTS = 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))or
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
a.RESIDSTS as STATUS, '' , ''
from SMASTER a
join NMASTER b on a.ID_NUM = b.ID_NUM
join BMASTER c on a.ID_NUM = c.ID_NUM
join RMASTER d on a.ID_NUM = a.ID_NUM
where LOWER(b.LAST_NAME) like '%ram%'
and a.RESIDSTS <> 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', ' -
Hi all, I have two sets of query which if run alone, runs without any errors but when i try to use UNION between these two queries, it gave me error like this:
Error converting data type varchar to numeric.
The sql queries are
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
a.RESIDSTS, d.ROOM_DESC, d.ROOM_PHONE
from SMASTER a
join NMASTER b on a.ID_NUM = b.ID_NUM
join BMASTER c on a.ID_NUM = c.ID_NUM
join RMASTER d on a.LOC_CDE = d.LOC_CDE and a.BLDG_CDE = d.BLDG_CDE and a.ROOM_CDE = d.ROOM_CDE
where LOWER(b.LAST_NAME) like '%ram%'
and a.RESIDSTS = 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))
UNION
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
a.RESIDSTS as STATUS, '' , ''
from SMASTER a
join NMASTER b on a.ID_NUM = b.ID_NUM
join BMASTER c on a.ID_NUM = c.ID_NUM
join RMASTER d on a.ID_NUM = a.ID_NUM
where LOWER(b.LAST_NAME) like '%ram%'
and a.RESIDSTS <> 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))but when i run
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
a.RESIDSTS, d.ROOM_DESC, d.ROOM_PHONE
from SMASTER a
join NMASTER b on a.ID_NUM = b.ID_NUM
join BMASTER c on a.ID_NUM = c.ID_NUM
join RMASTER d on a.LOC_CDE = d.LOC_CDE and a.BLDG_CDE = d.BLDG_CDE and a.ROOM_CDE = d.ROOM_CDE
where LOWER(b.LAST_NAME) like '%ram%'
and a.RESIDSTS = 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))or
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
a.RESIDSTS as STATUS, '' , ''
from SMASTER a
join NMASTER b on a.ID_NUM = b.ID_NUM
join BMASTER c on a.ID_NUM = c.ID_NUM
join RMASTER d on a.ID_NUM = a.ID_NUM
where LOWER(b.LAST_NAME) like '%ram%'
and a.RESIDSTS <> 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', 'I figure out that if I take
d.ROOM_PHONE
out from the code, it ran well. But why it gave error in there ? what if I need to keep that piece and get the result without error?
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
a.RESIDSTS, d.ROOM_DESC, d.ROOM_PHONE
from SMASTER a
join NMASTER b on a.ID_NUM = b.ID_NUM
join BMASTER c on a.ID_NUM = c.ID_NUM
join RMASTER d on a.LOC_CDE = d.LOC_CDE and a.BLDG_CDE = d.BLDG_CDE and a.ROOM_CDE = d.ROOM_CDE
where LOWER(b.LAST_NAME) like '%ram%'
and a.RESIDSTS = 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))
UNION
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
a.RESIDSTS as STATUS, '' ,''
from SMASTER a
join NMASTER b on a.ID_NUM = b.ID_NUM
join BMASTER c on a.ID_NUM = c.ID_NUM
join RMASTER d on a.ID_NUM = a.ID_NUM
where LOWER(b.LAST_NAME) like '%ram%'
and a.RESIDSTS <> 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))suchita
-
I figure out that if I take
d.ROOM_PHONE
out from the code, it ran well. But why it gave error in there ? what if I need to keep that piece and get the result without error?
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
a.RESIDSTS, d.ROOM_DESC, d.ROOM_PHONE
from SMASTER a
join NMASTER b on a.ID_NUM = b.ID_NUM
join BMASTER c on a.ID_NUM = c.ID_NUM
join RMASTER d on a.LOC_CDE = d.LOC_CDE and a.BLDG_CDE = d.BLDG_CDE and a.ROOM_CDE = d.ROOM_CDE
where LOWER(b.LAST_NAME) like '%ram%'
and a.RESIDSTS = 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))
UNION
select a.ID_NUM as ID, b.LAST_NAME, b.FIRST_NAME, c.BIRTH_DTE, RIGHT(c.SSN, 4),
a.RESIDSTS as STATUS, '' ,''
from SMASTER a
join NMASTER b on a.ID_NUM = b.ID_NUM
join BMASTER c on a.ID_NUM = c.ID_NUM
join RMASTER d on a.ID_NUM = a.ID_NUM
where LOWER(b.LAST_NAME) like '%ram%'
and a.RESIDSTS <> 'R' and (a.T_CDE in ('20', '21') or a.UDEF in ('20', '21'))suchita
What is the type for the column ROOM_PHONE. From your error, I bet it is numeric. In the second select change the final '' column to be to_number(NULL). That might make a difference. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]