列定义不明确错误.不分页工作

Question

我只想弄清楚下面的查询出了什么问题：-如果我们去掉分页Rownum部分，它就可以正常工作。提到的是以下查询：-我们已经尝试过删除分页，它有效，但我们无法使其能够处理分页及其给出的模糊性错误。我还可以看到所有列都有它们的tableNames

--

SELECT *
FROM
(SELECT a.* ,
ROWNUM rnum
FROM
(WITH TEMP_DCS AS (SELECT ACCOUNT_CYCLE_INV_DATE.ACCT_ID, ACCOUNT_CYCLE_INV_DATE.HIERARCHY_SOURCE, 
ACCOUNT_CYCLE_INV_DATE.INV_DATE, ACCOUNT_CYCLE_INV_DATE.INVOICE_NUM, ACCOUNT_CYCLE_INV_DATE.DATA_GROUP, ACCOUNT_CYCLE_INV_DATE.BILL_SYSTEM_ID 
FROM
ACCOUNT_CYCLE_INV_DATE_302345 ACCOUNT_CYCLE_INV_DATE
WHERE DATA_GROUP=20180118
AND BILL_SYSTEM_ID = 6)
/* Formatted on 4/25/2018 2:26:23 PM (QP5 v5.115.810.9015) */
SELECT 
/*+ leading(TEMP_DCS DIM_HIERARCHY FACT_TAX) full(TEMP_DCS)  */
DIM_HIERARCHY.ACCT_LBL,
DIM_HIERARCHY.ACCT_DESC,
DIM_HIERARCHY.ACCT_FMT,
DIM_HIERARCHY.ACCT_ID,
DIM_LOCATION.ADDR1,
DIM_LOCATION.ADDR2,
DIM_LOCATION.ADDR3,
DIM_LOCATION.ADDR4,
DIM_LOCATION.ADDR5,
DIM_HIERARCHY.ADMIN_ID,
DIM_HIERARCHY.AGCY_HIER_CD,
DIM_HIERARCHY.AGCY_HIER_NM,
DIM_HIERARCHY.AGGREGATION_ATTR ,
DIM_HIERARCHY.AGGLVLTYPE,
DIM_HIERARCHY.AGGRFMT AS AGGLVLID,
DIM_HIERARCHY.HPID,
DIM_HIERARCHY.AGGRLBL,
DIM_HIERARCHY.AGGR_CUSTOM_LABEL,
REF_BPI_PROVIDER.AREA_ID,
REF_BPI_PROVIDER.BC_DESC,
DIM_HIERARCHY.WORK_CIRCUIT_ID_1,
REF_BPI_PROVIDER.CB_DESC,
REF_BPI_PROVIDER.CHANGE_BY,
REF_BPI_PROVIDER.CHANGE_DT,
FACT_TAX.CHARGE_GROUP_ID,
REF_BPI_PROVIDER.CIC,
DIM_LOCATION.CITY,
FACT_TAX.CNTNUMBER,
REF_BPI_PROVIDER.COMMENTS,
DIM_HIERARCHY.COMM_SVC_AUTH_NB,
FACT_TAX.SUM_AMOUNT_TAX,
FACT_TAX.TAX_GROUP,
DIM_HIERARCHY.FRGN_ACC_1,
DIM_HIERARCHY.FRGN_ACC_2,
DIM_HIERARCHY.GROUPTYPE,
DIM_HIERARCHY.GROUP_LBL,
DIM_HIERARCHY.GROUP_FMT,
DIM_HIERARCHY.GROUP_DESC,
DIM_HIERARCHY.GROUP_ID,
DIM_LOCATION.HS_LOCATION_ID,
DIM_LOCATION.HS_LOCATION_NAME,
FACT_TAX.INTERNATIONAL_TAX_AMT,
DIM_HIERARCHY.INV_CUST_NUM,
FACT_TAX.INVOICE_NUM,
FACT_TAX.INV_DATE,
DIM_HIERARCHY.IP_SRV_ACC_NO,
FACT_TAX.LOCID,
DIM_HIERARCHY.MRK_AREA,
DIM_LOCATION.MOW_CNTRYID,
FACT_TAX.MOW_TAX_PERCENTAGE,
DIM_HIERARCHY.MSTR_ACCT_NBR,
REF_BPI_PROVIDER.ORIG_COMP_CD,
DIM_HIERARCHY.ORIG_SYSACCT_ID,
DIM_HIERARCHY.ORIG_SYSACCT_CD,
DIM_HIERARCHY.PRODUCT_FAMILY_ID,
REF_PRODUCT_FAMILY.PRODUCT_FAMILY,
REF_PRODUCT_FAMILY.PRODUCT_FAMILY_DESC,
TEMP_DCS.ACCT_ID,
TEMP_DCS.HIERARCHY_SOURCE,
TEMP_DCS.INV_DATE,
TEMP_DCS.INVOICE_NUM,
TEMP_DCS.BILL_SYSTEM_ID,
DIM_SUB_ADDL_INFO.SUB_ACCT_ATTR1,
DIM_HIERARCHY.PON,
DIM_HIERARCHY.PORTID,
DIM_HIERARCHY.PORTID1,
DIM_HIERARCHY.PROVIDER_ID,
REF_BPI_PROVIDER.PSEUDO_CIC,
REF_PROD_TYPE.PRODUCT_TYPE_ID,
REF_PROD_TYPE.PRODUCT_TYPE_DESC AS PTDESC,
DIM_HIERARCHY.PRODUCT_TYPE_ID,
DIM_HIERARCHY.RAO,
DIM_HIERARCHY.REGN_CD,
REF_BPI_PROVIDER.SBC_AFFL_IND,
DIM_HIERARCHY.SBU_DESC,
DIM_HIERARCHY.SBU_FMT,
DIM_HIERARCHY.SBU_LBL,
DIM_LOCATION.SERVICE_CTR,
DIM_HIERARCHY.SERVICE_FMT,
DIM_HIERARCHY.SERVICE_FMT2,
DIM_HIERARCHY.SERVICE_FMT3,
DIM_HIERARCHY.SERVICE_FMT4,
DIM_HIERARCHY.SERVICE_ID,
DIM_HIERARCHY.SERVICE_LBL,
DIM_LOCATION.SITE_ALIAS,
DIM_LOCATION.SITE_ID,
DIM_HIERARCHY.SRC_BILLER,
REF_BPI_PROVIDER.STATUS_CD,
REF_STCNTRY.STCNTRYTXT,
FACT_TAX.STCNTRY_ID,
DIM_HIERARCHY.SUBACCTYPE,
DIM_HIERARCHY.SUB_DEP_ID,
DIM_HIERARCHY.SUB_ACCT_ID,
DIM_HIERARCHY.SVCARRTYPE,
REF_BPI_PROVIDER.SVC_PRVDR_DESC,
REF_TAX.TAX_DESC AS TAXDESC,
FACT_TAX.TAX_SURCG_IND,
FACT_TAX.TAX_TYPE_CD,
DIM_HIERARCHY.LEG_TCCUSTID,
REF_TRANS_CODE.TRANS_CODE_DESC,
DIM_HIERARCHY.LEG_TCINVID,
FACT_TAX.TEL_PROVIDER_CD,
FACT_TAX.TOT_TAX_TRANS,
FACT_TAX.TOT_TAX_WITHLD,
DIM_LOCATION.TO_END_USR_CITY,
DIM_LOCATION.TO_END_USR_CUST_NAME,
DIM_LOCATION.TO_END_USR_STATE,
DIM_LOCATION.TO_END_USR_STREET,
DIM_LOCATION.TO_END_USR_ZIP,
FACT_TAX.TRANSCODE_ID,
FACT_TAX.MOW_TAX_WORDING,
DIM_HIERARCHY.WORK_CIRCUIT_ID_2,
DIM_HIERARCHY.WORK_CIRCUIT_ID_3,
DIM_HIERARCHY.WORK_CIRCUIT_ID_4,
DIM_HIERARCHY.WORK_CIRCUIT_ID_5,
DIM_HIERARCHY.WORK_CIRCUIT_ID_6,
DIM_HIERARCHY.WORK_CIRCUIT_ID_7,
DIM_HIERARCHY.WORK_CIRCUIT_ID_8,
DIM_LOCATION.ZIP_CODE,
DIM_HIERARCHY.BUNDLE_FAN,
REF_CHARGE_GROUP.CHARGE_GROUP_ID,
REF_CHARGE_GROUP.CHARGE_GROUP_DESC,
DIM_HIERARCHY.DEP_ID,
DIM_HIERARCHY.DLCI,
DIM_HIERARCHY.DLCI1,
REF_BPI_PROVIDER.EBAT_DESC,
REF_BPI_PROVIDER.EDI_DESC,
DIM_LOCATION.EOL_NUM,
FACT_TAX.CURRENCY_CD,
FACT_TAX.FFP_TAX_INC_IND,
FACT_TAX.BAND,
REF_PROD_TYPE.PRODUCT_TYPE_CODE,
REF_CHARGE_GROUP.CHARGE_GROUP_CODE,
REF_TRANS_CODE.TRANS_CODE,
DIM_HIERARCHY.BILLER_SERVICE,
DIM_LOCATION.LOCATION_ADDRESS
FROM DIM_HIERARCHY,
REF_BPI_PROVIDER,
FACT_TAX,
DIM_LOCATION,
REF_PRODUCT_FAMILY,
TEMP_DCS,
DIM_SUB_ADDL_INFO,
REF_PROD_TYPE,
REF_STCNTRY,
REF_TAX,
REF_TRANS_CODE,
REF_CHARGE_GROUP
WHERE DIM_HIERARCHY.ACCT_ID         = TEMP_DCS.ACCT_ID
AND DIM_HIERARCHY.BILL_SYSTEM_ID    = TEMP_DCS.BILL_SYSTEM_ID
AND DIM_HIERARCHY.BILLER_SERVICE    = TEMP_DCS.HIERARCHY_SOURCE
AND FACT_TAX.INVOICE_NUM            =TEMP_DCS.INVOICE_NUM
AND FACT_TAX.BILL_SYSTEM_ID         = TEMP_DCS.BILL_SYSTEM_ID
AND FACT_TAX.INVOICE_NUM            = TEMP_DCS.INVOICE_NUM
AND FACT_TAX.INV_DATE               = TEMP_DCS.INV_DATE
AND DIM_HIERARCHY.BILL_HIERARCHY_ID = FACT_TAX.BILL_HIERARCHY_ID
AND DIM_HIERARCHY.ACCT_PART_KEY     = FACT_TAX.ACCT_PART_KEY
AND DIM_HIERARCHY.BILL_SYSTEM_ID    = FACT_TAX.BILL_SYSTEM_ID
AND DIM_HIERARCHY.BILL_SYSTEM_ID    = REF_PRODUCT_FAMILY.BILL_SYSTEM_ID
AND DIM_HIERARCHY.PRODUCT_FAMILY_ID = REF_PRODUCT_FAMILY.PRODUCT_FAMILY_ID
AND DIM_HIERARCHY.BILL_SYSTEM_ID    = REF_PROD_TYPE.BILL_SYSTEM_ID
AND DIM_HIERARCHY.PRODUCT_TYPE_ID   = REF_PROD_TYPE.PRODUCT_TYPE_ID
AND DIM_HIERARCHY.BILL_SYSTEM_ID    = REF_TRANS_CODE.BILL_SYSTEM_ID
AND FACT_TAX.TRANSCODE_ID           = REF_TRANS_CODE.TRANS_CODE_ID
AND DIM_HIERARCHY.BILL_SYSTEM_ID    = REF_CHARGE_GROUP.BILL_SYSTEM_ID
AND FACT_TAX.CHARGE_GROUP_ID        = REF_CHARGE_GROUP.CHARGE_GROUP_ID
AND FACT_TAX.BILL_SYSTEM_ID         = REF_STCNTRY.BILL_SYSTEM_ID
AND FACT_TAX.STCNTRY_ID             = REF_STCNTRY.STCNTRY_ID
AND FACT_TAX.TAX_ID                 = REF_TAX.TAX_ID
AND FACT_TAX.BILL_SYSTEM_ID         = REF_TAX.BILL_SYSTEM_ID
AND DIM_HIERARCHY.ACCT_ID           = DIM_LOCATION.ACCT_ID (+)
AND DIM_HIERARCHY.BILL_SYSTEM_ID    = DIM_LOCATION.BILL_SYSTEM_ID (+)
AND DIM_HIERARCHY.SERVICE_ID        = DIM_LOCATION.SERVICE_ID (+)
AND DIM_HIERARCHY.ACCT_PART_KEY     = DIM_LOCATION.ACCT_PART_KEY (+)
AND DIM_HIERARCHY.BILL_SYSTEM_ID    = REF_BPI_PROVIDER.BILL_SYSTEM_ID (+)
AND DIM_HIERARCHY.PROVIDER_ID       = REF_BPI_PROVIDER.SVC_PRVDR_ID (+)
AND DIM_HIERARCHY.ACCT_ID           = DIM_SUB_ADDL_INFO.ACCT_ID (+)
AND DIM_HIERARCHY.GROUP_ID          = DIM_SUB_ADDL_INFO.GROUP_ID (+)
AND DIM_HIERARCHY.SUB_ACCT_ID       = DIM_SUB_ADDL_INFO.SUB_ACCT_ID (+)
AND DIM_HIERARCHY.BILL_SYSTEM_ID    = DIM_SUB_ADDL_INFO.BILL_SYSTEM_ID (+)
ORDER BY TEMP_DCS.ACCT_ID, TEMP_DCS.INVOICE_NUM, TEMP_DCS.INV_DATE, DIM_HIERARCHY.GROUP_ID, DIM_HIERARCHY.SUB_ACCT_ID, DIM_HIERARCHY.SERVICE_ID) a) WHERE rnum  > 0
and rnum <= 10000

