Create View with self-joins and same column multiple times

[Chimaeron]

Hello.

I want to execute the following statement in order to create a view:

Code: Select all

CREATE VIEW DPT_TEXTSET_TF AS
SELECT
   TXT_TF.ID AS ID,
   TXT_TF.NAME AS NAME,
   TF_F.FUNKTIONSTEXT AS FUNKTION,
   TF_KB.BEZEICHNUNG AS KURZBEFUND,
   TF_B.BEZEICHNUNG AS BEFUND,
   TF_A1.BEZEICHNUNG AS ABHILFESTAND,
   TF_A2.BEZEICHNUNG AS ABHILFEFAHRT
FROM
   TXT_TF,
   FUNKTION TF_F,
   TEXTE40 TF_KB,
   TEXTEMEMO TF_B,
   TEXTEMEMO TF_A1,
   TEXTEMEMO TF_A2
WHERE
   TF_F.FUNKTIONS_ID(+) = TXT_TF.FUNKTIONS_ID AND

   TF_KB.TE_TYP_ID = TXT_TF.TE_TYP_ID AND
   TF_KB.ID = @NULLVALUE(TXT_TF.KURZBEFUNDID,0) AND

   TF_B.TE_TYP_ID = TXT_TF.TE_TYP_ID AND
   TF_B.ID = @NULLVALUE(TXT_TF.BEFUNDID,0) AND

   TF_A1.TE_TYP_ID = TXT_TF.TE_TYP_ID AND
   TF_A1.ID = @NULLVALUE(TXT_TF.ABHILFESTANDID,0) AND

   TF_A2.TE_TYP_ID = TXT_TF.TE_TYP_ID AND
   TF_A2.ID = @NULLVALUE(TXT_TF.ABHILFEFAHRTID,0);

Unfortunately I get the following output message:

Code: Select all

Error: 00316 EXE CST Column BEZEICHNUNG specified twice

Although I understand the actual error message I don't understand why I cannot create the view. The SELECT-statement (without the CREATE VIEW-statement) works fine.

Any suggestions?

Thx a lot!

[Mike Vandine]

Hi,

Selects don't work the same as when creating a view. From the SQL Language Guide:

Syntax:

CREATE VIEW view name
(column name, column name, ...)
AS select...

Clauses:

column name

Specify column names if you want to give different names to the columns in the view.
If you do not specify column names, the columns of the view have the same names as
those of the result table of the SELECT command.
If the results of the SELECT command have duplicate column names (as can occur
with a join), or if a column is derived from a function or arithmetic expression, you
must give names to all the columns in the view. The new column names have to
appear in parenthesis after the view name.

Example:

CREATE VIEW DEPT_SAL (DEPT, TOTSAL) AS
SELECT DEPTNO, SUM(SALARY) FROM EMP, EMPSAL WHERE
EMP.EMPNO = EMPSAL.EMPNO GROUP BY DEPTNO;

So, in your case, since you're using duplicate column names, you need to specify ALL the names in the create view statement, i.e.

create view dpt_textset_tf (ID, NAME, FUNKTION, KURZBEFUND, BEFUND...)

Then I believe you can take out the 'AS' statements in the select so just have the

SELECT TXT_TF.ID,
TXT_TF.NAME,
...

Hope this helps!

Best regards,

[Chimaeron]

Hope this helps!

It did help!

Thank you very much!