PL/SQL Create View with Union Statement

jvera524
jvera524 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Firstly, should the single quotes be around 'pay.BSR', 'zpt.0pt' and 'zpt.dest_st'?

If not, remove them.

If yes, then these need to have an alias name so Oracle knows what to call the column in the view.

Author

Commented:
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.
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.
johnsoneSenior Oracle DBA
Commented:
If you are looking to name the columns in the view, then this is the way to do it:

CREATE VIEW "FIN".bsrcombined 
(dos, dispatchnumber, program, base, raw_mileage, bsr, dest_state, transmitted ) 
AS 
  SELECT pay.DOS, 
         pay.dispatchnumber, 
         pay.program, 
         pay.base, 
         pay.mileage_load Raw_Mileage, 
         NULL, 
         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, 
         NULL, 
         NULL, 
         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 (+) 

Open in new window


Putting dummy string values into fields is not going to accomplish anything other than data type mismatches.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial