How to show descriprions from a count distinct on 1 row...

Barticchia

Hi to everyone...

i have an issue on a report and a new request to satisfy:

that's my scenario:

field 1 -  field 2

A               3

Field 2 is an "count(distinct code)"; it's mean that field "A" has 3 differents codes.

New request is to visualize on the same row, different codes concatenate each other with a " - "

for expample:

Field 1     -     codes concatenate        -       Field 2

   A      |   "pippo" - "pluto" - "paperino" |         3

I thought about union queries (in this case 3) but i don't know first of all how to identify each of those 3 count distinct (in this case; it's not static) in order to use an eventual case when statement.

Any idea?

Many thanks guys every little tips will be appreciated

Pedro F

Hi,

Assuming you're using an Oracle database (11g or higher),you can use something like this

Barticchia

Ciao Pedro!!!

First of all i'd like to thank you for your reply.

I forgot to tell that we use Netezza Database...

i don't think this function is applicable on Netezza... i will check.

thanks again

Joel

Try this

Barticchia

Ciao!!!

i'm testing this function on Netezza DB.

With sql it works fine and i see what i want.

Now i'm trying to use Evaluate_Aggr on Obiee but an error occur: this is the formula that i used:

evaluate_aggr('GROUP_CONCAT(%1,%2)' AS CHAR,"FE_F_CELLE"."CODIFICA_CELLA",'-')

and this is the error:

Odbc driver returned an error (SQLExecDirectW).

  Error Details

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 16001] ODBC error state: 42S02 code: 29 message: ERROR: Function 'GROUP_CONCAT(VARCHAR, UNKNOWN)' does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts. [nQSError: 16014] SQL statement preparation failed. (HY000)

SQL Issued: SELECT 0 s_0, "Inventory Rete Mobile"."FE_F_LOCALITA_DIRETTA"."CODIFICA_LOCALITA_RETE" s_1, evaluate_aggr('GROUP_CONCAT(%1,%2)' AS CHAR,"Inventory Rete Mobile"."FE_F_CELLE"."CODIFICA_CELLA",'-') s_2 FROM "Inventory Rete Mobile" FETCH FIRST 1000001 ROWS ONLY

if a copy sql from Manager session, and i paste it on Aginity tool; it works!!!! what a hell...

i really don't understand...

Do you have any idea reason of this problem???

this is sql generated:

select distinct 0 as c1,

     D1.c2 as c2,

     D1.c1 as c3

from

     (select GROUP_CONCAT(T8853123.CODIFICA_CELLA,'-') as c1,

               T8856923.CODIFICA_LOCALITA_RETE as c2

          from

                    FE_F_CELLE T8853123 /* A_RM_FE_F_CELLE */  full outer join (

                         FE_F_IMPIANTI T8853071 /* A_RM_FE_F_IMPIANTI */  left outer join

                         FE_F_LOCALITA T8856923 /* A_RM_FE_F_LOCALITA_DIRETTA */  On T8853071.CODICE_LOCALITA = T8856923.CODIFICA_LOCALITA_RETE) On T8853123.CODIFICA_IMPIANTO = substring(T8853071.IMPIANTO from 1 for 9)

          group by T8856923.CODIFICA_LOCALITA_RETE

     ) D1

Thanks for your help!!!

Joel

Is your connection pool using the same user that you connected to your Netezza DB to execute the SQL successfully? If the user is different, you'll need to prefix the GROUP_CONCAT function with the user.

For example: USER1.GROUP_CONCAT

Barticchia

Ciao!!! i found the problem...

in aginity tool, i have a DB version different then DB production.

I was trying report on OBIEE that target to DB production.

Following some web links, tells that we have to install that particular GROUP_CONCAT function in Netezza DB.

We're still waiting the answer from the manager.

Thanks all for you precious help

Simone