Re: SQL Create table with a UNION query returns error

Several things wrong with this statement.
1 - Style. You put your from statement at the end of a select statement
instead of on it's own line. Had to look for it. Not nitpicking. It
does make a big difference.
2 - Do NOT join files using
filea, fileb where fileaCol=filebCol
2a - Makes you look like you learned SQL by doing RTVQMQRY and never
cracked an SQL book
2b - You really need to understand concepts like "left outer join"
3 - Consider using the CASE clause

Try this instead:
Create table qtemp/AR0119W as (
select
case
when sfx_slspid is not null and (
(sfx_orgid = 'AXIP' and sfx_pvdid='HARTFIELD')
then cast( 'X' as char( 1))
else cast( ' ' as char( 1))
end as qSelect1,
case
when sfx_slspid is not null and (
(sfx_orgid = 'AXIP' and sfx_pvdid='HARTFIELD')
then cast( '*' as char( 1))
else cast( ' ' as char( 1))
end as qPrvSlctd,
SFM_SLSPID as q_SLSPID,
SFM_SLID as q_SLID,
SFM_SLNAME as q_SLNAME,
SFM_STATUS as q_STATUS
from SfMast left outer join SfXref
on SfMast.SFM_SLSPID = SfXref.SFX_SLSPID
order by Q_SLSPID
) WITH DATA


Rob Berendt