jvera524
asked on
PL/SQL Create View with Union Statement
Hello I cannot this create view statement to work. I get an ORA-00998 error. When I run union query alone it works fine.
create view "FIN".BSRCombined as SELECT
pay.DOS,
pay.dispatchnumber,
pay.program,
pay.base,
pay.mileage_load Raw_Mileage,
'pay.BSR',
pay.dest_state,
pay.Transmitted
FROM
DSS.ALLPGMSFINWPMT pay,
DSS.CLIN cl
WHERE
pay.program not in (60,61,62,63,64,65,66,67,6
pay.patnum = cl.recordnum (+)
UNION
SELECT
zpt.DOS,
zpt.dispatchnumber,
zpt.program,
zpt.base,
zpt.mileage_load,
'zpt.0pt',
'zpt.dest_st',
to_number('99999')
FROM
DSS.ZEROBILLED zpt,
DSS.CLIN cl
WHERE
zpt.program not in (60,61,62,63,64,65,66,67,6
zpt.patnum = cl.recordnum (+)
Thank you in advance.
create view "FIN".BSRCombined as SELECT
pay.DOS,
pay.dispatchnumber,
pay.program,
pay.base,
pay.mileage_load Raw_Mileage,
'pay.BSR',
pay.dest_state,
pay.Transmitted
FROM
DSS.ALLPGMSFINWPMT pay,
DSS.CLIN cl
WHERE
pay.program not in (60,61,62,63,64,65,66,67,6
pay.patnum = cl.recordnum (+)
UNION
SELECT
zpt.DOS,
zpt.dispatchnumber,
zpt.program,
zpt.base,
zpt.mileage_load,
'zpt.0pt',
'zpt.dest_st',
to_number('99999')
FROM
DSS.ZEROBILLED zpt,
DSS.CLIN cl
WHERE
zpt.program not in (60,61,62,63,64,65,66,67,6
zpt.patnum = cl.recordnum (+)
Thank you in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The issue is really only with the 'pay.BSR' found in the first select. It shouldn't matter provided the column types are the same.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I thought for example using pay.dest_state and 'zpt.dest_st the column would be called dest_state.
Thanks I will try giving all those fields aliases.