Re: Subquery in from clause

On Nov 27, 5:24 pm, chris <cboi..._at_jlr.ca> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

yeah it is workin, it was my fault that in select i selected the same field twice, it was difficult to find out cos the select contains more fields..

Thanks Charls ,thanks Chris Received on Tue Nov 27 2007 - 22:35:32 CST