On Nov 26, 4:43 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Nov 26, 5:57 am, pal <jayadevpal..._at_gmail.com> wrote:
>
>
>
>
>
> > SELECT emp_master.loc_cd,
> > dept_master.dept_cd,
> > dept_master.dept_name,
> > Substr(dept_master.dept_cd,1,2) as cmain_dept,
> > desg_master.desg_name,
> > emp_master.l_name,
> > settlements.other_deductions,.....,
> > paywork.varern16,.....,
> > location.next_pay_date,
> > location.curr_dec
> > FROM (select * from paywork,settlements where paywork.emp_code(+)=
> > settlements.emp_code),
> > dept_master,
> > emp_master,
> > desg_master ,
> > user_location,
> > location;
>
> > I'm using the subquery to get the feilds from the two tables paywork
> > and settlements,
>
> > but wen i'm excecuting it gives an error like "Invalid column".. (the
> > columns paywork. and settlements.)
>
> > the subquery is retrieving all the feilds that i want but in I dont
> > know how to place it in FROM clause..
>
> > can any one tell me how to write the subquery in FROM clause
>
> > thanks and regards
>
> You have created an inline view, but have not provided an alias for
> the inline view. The columns returned by the inline view would need
> to be referenced by the inline view's alias. Before I show that, you
> will be running into another error, as demonstrated by the following:
> Created two tables, assume that these are simplified versions of your
> PAYWORK and SETTLEMENTS tables:
> CREATE TABLE T1(
> C1 VARCHAR2(20),
> C2 NUMBER(10));
>
> CREATE TABLE T2(
> C1 VARCHAR2(20),
> C2 NUMBER(10));
>
> Now, insert one row into each table:
> INSERT INTO
> T1
> VALUES(
> 'TEST',
> 10);
>
> INSERT INTO
> T2
> VALUES(
> 'TEST',
> 20);
>
> A simple select that joins the two tables:
> SELECT
> *
> FROM
> T1,
> T2
> WHERE
> T1.C1=T2.C1;
>
> C1 C2 C1 C2
> ----- ---- ----- ----
> TEST 10 TEST 20
>
> Sliding the simple select into an inline view:
> SELECT
> *
> FROM
> (SELECT
> *
> FROM
> T1,
> T2
> WHERE
> T1.C1=T2.C1) T1_T2;
>
> ERROR at line 2:
> ORA-00918: column ambiguously defined
>
> The C1 and C2 columns exist in both tables, as would the EMP_CODE
> columns in your SQL statement. The fix:
> SELECT
> *
> FROM
> (SELECT
> T1.C1 T1_C1,
> T1.C2 T1_C2,
> T2.C1 T2_C1,
> T2.C2 T2_C2
> FROM
> T1,
> T2
> WHERE
> T1.C1=T2.C1) T1_T2;
>
> T1_C1 T1_C2 T2_C1 T2_C2
> ------- ----- ------- -----
> TEST 10 TEST 20
>
> You will note in the above that I aliased the inline view as T1_T2, in
> addition to aliasing the columns returned from each tables, such as
> T1_C1. To refer to the columns in the inline view, I must use both
> the column alias and the inline view alias (unless I am only dealing
> with a single table or a single inline view, in which case I do not
> need to specify the table/view alias):
> SELECT
> T1_T2.T1_C1
> FROM
> (SELECT
> T1.C1 T1_C1,
> T1.C2 T1_C2,
> T2.C1 T2_C1,
> T2.C2 T2_C2
> FROM
> T1,
> T2
> WHERE
> T1.C1=T2.C1) T1_T2;
>
> T1_C1
> -------
> TEST
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -
thanks for the valid information , but still i have a problem.
in my subquery i want to select all fields.when i select all then
as you said the ORA-00918: column ambiguously defined error occurs
Received on Mon Nov 26 2007 - 22:45:24 CST
