Can't use count with in a view...

Database

Can't use count with in a view...

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.
Tags (1)
4 REPLIES

Re: Can't use count with in a view...

In general, count is allowed in a view. It's the WITH clause that is not allowed. Also, ORDER BY is not allowed in a view. Those constructs are
disallowed because a view must define a relation.

Re: Can't use count with in a view...

Thanks Jim - i'll see if I can re work my view without those constructs. Much appreciated.
N/A

Re: Can't use count with in a view...

There are two ways to rewrite your query using Standard SQL, although both will result in a slightly different output.

#1:
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 GROUPING SETS((BAN,USERNM), (BAN))
HAVING "#_ORDERS" > 2

This returns a summary line for the count per ban like the WITH BY, but without
ORDER BY BAN, GROUPING(usernm), usernm
it's not really usefull.

#2:
SELECT A1.CUSTOMER_BAN AS "BAN"
,A1.CREATOR_USERNM AS "USERNM"
,COUNT(*) AS "#_ORDERS"
,sum(#_ORDERS) over (partition by BAN)
FROM SAS_INSIGHT_VIEWS.orders ORDER A1

WHERE BAN IS NOT NULL

GROUP BY BAN,USERNM
HAVING "#_ORDERS" > 2

Now the count per ban is an extra column instead of an extra row.

Dieter

Re: Can't use count with in a view...

Thanks Dieter - the second query will definitely come in handy. Much appreciated.