Home » SQL & PL/SQL » SQL & PL/SQL » VIEW not showing all fields (oracle 10.1.2)
VIEW not showing all fields [message #358012] Fri, 07 November 2008 14:07 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi All
I had the following query to create a view and for some reason its only executing the first select statement. Any help is greatly appreciated.

create or replace view "dw_c_o_vw" 
(c_o_nbr,reas_t,chng_desc,c_o_t,c_o_amt,c_o_stat_t,appr_dt,cont_id)
AS
SELECT DISTINCT c_o_nbr,reas_t,chng_desc,c_o_t,c_o_amt,c_o_stat_t,appr_dt,cont_id as"cont_id_t_cont_co"
FROM DWSTAGE.STG_DSS_T_CONT_CO

UNION ALL

SELECT DISTINCT NULL,NULL,NULL,NULL,NULL,NULL,NULL,CONT_ID
FROM DWSTAGE.STG_DSS_T_CONT

UNION ALL

SELECT DISTINCT NULL,NULL,NULL,NULL,NULL,NULL,NULL,CONTID
FROM DWSTAGE.STG_TPLC_CONTRACT
Thanks
Re: VIEW not showing all fields [message #358015 is a reply to message #358012] Fri, 07 November 2008 14:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: VIEW not showing all fields [message #358030 is a reply to message #358012] Fri, 07 November 2008 15:16 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
aarti81 wrote on Fri, 07 November 2008 15:07

create or replace view "dw_c_o_vw" 



<sigh>Some people wish to make it so difficult. Now you and every other will have to refer to the view in double quotes every time you wish to use it.</sigh>
Re: VIEW not showing all fields [message #358035 is a reply to message #358012] Fri, 07 November 2008 17:00 Go to previous message
samtajain
Messages: 7
Registered: November 2008
Junior Member
That's a good suggestion.

Remove the quotes as part of standards.

Also you might not have compiled the view properly.

Here are options you have
1. Whichever tool you are compiling remove the space between the select statements and then compile it...its nothing like the space is an issue but may be when you compile you do not select everything.

2. Put it in a sql or txt file and compile it through sqlplus using @<location of file><filename>.<ext>

It should work.

Previous Topic: data split by range
Next Topic: select , into
Goto Forum:
  


Current Time: Sun Dec 04 10:41:28 CST 2016

Total time taken to generate the page: 0.08878 seconds