Teradata Direct Query Error

[ThreadRider2000]

I'm able to run the below query in TD SQL Assistant.  I am using ORDER BY because my understanding is that will improve the PowerBI performance.

SELECT TPO_SKU_PRD_SLS_SUMMRY.TPO_SKU,
TPO_SKU_PRD_SLS_SUMMRY.BRND_NM,
TPO_PROD_MSTR.TPO_SGMNT_NM,
Cast((Trim(TPO_SKU_PRD_SLS_SUMMRY.YR_NUM) ||
CASE WHEN TPO_SKU_PRD_SLS_SUMMRY.PRD_NUM <10 THEN
'0' || Trim(TPO_SKU_PRD_SLS_SUMMRY.PRD_NUM)
ELSE Trim(TPO_SKU_PRD_SLS_SUMMRY.PRD_NUM)
END) AS INTEGER) AS YR_PRD_NUM,
TPO_SKU_PRD_SLS_SUMMRY.BU_NM,
TPO_SKU_PRD_SLS_SUMMRY.DIVSN_NM,
TPO_SKU_PRD_SLS_SUMMRY.RGN_NM,
TPO_SKU_PRD_SLS_SUMMRY.MKT_NM,
TPO_SKU_PRD_SLS_SUMMRY.GEO_TYP_CD,
TPO_SKU_PRD_SLS_SUMMRY.LOCL_CRNCY_CDV,
TPO_SKU_PRD_SLS_SUMMRY.MTRL_UOM_CDV,
TPO_SKU_PRD_SLS_SUMMRY.CY_NET_REV_AMT,
TPO_SKU_PRD_SLS_SUMMRY.LY_NET_REV_AMT,
TPO_SKU_PRD_SLS_SUMMRY.CY_EXPNS_RTRN_AMT,
TPO_SKU_PRD_SLS_SUMMRY.LY_EXPNS_RTRN_AMT,
TPO_SKU_PRD_SLS_SUMMRY.CY_TRK_STL_AMT,
TPO_SKU_PRD_SLS_SUMMRY.LY_TRK_STL_AMT,
TPO_SKU_PRD_SLS_SUMMRY.CY_MFG_DFECT_AMT,
TPO_SKU_PRD_SLS_SUMMRY.LY_MFG_DFECT_AMT,
TPO_SKU_PRD_SLS_SUMMRY.CY_NET_TKT_SALE_QTY,
TPO_SKU_PRD_SLS_SUMMRY.LY_NET_TKT_SALE_QTY
FROM SEM_TPO.TPO_SKU_PRD_SLS_SUMMRY,
SEM_TPO.TPO_PROD_MSTR
WHERE TPO_SKU_PRD_SLS_SUMMRY.TPO_SKU = TPO_PROD_MSTR.TPO_SKU AND
TPO_SKU_PRD_SLS_SUMMRY.YR_NUM >= (Extract(YEAR From Current_Date)-2) AND
(TPO_SKU_PRD_SLS_SUMMRY.YR_NUM <= Extract(YEAR From Current_Date) AND
TPO_SKU_PRD_SLS_SUMMRY.PRD_NUM <= Extract(MONTH From Current_Date))
ORDER BY TPO_SKU_PRD_SLS_SUMMRY.TPO_SKU,
TPO_SKU_PRD_SLS_SUMMRY.BRND_NM,
TPO_PROD_MSTR.TPO_SGMNT_NM,
Cast((Trim(TPO_SKU_PRD_SLS_SUMMRY.YR_NUM) ||
CASE WHEN TPO_SKU_PRD_SLS_SUMMRY.PRD_NUM <10 THEN
'0' || Trim(TPO_SKU_PRD_SLS_SUMMRY.PRD_NUM)
ELSE Trim(TPO_SKU_PRD_SLS_SUMMRY.PRD_NUM)
END) AS INTEGER),
TPO_SKU_PRD_SLS_SUMMRY.BU_NM,
TPO_SKU_PRD_SLS_SUMMRY.DIVSN_NM,
TPO_SKU_PRD_SLS_SUMMRY.RGN_NM,
TPO_SKU_PRD_SLS_SUMMRY.MKT_NM,
TPO_SKU_PRD_SLS_SUMMRY.GEO_TYP_CD,
TPO_SKU_PRD_SLS_SUMMRY.LOCL_CRNCY_CDV,
TPO_SKU_PRD_SLS_SUMMRY.MTRL_UOM_CDV

However, when I input the SQL in PowerBI direct query, I encounter the following error.

TPO_SKU_PRD_SLS_SUMMRY

Teradata: [Teradata Database] [3706] Syntax error: ORDER BY is not allowed in subqueries.

I've seen other TD sql errors when connecting with PowerBI.  Is there something in general that should be configured to ensure all TD SQL statements can be run in PowerBI without encountering issues?

Thanks,

Kelly

[amitchandak]

@ThreadRider2000 , I can be a database level issue. Some database do not allow order by in subquery. In this case of you need to give order by in the outer query. Also, you are not using any limit, so I doubt order by will benefit

[ThreadRider2000]

Hi,

This is not a database issue as I can execute it in Excel Power Query/PowerPivot and pull all the data.  However, when I try and do the same in PowerBI, the query fails.  I was able to load ~1.7 Million rows into Excel Power Pivot

Thought Power Query is supposed to be the same between Excel and Power BI? 

Thanks

[v-shex-msft]

Hi @ThreadRider2000,

I suppose this should be related to power bi service security. AFAIK, some types of data sources, functions, and requests/operations will be blocked due to security reasons.

Power BI Security#data-storage-security 
According to your error message, I think it means 'order by' function not allowed to use in t-SQL subquery when they executed on power bi service side. You can try to remove it or switch to other equivalent functions and try again.

Regards,

Xiaoxin Sheng

[ThreadRider2000]

Thanks for the suggestion and links, but I am using strictly Power BI desktop.

Kelly