Avatar of jvera524
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,68,71,73,74,82,85) and
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,68,71,73,74,82,85) and
zpt.patnum = cl.recordnum (+)

Thank you in advance.
Oracle Database

Avatar of undefined
Last Comment
johnsone

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
ianmills2002

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
jvera524

ASKER
Yes I need.

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.
ianmills2002

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
johnsone

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck