Hi! I'm new to using teradata, and was looking to create a view on a table, but my select statement contains a with condition as below, resulting in a 3706 error.
create view SANDBOX.JG_ORDERS_BY_BAN as
SELECT A1.CUSTOMER_BAN AS "BAN" ,A1.CREATOR_USERNM AS "USERNM" ,COUNT(*) AS "#_ORDERS"
FROM SAS_INSIGHT_VIEWS.orders ORDER A1
WHERE BAN IS NOT NULL
GROUP BY BAN,USERNM HAVING "#_ORDERS" > 2 WITH COUNT (USERNM) BY BAN ORDER BY CUSTOMER_BAN;
Note that the above works fine as a standard select statement, just when trying to create as a veiw. It creates for each BAN the number of orders (over two) a given user has created.
My questions are 1. What alternative ways are there of achieving this via a view? I've had a good read up, but couldn't find a workaround for views. 2. Is there a more elegant way of writing the above code? As I said, i'm new to teradata, so would appreciate any pointers from any dab hands out there! 3. What is the reason a count can't be in a view? is it because of the time it takes to process the count?
Any help, or pointers to articles that will help me help myself would be most appreciated! Thanks.