Ora-00918:column ambiguously defined error coping method

Recently do a project, often need to use a multiple table query, through the need for paging and so on, so often encounter

ora-00918:column ambiguously defined error coping method, meaning that a column definition is ambiguous, so

After a lot of testing, can be solved, especially when it comes to tablename.*, where the * is very easy to make such a mistake.

The following 2 SQL statements need to be carefully studied:

Select v1.*, (v1.data-v2.data) as rate, v3.*, (v3.data-v4.data) as Rate2

From (

Select RowNum as RN, a.* from (

Select Ayearmon, DATA, DATA-100 as rate

From Test

where isvalid=1 and code= ' 3503 '

and region = ' 000000 '

ORDER BY Ayearmon Desc

) a) V1,

(

Select (rownum-1) as RN1, b.* from (

Select Ayearmon, DATA

From Test

where isvalid=1 and code= ' 3503 '

and region = ' 000000 '

ORDER BY Ayearmon Desc

) b) V2,

(select RowNum as RN, c.* from (

Select Ayearmon, DATA, DATA-100 as rate

From Test

where isvalid=1 and code= ' 3502 '

and region = ' 000000 '

ORDER BY Ayearmon Desc

) c) V3,

(

Select (rownum-1) as RN1, d.* from (

Select Ayearmon, DATA

From Test

where isvalid=1 and code= ' 3502 '

and region = ' 000000 '

ORDER BY Ayearmon Desc

) d) V4

where V1.rn = v2.rn1 and V3.rn = v4.rn1 and V1.rn = V3.rn

and V1.rn >= 1 and V1.rn <= 10

And

SELECT * FROM (

Select v1.*, Case when substr (V1.ayearmon, 5, 2) = ' V1 ' THEN. DATA ELSE (v1. Data-v2. DATA) End as Dy

From (

Select RowNum as RN, a.* from (

Select Ayearmon, DATA

From v_yy_mac201

where isvalid=1 and code= ' 10011001 ' and length (ayearmon) = 6

and Timetype= ' b0301 ' and region = ' 000000 '

ORDER BY Ayearmon Desc

) a) V1,

(

Select (rownum-1) as RN1, b.* from (

Select Ayearmon, DATA

From v_yy_mac201

where isvalid=1 and code= ' 10011001 ' and length (ayearmon) = 6

and Timetype= ' b0301 ' and region = ' 000000 '

ORDER BY Ayearmon Desc

b) v2 where v1.rn = V2.rn1

)

where RN >= 1 and RN <= 50

Through the above 2 SQL, can find out what law.

Have a free time to continue the discussion.