troubleshooting Question

PL/SQL Create View with Union Statement

Avatar of jvera524
jvera524 asked on
Oracle Database
4 Comments2 Solutions1168 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
ianmills2002

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros