Hi,
Is it possible to concatenate columns and then count the distinct results?
I am using an SAP BI reporting tool (BusinessObjects Web Intelligence) with an OpenEdge 10.1C ODBC connection to a Progress database, but I'm unable to get the syntax correct to count a distinct concatenation of particular columns. Counting a concatenation of the columns is fine (SQL Extract "COUNT(Test.Batchno + Test.Sampleno + Test.Subsample + Test.Testgroup)" ) but when I try to count the DISTINCT (SQL Extract "COUNT(DISTINCT(Test.Batchno + Test.Sampleno + Test.Subsample + Test.Testgroup))" ) concatenation it produces an error [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error at or about %s(statement excerpt). (10713).
Cheers,
Rich
I don't know off hand if it is possible to do directly, but it sounds like the sort of thing for which a view would be the right solution.
Thanks for your reply Thomas, unfortunately I don't think a view is a possibility here.
The way in which the reporting tool works in that you create objects using SQL syntax (in a separte application called Universe Designer), link to the resulting 'Universe' of data objects, then drag these objects into the query panel. The reporting tool automatically generates the SQL based on the objects you add to the query. i.e: "COUNT(Test.Batchno + Test.Sampleno + Test.Subsample + Test.Testgroup)" would create an object that produces a count of the concatenated fields, sadly not a distinct count.
The count object I want to create needs to be a distnct count of these fields, to allow me to compare results against a similar concatenation of different fields. (Sorry if the above confuses you more, trying to easily describe what occurs in as few words as possible is proving difficult... hahaha)
The exact mechanism of the tool is not entirely clear to me .. but, then, you expected that! But, why can't you create a view in the OE database and then add that view to the universe of objects on which you can make queries. AFAIK, you can write any SQL against and OE view that you can write against a table.
A view would make life a little easier, unfortunately I have read-only access to the OE database, therefore I can't create a view. Requesting a change to the production database for something such as this would be a last resort, as the outage would effect 1000's of users Nationwide and isn't as simple as me just asking for it to happen. There are far too many hoops I'd have to jump through to get it done. I appreciate your suggestions though, thanks very much.
I don't think creating a view should create any outage or risk. It isn't going to impact any existing ABL program, which has no access to views anyway. And it isn't going to impact any SQL clients who aren't using it, which, by definition, is zero right now since you haven't defined the view.
I'd explore this in a test environment before you wrote it off by definition ... especially if you haven't found any other solutions.
Of course, solving it in ABL would be straightforward....