AboutSuchitra Joshi Expertise I can answer questions regarding SQL, PL/SQL, Procedures, Functions, Triggers, SQL Loader and some basic dba activities or performance tuning for Oracle 8i database. I cannot answer questions related to major dba activities, backup and recovery.
Experience 10+ years D2K
Awards and Honors OCP - SQL and PL/SQL
Brainbench certifications in Oracle Administration, Database, PL/SQL
Expert: Suchitra Joshi Date: 6/11/2008 Subject: union syntax
Question how to fetch fields from three tables in a single statement
The Query(select a.*,b.*,s.cpp,s.windmill from anntmp a,anntmp1 b, servicemas s where s.circlecode=c1.circlecode and s.serviceno=c1.serviceno and a.serviceno=b.serviceno and b.serviceno=s.serviceno and a.circlecode=b.circlecode and b.circlecode=s.circlecode and a.ltorhtside=s.ltorhtside and LPAD(ltrim(rtrim(billmonth)),6,'0') =:bill.monyear) returns the error "alias name required to avoid dupilcate column names"
Answer Hi Savitha,
The error indicates that there are some duplicate column names in some of the 3 tables.
Then the above error will occur since -
- columns a1 and a2 are present in all tables tab1, tab2 and tab3
- column b1 is present in both tables tab2 and tab3
So use the following query in your case (not sure what c1 is) -
select a.serviceno a_serviceno,
a.circlecode a_circlecode,
a.ltorhtside a_ltorhtside,
b.serviceno b_serviceno,
b.circlecode b_circlecode,
s.ltorhtside s_ltorhtside,
s.cpp s_cpp,
s.windmill s_windmill
from anntmp a,anntmp1 b, servicemas s
where s.circlecode=c1.circlecode
and s.serviceno=c1.serviceno
and a.serviceno=b.serviceno
and b.serviceno=s.serviceno
and a.circlecode=b.circlecode
and b.circlecode=s.circlecode
and a.ltorhtside=s.ltorhtside
and LPAD(ltrim(rtrim(billmonth)),6,'0') =:bill.monyear
Here a_serviceno, a_circlecode, a_ltorhtside, b_serviceno, b_circlecode, s_ltorhtside are all alias names. You will have to give all column names from all tables in the select list (instead of *). Alias names can be any names similar to any column names.
