On Nov 26, 11:45 pm, pal <jayadevpal..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
You have to use fields name instead of the *
you have exactly the problem Charles describe
Received on Tue Nov 27 2007 - 06:24:01 CST
