December 22, 2008 at 9:39 am
SELECT O.ORDERID, c.FIRSTNAME, c.LASTNAME,
'' AS CREDITCARDNUMBER, '' AS COUNTRY,
'Soundex' AS GROUPTYPE
FROM
ORDERS O JOIN CUSTOMER C ON O.CUSTOMERID=C.CUSTOMERID
WHERE ORDERDATE BETWEEN min(orderdate) AND max(orderdate)
AND O.CUSTOMERID IN
(SELECT INT1 FROM BRADTEMP WHERE CHAR1 IN
(SELECT CHAR1
FROM BRADTEMP
GROUP BY CHAR1
HAVING COUNT(INT2) > 3))
UNION
SELECT O.ORDERID, c.FIRSTNAME, c.LASTNAME, CREDITCARDNUMBER,
'' AS COUNTRY, 'Credit Card' AS GROUPTYPE
FROM CREDITCARDPAYMENT CCP LEFT JOIN
ORDERS O ON O.ORDERID=CCP.ORDERID LEFT JOIN
CUSTOMER C ON O.CUSTOMERID=C.CUSTOMERID
WHERE O.ORDERDATE between min(orderdate) AND max(orderdate)
AND LEFT(REPLACE(CCP.CREDITCARDNUMBER,' ',''),12) IN
(SELECT LEFT(REPLACE(CREDITCARDNUMBER,' ',''),12) AS CCNUMBER
FROM CREDITCARDPAYMENT CCP LEFT JOIN
ORDERS O ON O.ORDERID=CCP.ORDERID
WHERE LTRIM(RTRIM(ISNULL(CREDITCARDNUMBER,'')))<>''
AND O.ORDERDATE BETWEEN min(orderdate) AND max(orderdate)
GROUP BY LEFT(REPLACE(CREDITCARDNUMBER,' ',''),12)
HAVING COUNT(DISTINCT CCP.ORDERID) > 2)
UNION
SELECT ORDERID, c.FIRSTNAME, c.LASTNAME,
'' AS CREDITCARDNUMBER, COUNTRY, 'Country' AS GROUPTYPE
FROM CUSTOMER C LEFT JOIN
CUSTOMERADDRESS CA ON C.CUSTOMERID=CA.CUSTOMERID LEFT JOIN
ADDRESS A ON CA.ADDRESSID=A.ADDRESSID JOIN
ORDERS O ON C.CUSTOMERID=O.CUSTOMERID
WHERE O.ORDERDATE BETWEEN min(orderdate) AND max(orderdate)
AND (A.COUNTRY IN
('IRAN','GHANA', 'NIGERIA', 'MAURITANIA', 'MALI', 'BENIN', 'GUINEA', 'SENEGAL') OR
A.COUNTRY LIKE 'COTE D%')
and orderid not in (
select orderid from Production_Warehouse.dbo.Staging_Fraud)
ORDER BY GROUPTYPE, O.ORDERID
please help i am getting following error:
Msg 147, Level 15, State 1, Line 1
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.
December 22, 2008 at 9:56 am
WHERE ORDERDATE BETWEEN min(orderdate) AND max(orderdate)
This part of the query is culprit.
You just need to omit this condition. I guess u need to run this query on all records.
December 22, 2008 at 10:44 am
ps (12/22/2008)
WHERE ORDERDATE BETWEEN min(orderdate) AND max(orderdate)
This part of the query is culprit.
You just need to omit this condition. I guess u need to run this query on all records.
OR move it into "having", Like:
GROUP BY ....
HAVING ORDERDATE BETWEEN min(orderdate) AND max(orderdate)
* Noel
December 23, 2008 at 1:32 am
Or maybe MIN(ORDERDATE) and MAX(ORDERDATE) are meant as external parameters of the query and should in fact be @min_orderdate and @max_orderdate? You didn't explain anything about your query, so we can only guess what you want to do...
Because, unless I'm missing something, any orderdate will always be between MIN and MAX, so why to put it there?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply