| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Subquery in from clause
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
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
T1_C1
 |
 |