寫這麼就的SQL，都還沒遇到別名被占用的問題,在我寫了一個SQL:
SELECT
Y.CCX_INNERCODE2,
Y.COMPANYCODE4
FROM
(
SELECT
DISTINCT(A.PERSONALNUM5),
A.PERSONALNAME6,
COUNT(A.PERSONALNAME6) OVER(PARTITION BY A.PERSONALNUM5),
C.LASTSTE,
D.PREHOLE,
F.PASSHOMETIME,
F.PASSRATE,
J.DISHOMETIME,
J.DISRATE,
X.OTHERHOMETIME,
X.OTHERRATE
FROM
(
SELECT
CL.PERSONALNUM5 PERSONALNUM5,
CL.PERSONALNAME6 PERSONALNAME6
FROM CT_PERSONAL CL
) A
LEFT JOIN
(
SELECT
JIE.CODE CODE
FROM JYDB.LC_ISSUANCEEXAMINATION_SE JIE
) B ON A.PERSONALNUM5 = B.CODE
LEFT JOIN
(
SELECT
DISTINCT(JSA.MEMBERNAME),
JSA.MEMBERNUM MEMBERNUM,
CASE WHEN JSA.MEMBERNAME IS NOT NULL THEN MAX(JSA.MEETINGNUMBER) OVER(PARTITION BY JSA.MEETINGNUMBER) END AS LASTSTE--最後任職屆次
FROM JYDB.SM_CSCRMEMBER JSA --WHERE JSA.MEMBERNAME LIKE '%韓炯%'
) C ON C.MEMBERNUM = A.PERSONALNUM5
LEFT JOIN
(
SELECT
JSB.MEETINGNUMBER PREHOLE, --在頁面顯示時需要處理為:7,8,9..
JSB.MEMBERNUM MEMBERNUM,
JSB.MEMBERNAME MEMBERNAME
FROM JYDB.SM_CSCRMEMBER JSB
) D ON D.MEMBERNUM = A.PERSONALNUM5
LEFT JOIN
(
SELECT
BCX.INDEXCODE,
BCX.IND_PRO_CODE,
BCX.JY_INDEX_CODE
FROM BOND_CCX_INDEX BCX
) E ON E.JY_INDEX_CODE = A.PERSONALNUM5
LEFT JOIN
--會議審核通過(非個人表決)情況
(
SELECT
DISTINCT(W.HOMETIME) PASSHOMETIME,
W.INNERCODE21 INNERCODE21,
ROUND((W.HOMETIME/Z.ALLTIME),2) PASSRATE
FROM
(
SELECT
DISTINCT(LN.INNERCODE21) INNERCODE21,
COUNT(1) OVER(PARTITION BY (SELECT COUNT(1) FROM LC_ISSUANCEEXAMINATION)) HOMETIME
FROM LC_ISSUANCEEXAMINATION LN,BOND_CCX_INDEX BC
WHERE LN.IFPASSED19 = BC.IND_PRO_CODE AND BC.JY_INDEX_CODE = 1
) W LEFT JOIN
(
SELECT
DISTINCt(LN.INNERCODE21) INNERCODE21,
COUNT(1) OVER(PARTITION BY (SELECT COUNT(1) FROM LC_ISSUANCEEXAMINATION)) ALLTIME
FROM LC_ISSUANCEEXAMINATION LN,BOND_CCX_INDEX BC
WHERE LN.IFPASSED19 = BC.IND_PRO_CODE
) Z ON Z.INNERCODE21 = W.INNERCODE21
) F ON F.INNERCODE21 = E.INDEXCODE
LEFT JOIN
--會議審核未通過(非個人表決)情況
(
SELECT
DISTINCT(W.HOMETIME) DISHOMETIME,
W.INNERCODE21 INNERCODE21,
ROUND((W.HOMETIME/Z.ALLTIME),2) DISRATE
FROM
(
SELECT
DISTINCT(LN.INNERCODE21) INNERCODE21,
COUNT(1) OVER(PARTITION BY (SELECT COUNT(1) FROM LC_ISSUANCEEXAMINATION)) HOMETIME
FROM LC_ISSUANCEEXAMINATION LN,BOND_CCX_INDEX BC
WHERE LN.IFPASSED19 = BC.IND_PRO_CODE AND BC.JY_INDEX_CODE = 2
) W LEFT JOIN
(
SELECT
DISTINCt(LN.INNERCODE21) INNERCODE21,
COUNT(1) OVER(PARTITION BY (SELECT COUNT(1) FROM LC_ISSUANCEEXAMINATION)) ALLTIME
FROM LC_ISSUANCEEXAMINATION LN,BOND_CCX_INDEX BC
WHERE LN.IFPASSED19 = BC.IND_PRO_CODE
) Z ON Z.INNERCODE21 = W.INNERCODE21
) J ON J.INNERCODE21 = E.INDEXCODE
LEFT JOIN
--其他情況
(
SELECT
DISTINCT(W.HOMETIME) OTHERHOMETIME,
W.INITIALINFOPUBLDATE2 INITIALINFOPUBLDATE2,
W.INNERCODE21 INNERCODE21,
ROUND((W.HOMETIME/Z.ALLTIME),2) OTHERRATE
FROM
(
SELECT
DISTINCT(LN.INNERCODE21) INNERCODE21,
LN.INITIALINFOPUBLDATE2 INITIALINFOPUBLDATE2,
COUNT(1) OVER(PARTITION BY (SELECT COUNT(1) FROM LC_ISSUANCEEXAMINATION)) HOMETIME
FROM LC_ISSUANCEEXAMINATION LN,BOND_CCX_INDEX BC
WHERE LN.IFPASSED19 = BC.IND_PRO_CODE AND BC.JY_INDEX_CODE != 1 AND BC.JY_INDEX_CODE != 2
) W LEFT JOIN
(
SELECT
DISTINCt(LN.INNERCODE21) INNERCODE21,
COUNT(1) OVER(PARTITION BY (SELECT COUNT(1) FROM LC_ISSUANCEEXAMINATION)) ALLTIME
FROM LC_ISSUANCEEXAMINATION LN,BOND_CCX_INDEX BC
WHERE LN.IFPASSED19 = BC.IND_PRO_CODE
) Z ON Z.INNERCODE21 = W.INNERCODE21
) X ON X.INNERCODE21 = E.INDEXCODE
LEFT JOIN
(
SELECT
CSE.CCX_INNERCODE2,
CSE.COMPANYCODE4,
CSE.SECUCODE5,
CSE.SECUABBR6,
CSE.SECUMARKET12,
CSE.SECUCATEGORY13
FROM CCX_STOCK_INNERCODE CSE
) Y ON Y.CCX_INNERCODE2 = E.INDEXCODE
LEFT JOIN
(
SELECT
CL.INFOPUBLDATE2,
CL.WORKPLACE7,
CL.POSITIONNAME18,
CL.EDUCATION11,
CL.MAJORNAME17
FROM CT_PERSONAL CL
) N ON N.INFOPUBLDATE2 = X.INITIALINFOPUBLDATE2
)
上面報如下錯誤: ORA-06553: PLS-306:調用'OGC_Y'時參數個數或類型錯誤
剛開始我沒想到為什麼會報這樣的錯誤,只是感覺是不是別名的問題,然後我該成X後,就又出現:ORA-06553: PLS-306:調用'OGC_X'時參數個數或類型錯誤 提示錯誤。
這時候我就開始疑問了,為什麼會有這樣的錯誤的呢,奇怪,想想就去了GOOGLE，解決的基本思路就是:
首先我們可來看看這個OGC_Y到底是什麼呢:SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_NAME='OGC_X' 後得到如下: 1 MDSYS OGC_X FUNCTION,由這裡我們可以確定在系統中它早已有定義了,在下來,我們想了解的,它為什麼會被定義呢,:
SELECT OWNER, SYNONYM_NAME,TABLE_OWNER,TABLE_NAME FROM DBA_SYNONYM WHERE TABLE_NAME = 'OGC_X'：1 PUBLIC X MDSYS OGC_X 可以看出,REF(X)調用了'OGC_X'這個函數,即'OGC_X'指定了一個別名,然後我們查以下:SELECT OWNER,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME FROM DBA_SYSNONYM WHERE TABLE_NAME = 'Y' : 1 PUBLIC Y MDSYS OGC_Y
<=OK=> 我們終於看到的就是Y已經被占用了,所以我們可以再在官方查看以下詳細資料:Oracle Spatial is conformant with Open Geospatial Consortium (OGC) Simple Features Specification 1.1.1 (Document 99-049), starting with Oracle Database release 10g(version 10.1.0.4). Conformance with the SQL92 with Geometry Types Implementation means that Oracle Spatial supports all the types, functions, and language constructs detailed in Section 3.2 of the specification. Synonyms are created to match all OGC function names except for X(p Point) and Y(p Point). For these functions, you must use the names OGC_X and OGC_Y instead of just X and Y. 在這段中寫得很想詳細,所以我們可以用兩中方法來解決,一種就是另期一個別名,一個就是更改其系統設定的別名,OK,這個問題第一次遇到,並及時解決,很OK....所以這個問題說明我們在寫SQL是一個好的習慣是必要的,不管是在命名方面還是在甚至是在設計方面.....
補充:我把每個字母都測試了下:最終得到的是X與Y是被占用的,如果當你遇到時,可以把每個組合都測試下,或者把官方文檔弄下來好好的讀幾遍....,問題就幾乎沒有了
