Total Pageviews

2015/07/31

[Oracle to SQL Server Migration] An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Problem
Here is my original SQL statement in Oracle:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
SELECT B.MASTER_AGE,
       ORG_TYPE,
       B.AGE,
       SUM(DEBT_AMT) B_AMT,
       SUBSTR(B.AGE_NAME,1,3) AGE_NAME
FROM DBM032FA A,
     DBM030FA B
WHERE A.STA_DATE = B.STA_DATE
  AND TO_CHAR(B.STA_DATE, 'yyyy') = :qryYear + 1911
  AND A.AGE = B.AGE
  AND DEBT_TYPE = 'B'
GROUP BY ORG_TYPE,
         B.MASTER_AGE,
         B.AGE,
         B.AGE_NAME
ORDER BY org_type

Owing to I need to change database to Microsoft SQL Server. Therefore, the foregoing SQL statement will be translated as following: 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
SELECT B.MASTER_AGE,
       ORG_TYPE,
       B.AGE,
       SUM(DEBT_AMT) B_AMT,
       SUBSTRING(B.AGE_NAME,1,3) AGE_NAME
FROM DBM032FA A,
     DBM030FA B
WHERE A.STA_DATE = B.STA_DATE
  AND CONVERT(VARCHAR(4), MAX(B.STA_DATE), 112) = :qryYear + 1911
  AND A.AGE = B.AGE
  AND DEBT_TYPE = 'B'
GROUP BY ORG_TYPE,
         B.MASTER_AGE,
         B.AGE,
         B.AGE_NAME
ORDER BY org_type

But as I executed this SQL statement in SQL Sever, it throw this exception unexpectedly:
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.(除非彙總置於 HAVING 子句或選取清單所包含的子查詢中，且彙總的資料行為外部參考，否則不得在 WHERE 子句中出現。)

 Solution
Move CONVERT(VARCHAR(4), MAX(B.STA_DATE), 112) = :qryYear + 1911 from where clause to having clause:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
SELECT B.MASTER_AGE,
       ORG_TYPE,
       B.AGE,
       SUM(DEBT_AMT) B_AMT,
       SUBSTRING(B.AGE_NAME,1,3) AGE_NAME
FROM DBM032FA A,
     DBM030FA B
WHERE A.STA_DATE = B.STA_DATE
  AND A.AGE = B.AGE
  AND DEBT_TYPE = 'B'
GROUP BY ORG_TYPE,
         B.MASTER_AGE,
         B.AGE,
         B.AGE_NAME
HAVING CONVERT(VARCHAR(4), MAX(B.STA_DATE), 112) = :qryYear+ 1911
ORDER BY org_type


Reference
[1] http://blog.xuite.net/a88370.a88370/cjoushua/73695699-SQL+%E5%AD%B8%E7%BF%92%E7%AD%86%E8%A8%98-Having
[2] https://technet.microsoft.com/en-us//library/cc645611(v=sql.105).aspx
Posted by Albert Kuo at 2:08 PM
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)