Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Teradata Direct Query Error

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

Need Help

4 Replies

Replies have been turned off for this discussion
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , 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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous,

        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