Summarize IN-DB does not work

[Haotian]

Hi Community.

I am a newbie for Alteryx. I am trying to use Summarize in-db to group by string and count distinct other variables. But it will not give me any error message and results. There are no results to show up. 

Now I am confused and need help.

Thanks

Haotian

[atcodedog05]

Hi @Haotian ,

can you show the complete error message.

[atcodedog05]

And try trial and error by keeping only grouby and removing count distinct and vise versa only distinct and no groupby.

[Haotian]

Start: Designer x64: Started running at 09/30/2020 13:05:26
Info: Browse In-DB (6): Running In-DB query
Error: Browse In-DB (6): DataWrapOCI: Unable to prepare the query: "WITH "Tool2_7d4d" AS (select UIIDWP01.V_T_F_ACCT_TXN.ACCOUNT_PROFILE_KEY,
UIIDWP01.V_T_D_TXN_TYPE.UII_DESCRIPTION
,UIIDWP01.V_T_F_ACCT_TXN.AMOUNT
from UIIDWP01.V_T_D_TXN_TYPE
inner join UIIDWP01.V_T_F_ACCT_TXN on UIIDWP01.V_T_F_ACCT_TXN.TXN_TYPE_KEY = UIIDWP01.V_T_D_TXN_TYPE.TXN_TYPE_KEY
where Lower(UIIDWP01.V_T_D_TXN_TYPE.UII_DESCRIPTION) Like '%gift%'
and UIIDWP01.V_T_D_TXN_TYPE.TRAUNCH_ID < '100050'
and UIIDWP01.V_T_F_ACCT_TXN.TIME_KEY Between 20191001
and 20194365), "Tool3False_151b" AS (SELECT * FROM "Tool2_7d4d" WHERE NOT ("UII_DESCRIPTION" LIKE '%ACH%')), "Tool4_2533" AS (SELECT "UII_DESCRIPTION", COUNT(DISTINCT "ACCOUNT_PROFILE_KEY") AS "CountDistinct_ACCOUNT_PROFILE_KEY" FROM "Tool3False_151b" GROUP BY "UII_DESCRIPTION") SELECT * FROM "Tool4_2533" WHERE ROWNUM <= 100" Error: ORA-00972: identifier is too long

File_Output: Browse In-DB (5): C:\Users\hwu\AppData\Local\Temp\Engine_20088_._AlteryxInDBBrowseCache_f9c0654ca71265172799365a5be1e226.yxdb|In-DB query completed in 0.2 seconds returning 100 records
End: Designer x64: Finished running in 1.9 seconds with 1 error

[atcodedog05]

Hi @Haotian ,

I got a hunch

Rename the output field name of summarize

Yellow highlighted

[Haotian]

If I remove distinct, it seems working. It might be related to the account profile key recognized as numerical instead of string

[atcodedog05]

Try renaming

[atcodedog05 · Accepted solution]

Make it only count or so

[Haotian]

yeah. I changed the name and it did not work for distinct. If I use count or sum, it works well

[atcodedog05]

Only count Distinct also ?

[atcodedog05]

Guessing it worked : )