I am trying to build a simple piece of code that gives the count of customers by the number of accounts they hold. The Teradata SQL to do this looks something like this.
select dt.total_accounts, count(*) from
(select
distinct cust_no,
count(*)
as total_accounts
from
Cust_accts
group by 1
) as dt
group by dt.total_accounts
I don’t believe that we can get BO (Version 5) to build derived tables
so any thoughts on other ways of building the same.
I have tried defining the count as both a metric and a dimension and selecting both and then putting a break on the dimension object so I could aggregate at that level. No luck.
Are you trying to do this in Designer or Reporter? You should be able to put that code in the Select of an object in Designer, but I don’t of any way to accomplish this in Reporter without writing freehand SQL.
Not concerned if I do it in Reporter or Designer at this stage.
Not sure what you mean by putting the code in a select statement of an object. do you mean that I should be able to put the whole select including the ref to the derived table in the object. Tried this and Reporter says Query does not reference a table when I try to use it.
I believe that version 6.5 is supposed to support derived tables. Until then, you can create your sub-query as a view, reference that view in Designer, and then build the appropriate objects. Is that an option for you?
I’m not sure if I can run with a view on this as I really need to pass parameters through to the derived table and I don’t think that implementing it as a view will let me do this. I have cut it into Free-Hand SQL and at least get the results I want albeit without the flexibility I’d like. Guess I’ll have to wait for 6.5.
(BOB member since 2002-08-08)
(BOB member since 2002-06-06)