Answer 1

您的内部查询有多个名称相同的列，尽管它们来自不同的表；这些似乎是：

ACCT_ID (from DIM_HIERARCHY and TEMP_DCS)
CHARGE_GROUP_ID
INV_DATE
INVOICE_NUM
PRODUCT_TYPE_ID

外部查询无法处理这种歧义。您需要对冲突列进行别名，以便名称在外部查询中是唯一的。

---

作为问题的简单演示：

SELECT *
FROM
(SELECT a.* ,
ROWNUM rnum
FROM
(
-- replacing your entire inner query with somethign simple
select dummy, dummy from dual
) a) WHERE rnum  > 0
and rnum <= 10000;
SQL Error: ORA-00918: column ambiguously defined
00918. 00000 -  "column ambiguously defined"

如果你给列取别名，问题就会消失：

SELECT *
FROM
(SELECT a.* ,
ROWNUM rnum
FROM
(
-- replacing your entire inner query with somethign simple
select dummy as dummy1, dummy as dumym2 from dual
) a) WHERE rnum  > 0
and rnum <= 10000;
D D       RNUM
- - ----------
X X          1

Answer 2

您希望分页查询(如果使用rownum(的形式为：

select * 
from ( select /*+ FIRST_ROWS(n) */ 
a.*, ROWNUM rnum 
from ( your_query_goes_here, 
with order by ) a 
where ROWNUM <= 
:MAX_ROW_TO_FETCH ) 
where rnum  >= :MIN_ROW_TO_FETCH;

你似乎把上限和下限都放在了同一个地方(还有其他事情(。您有：

SELECT *
FROM
(SELECT a.* ,
ROWNUM rnum
FROM
(
select ...
ORDER BY TEMP_DCS.ACCT_ID, TEMP_DCS.INVOICE_NUM, TEMP_DCS.INV_DATE, DIM_HIERARCHY.GROUP_ID, DIM_HIERARCHY.SUB_ACCT_ID, DIM_HIERARCHY.SERVICE_ID) a) 
WHERE rnum  > 0 and rnum <= 10000

你可能想要：

select * 
from ( select a.*, ROWNUM rnum 
from ( select ... from ... ORDER BY ...) a 
where ROWNUM <= 10000 ) 
where rnum  >= 0;