Toad World® Forums

Creating a new view from 2 tables but contains no data?


#1

The view has been created with the following statement. It all seems to work but there is no data there - do I need to specify the string?? Not sure what I’m doing here??

   CREATE OR REPLACE FORCE VIEW NEW_VIEW_VW
(
URL,
ORGANISATION,
CATEGORY,
SEARCH_1,
SEARCH_2
)
AS
SELECT DISTINCT,
TABLE1.SERV_URL,
TABLE2.ORGANISATION,
TABLE1.SEARCH_1,
TABLE1.SEARCH_2
FROM TABLE1,
TABLE2;
CREATE PUBLIC SYNONYM NEW_VW;
DROP PUBLIC SYNONYM NEW_VIEW_VW;
CREATE PUBLIC SYNONYM NEW_VIEW_VW;
GRANT SELECT ON NEW_VIEW_VW;

#2

Just an unrelated observation - you have a cartesian join in your view def - i.e. a join with no where clause. This is like having a GOTO in program code - never usually a good thing :frowning:


#3

Hi,

Please check your SELECT statement for DISTINCT clause. Or check whether your query is working or not.

On 06-Jan-2014 7:31 PM, “palmy12” bounce-palmy12@toadworld.com wrote:

Creating a new view from 2 tables but contains no data?

Thread created by palmy12

The view has been created with the following statement. It all seems to work but there is no data there - do I need to specify the string?? Not sure what I’m doing here??

CREATE OR REPLACE FORCE VIEW NEW_VIEW_VW

(

URL,

ORGANISATION,

CATEGORY,

SEARCH_1,

SEARCH_2

)

AS

SELECT DISTINCT,

TABLE1.SERV_URL,

TABLE2.ORGANISATION,

TABLE1.SEARCH_1,

TABLE1.SEARCH_2

FROM TABLE1,

      TABLE2;

CREATE PUBLIC SYNONYM NEW_VW;

DROP PUBLIC SYNONYM NEW_VIEW_VW;

CREATE PUBLIC SYNONYM NEW_VIEW_VW;

GRANT SELECT ON NEW_VIEW_VW;

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Oracle notifications altogether.

Toad World - Oracle Discussion Forum

Flag this post as spam/abuse.


#4

You are selecting 5 columns and specify only 4 in the view definition (CATEGORY is missing).

CREATE OR REPLACE FORCE VIEW creates the view, but you shouldn’t be able to use it.


#5

Hi,

I’ve been selected as a PL/SQL developer in chain sys India Pvt Ltd(chennai). I need to know about the Pl/sql developer scope in future and also need to knw abt chain sys company. Also, i knw some of the basics in java. Is it worthable to join in that organisation as pl/sql developer or else i’ll look for some java openings.

Any feedback will be greatly appreciated. They are also aaking for five year bond!!!


#6

Hi,

I’ve been selected as a PL/SQL developer in chain sys India Pvt Ltd(chennai). I need to know about the Pl/sql developer scope in future and also need to knw abt chain sys company. Also, i knw some of the basics in java. Is it worthable to join in that organisation as pl/sql developer or else i’ll look for some java openings.

Any feedback will be greatly appreciated. They are also aaking for five year bond!!!