获取 SQL 错误:ORA-00957:创建视图时列名重复 [英] Getting SQL Error: ORA-00957: duplicate column name, while creating view

问题描述

我正在尝试创建视图，但出现重复的列名错误.如果我单独运行 select 查询，则查询返回如下结果:

I am trying to make a view, but I am getting a duplicate column name error. If I run the select query separately, then the query returns a result like:

SELECT distinct app.APP_REF_NO, app.APP_STATUS, app.APP_DT, app.ATTEND_STAFF,
                app.ATTEND_BRANCH, app.PRODUCT_TYPE, cust.CUST_ID, 
                cust.APP_JOINT_T, cust.ID1_TYPE, cust.ID1, cust.ID2_TYPE, 
                cust.ID2, cust.FIRST_NAME, cust.LAST_NAME, cust.FULL_NAME, 
                cust.FULL_NAME_CAP, cust.DOB, fac.FACILITY_NO, fac.PRODUCT_TYPE,
                fac.PRODUCT_CODE, fac.MAIN_PROD_IND, fac.AMT_APPLIED
FROM 
    LOSA_APP app 
LEFT JOIN 
    LOSA_CUST cust 
ON
    cust.APP_REF_NO = app.APP_REF_NO
LEFT JOIN 
   LOSA_FACILITIES fac 
ON
    fac.APP_REF_NO = app.APP_REF_NO
LEFT JOIN 
    OS_CURRENTSTEP STEP 
ON
    STEP.REF_ID = app.APP_REF_NO
   WHERE (app.APP_STATUS ='P' OR app.APP_STATUS ='T' OR 
         ((app.APP_STATUS='R' OR app.APP_STATUS='S') AND STEP.STEP_NAME='011'));

这个查询工作正常.但是当我尝试将它作为一个视图运行时:

This query works fine. But when I try to run it as a view like:

CREATE VIEW basit_test1 AS
SELECT distinct app.APP_REF_NO, app.APP_STATUS, app.APP_DT, app.ATTEND_STAFF,
                app.ATTEND_BRANCH, app.PRODUCT_TYPE, cust.CUST_ID, 
                cust.APP_JOINT_T, cust.ID1_TYPE, cust.ID1, cust.ID2_TYPE, 
                cust.ID2, cust.FIRST_NAME, cust.LAST_NAME, cust.FULL_NAME, 
                cust.FULL_NAME_CAP, cust.DOB, fac.FACILITY_NO, fac.PRODUCT_TYPE,
                fac.PRODUCT_CODE, fac.MAIN_PROD_IND, fac.AMT_APPLIED
FROM 
    LOSA_APP app 
LEFT JOIN 
    LOSA_CUST cust 
ON
    cust.APP_REF_NO = app.APP_REF_NO
LEFT JOIN 
   LOSA_FACILITIES fac 
ON
    fac.APP_REF_NO = app.APP_REF_NO
LEFT JOIN 
    OS_CURRENTSTEP STEP 
ON
    STEP.REF_ID = app.APP_REF_NO
   WHERE (app.APP_STATUS ='P' OR app.APP_STATUS ='T' OR 
         ((app.APP_STATUS='R' OR app.APP_STATUS='S') AND STEP.STEP_NAME='011'));

然后我收到重复的列名错误.为什么我会收到此错误?

Then I get the duplicate column name error. Why am I getting this error?

推荐答案

您有两个 product_type 列:

fac.PRODUCT_TYPE

和

app.PRODUCT_TYPE

你应该别名其中之一，例如

you should alias one of them eg

app.PRODUCT_TYPE app_prod_type

这篇关于获取 SQL 错误:ORA-00957:创建视图时列名重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！