views and joins - HELP

Toad for Oracle
1

Hi,

I am new to Oracle but am somewhat familiar with SQL.

I have created two views and what to join them together this is what i have done so far. It is showing to many fields for the PIDM and should only show one. SGVSATT_ATTR_DESC is showing everything

View one (jcarr1)

SELECT SGVSATT_TERM_CODE,SGVSATT_PIDM FROM sgvsatt WHERE SGVSATT_PIDM = ‘2485’ and SGVSATT_TERM_CODE = ‘201400’;

view (jcarr3)

SELECT SGVSATT_ATTR_DESC,SGVSATT_ATTR_CODE FROM sgvsatt WHERE SGVSATT_PIDM = ‘2485’ and SGVSATT_ATTR_DESC = ‘%’ and SGVSATT_ATTR_CODE = ‘%’;

Select jcarr1.SGVSATT_PIDM, sgvsatt.SGVSATT_ATTR_DESC

FROM jcarr1, jcarr3, sgvsatt where jcarr1.SGVSATT_PIDM = jcarr3.SGVSATT_ATTR_DESC(+)

Can you help ?? Thanks in advance

Toad version 7.4.0.1

2

In your query:

Select jcarr1.SGVSATT_PIDM, sgvsatt.SGVSATT_ATTR_DESC

FROM jcarr1, jcarr3, sgvsatt where jcarr1.SGVSATT_PIDM = jcarr3.SGVSATT_ATTR_DESC(+)

No need to include sgvsatt in the FROM clause. You already have defined the SGVSATT_ATTR_DESC in the VIEW jcarr3. Moreover, you did not have any JOIN condition for sgvsatt and therefore your query will return cartesian product.