db2—使用自定义sql查询将tableau连接到teradata

svgewumm 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(485)

edit：即使我尝试在任何地方删除'as'，我仍然会在一些看起来像普通sql命令的事情上出错。如果我删除'as'，下一个错误是：
[ibm][cli driver][db2]sql0199n在“”之后使用保留字“cast”无效。预期的令牌可能包括：“，）”。sqlstate=42601
问：我正在尝试用一个自定义sql查询连接到teradata，虽然它在teradata sql assistant中运行得很好，但在tableau中运行时，我总是会遇到错误。
其中一个错误如下所示，但即使我尝试更改某些内容，也会出现类似的错误：
[ibm][cli driver][db2]sql0199n保留字“as”following“”的使用无效。预期的标记可能包括：“join cross inner left right full（”）。sqlstate=42601
这是查询（对不起，太长了）：

WITH RQSTS AS(
select CAST( M.RQST_ID AS VARCHAR(20)) AS MASTER_RQST_ID,
M.RQST_TYP_CD
FROM PRTHD.HOST_RQST AS M --MASTER
WHERE M.MSTR_RQST_ID IS NULL
  AND M.crt_user_id='SXB8NBS'
  AND M.OCYC_SOQ_RSN_CD = 170
  AND date(M.CRT_TS) >= CURRENT_DATE - 7 DAY
),
RQST_TYPE AS(
select R.MASTER_RQST_ID AS RQST_ID, 
NRT.TYP_DESC AS HOST_TYPE
FROM RQSTS AS R
LEFT JOIN PRTHD.N_HOST_RQST_TYP AS NRT     
ON R.RQST_TYP_CD = NRT.RQST_TYP_CD
GROUP BY R.MASTER_RQST_ID, NRT.TYP_DESC
),
PO_DETAILS AS(
select A.* 
FROM (
  SELECT distinct PRTHD.BYO_PO.DSVC_TYP_CD,
  RT.RQST_ID,
  RT.HOST_TYPE,
  PRTHD.BYO_PO.PO_CMT_TXT,
  PRTHD.BYO_PO.BYO_NBR,
  CASE WHEN PRTHD.BYO_PO.DSVC_TYP_CD = 2 THEN RIGHT('00'||PRTHD.BYO_PO.BYO_NBR,2) 
     ELSE RIGHT('00'|| PRTHD.BYO_PO_LOC_SKU.LOC_NBR,2) END
  || PRTHD.BYO_PO.acct_po_typ_cd || RIGHT('00000'||PRTHD.BYO_PO.PO_CTRL_NBR,5) AS PO,
  PRTHD.BYO_PO.ORD_REF_NBR,
  PRTHD.BYO_PO_LOC_SKU.MKT_DC_NBR,
  PRTHD.BYO_PO_LOC_SKU.LOC_NBR,
  PRTHD.BYO_PO_LOC_SKU.SKU_NBR,
  CAST(PRTHD.BYO_PO_LOC_SKU.ORD_QTY AS INTEGER) AS ORD_QTY,
  PRTHD.BYO_PO.CRT_DT,
  PRTHD.BYO_PO.PO_CTRL_NBR,
  M.MVNDR_NM,
  PRTHD.BYO_PO.MVNDR_NBR,
  PRTHD.BYO_PO.OCYC_SOQ_RSN_CD AS RSN_CD
FROM ((PRTHD.BYO_PO AS BP INNER JOIN PRTHD.BYO_PO_LOC_SKU
  ON (PRTHD.BYO_PO.ORD_SEQ_NBR = PRTHD.BYO_PO_LOC_SKU.ORD_SEQ_NBR)
  AND (PRTHD.BYO_PO.PO_CTRL_NBR = PRTHD.BYO_PO_LOC_SKU.PO_CTRL_NBR)
  AND (PRTHD.BYO_PO.ACCT_PO_TYP_CD = PRTHD.BYO_PO_LOC_SKU.ACCT_PO_TYP_CD)
  AND (PRTHD.BYO_PO.MKT_DC_NBR = PRTHD.BYO_PO_LOC_SKU.MKT_DC_NBR)
  AND (PRTHD.BYO_PO.MKT_DC_IND = PRTHD.BYO_PO_LOC_SKU.MKT_DC_IND)
  AND (PRTHD.BYO_PO.BYO_NBR = PRTHD.BYO_PO_LOC_SKU.BYO_NBR))
INNER JOIN PRTHD.SKU 
  ON PRTHD.BYO_PO_LOC_SKU.SKU_NBR = PRTHD.SKU.SKU_NBR)
INNER JOIN PRTHD.BYO_PO_SKU 
  ON (PRTHD.SKU.SKU_NBR = PRTHD.BYO_PO_SKU.SKU_NBR)
  AND (PRTHD.BYO_PO_LOC_SKU.BYO_NBR = PRTHD.BYO_PO_SKU.BYO_NBR) 
  AND (PRTHD.BYO_PO_LOC_SKU.MKT_DC_IND = PRTHD.BYO_PO_SKU.MKT_DC_IND)
  AND (PRTHD.BYO_PO_LOC_SKU.MKT_DC_NBR = PRTHD.BYO_PO_SKU.MKT_DC_NBR)
  AND (PRTHD.BYO_PO_LOC_SKU.ACCT_PO_TYP_CD = PRTHD.BYO_PO_SKU.ACCT_PO_TYP_CD)
  AND (PRTHD.BYO_PO_LOC_SKU.PO_CTRL_NBR = PRTHD.BYO_PO_SKU.PO_CTRL_NBR)
  AND (PRTHD.BYO_PO_LOC_SKU.ORD_SEQ_NBR = PRTHD.BYO_PO_SKU.ORD_SEQ_NBR)
  AND (PRTHD.BYO_PO_LOC_SKU.PO_LINE_NBR = PRTHD.BYO_PO_SKU.PO_LINE_NBR)
LEFT JOIN PRTHD.MVNDR AS M
  ON M.MVNDR_NBR = PRTHD.BYO_PO.MVNDR_NBR
INNER JOIN RQST_TYPE AS RT
  ON TRIM(SUBSTR(BP.PO_CMT_TXT,6,11)) = RT.RQST_ID
) AS A)
Select P.RQST_ID,
P.DSVC_TYP_CD,
CASE WHEN P.DSVC_TYP_CD =1 THEN 'DTS'
   WHEN P.DSVC_TYP_CD =2 THEN 'RDC AGG'
   WHEN P.DSVC_TYP_CD =3 THEN 'RDCX'
   END AS DSVC_TYP_DESC ,
CASE WHEN P.DSVC_TYP_CD =2 AND S.STR_NBR IS NOT NULL THEN 'Y'
   ELSE 'N' END AS RDC_AGG_PEG_FLG,
CASE WHEN P.DSVC_TYP_CD =2 AND S.STR_NBR IS NOT NULL THEN P.ORD_REF_NBR
   ELSE P.PO END AS PO_NBR,
P.BYO_NBR,
P.PO_CTRL_NBR,
P.CRT_DT,
P.HOST_TYPE,
P.MVNDR_NBR,
P.MVNDR_NM,
CASE WHEN P.DSVC_TYP_CD =2 AND S.STR_NBR IS NOT NULL THEN AR.MKT_DC_NBR 
   ELSE P.MKT_DC_NBR END AS MKT_DC_NBR,
P.LOC_NBR,
COALESCE(SKU.MER_DEPT_NBR || '-' || D.SHRT_DEPT_NM,'') AS DEPT,
COALESCE(SKU.MER_CLASS_NBR || '-' || MC.SHRT_CLASS_DESC,'') AS CLASS,
COALESCE(SKU.MER_SUB_CLASS_NBR || '-' || MSC.SHRT_SUBCLASS_DESC,'') AS SUB_CLASS,
P.SKU_NBR,
P.ORD_QTY,
COALESCE(SD.AVG_DC_LCOST_AMT*COALESCE(P.ORD_QTY,0),
MSM.CURR_COST_AMT*COALESCE(P.ORD_QTY,0)) AS TOTAL_COST,
SD.AVG_DC_LCOST_AMT AS SD_COST,
MSM.CURR_COST_AMT AS STR_CURR_COST,
MSD.CURR_COST_AMT AS DC_CURR_COST,
P.PO_CMT_TXT,
P.RSN_CD
FROM PO_DETAILS AS P
--SKU HIERARCHY INFO
LEFT JOIN PRTHD.SKU  AS SKU                
  ON SKU.SKU_NBR = P.SKU_NBR
LEFT JOIN PRTHD.DEPT AS D                    
  ON SKU.MER_DEPT_NBR = D.DEPT_NBR
LEFT JOIN PRTHD.MER_CLASS AS MC           
  ON MC.MER_DEPT_NBR= SKU.MER_DEPT_NBR 
  AND MC.MER_CLASS_NBR = SKU.MER_CLASS_NBR
LEFT JOIN PRTHD.MER_SUB_CLASS AS MSC    
  ON MSC.MER_DEPT_NBR= SKU.MER_DEPT_NBR 
  AND MSC.MER_CLASS_NBR = SKU.MER_CLASS_NBR 
  AND MSC.MER_SUB_CLASS_NBR = SKU.MER_SUB_CLASS_NBR
LEFT JOIN PRTHD.MVNDR_SKU_DC AS MSD     
  ON MSD.MVNDR_NBR = P.MVNDR_NBR 
  AND MSD.SKU_NBR = P.SKU_NBR 
  AND MSD.DC_NBR = P.LOC_NBR
LEFT JOIN PRTHD.STR AS S                  
  ON S.STR_NBR = P.LOC_NBR
LEFT JOIN PRTHD.SKU_DC AS SD               
  ON SD.DC_NBR=P.MKT_DC_NBR 
  AND SD.SKU_NBR=P.SKU_NBR
LEFT JOIN PRTHD.MVNDR_SKU_MKT AS MSM    
  ON MSM.MVNDR_NBR = P.MVNDR_NBR 
  AND MSM.SKU_NBR = P.SKU_NBR 
  AND MSM.MKT_NBR = S.MKT_NBR
LEFT JOIN PRTHD.AGG_RQST AS AR 
  ON AR.PO_NBR = P.ORD_REF_NBR 
  AND P.MVNDR_NBR = AR.MVNDR_NBR 
  AND SKU.MER_DEPT_NBR = AR.MER_DEPT_NBR
  AND AR.ORD_DT >= P.CRT_DT

有人能帮我找出这里可能出了什么问题吗？谢谢您！

sql db2 tableau-api teradata-sql-assistant

来源：https://stackoverflow.com/questions/63307336/connecting-tableau-to-teradata-with-custom-sql-query

1条答案

按热度按时间

uajslkp61#

删除所有 AS :

WITH RQSTS (
    select CT(M.RQST_ID  VARCHAR(20))  MTER_RQST_ID,
        M.RQST_TYP_CD
    FROM PRTHD.HOST_RQST  M --MTER
    WHERE M.MSTR_RQST_ID IS NULL
        AND M.crt_user_id = 'SXB8NBS'
        AND M.OCYC_SOQ_RSN_CD = 170
        AND date(M.CRT_TS) >= CURRENT_DATE - 7 DAY
),
RQST_TYPE (
    select R.MTER_RQST_ID  RQST_ID,
        NRT.TYP_DESC  HOST_TYPE
    FROM RQSTS  R
        LEFT JOIN PRTHD.N_HOST_RQST_TYP  NRT ON R.RQST_TYP_CD = NRT.RQST_TYP_CD
    GROUP BY R.MTER_RQST_ID,
        NRT.TYP_DESC
),
PO_DETAILS (
    select A.*
    FROM (
            SELECT distinct PRTHD.BYO_PO.DSVC_TYP_CD,
                RT.RQST_ID,
                RT.HOST_TYPE,
                PRTHD.BYO_PO.PO_CMT_TXT,
                PRTHD.BYO_PO.BYO_NBR,
                CE
                    WHEN PRTHD.BYO_PO.DSVC_TYP_CD = 2 THEN RIGHT('00' || PRTHD.BYO_PO.BYO_NBR, 2)
                    ELSE RIGHT('00' || PRTHD.BYO_PO_LOC_SKU.LOC_NBR, 2)
                END || PRTHD.BYO_PO.acct_po_typ_cd || RIGHT('00000' || PRTHD.BYO_PO.PO_CTRL_NBR, 5)  PO,
                PRTHD.BYO_PO.ORD_REF_NBR,
                PRTHD.BYO_PO_LOC_SKU.MKT_DC_NBR,
                PRTHD.BYO_PO_LOC_SKU.LOC_NBR,
                PRTHD.BYO_PO_LOC_SKU.SKU_NBR,
                CT(PRTHD.BYO_PO_LOC_SKU.ORD_QTY  INTEGER)  ORD_QTY,
                PRTHD.BYO_PO.CRT_DT,
                PRTHD.BYO_PO.PO_CTRL_NBR,
                M.MVNDR_NM,
                PRTHD.BYO_PO.MVNDR_NBR,
                PRTHD.BYO_PO.OCYC_SOQ_RSN_CD  RSN_CD
            FROM (
                    (
                        PRTHD.BYO_PO  BP
                        INNER JOIN PRTHD.BYO_PO_LOC_SKU ON (
                            PRTHD.BYO_PO.ORD_SEQ_NBR = PRTHD.BYO_PO_LOC_SKU.ORD_SEQ_NBR
                        )
                        AND (
                            PRTHD.BYO_PO.PO_CTRL_NBR = PRTHD.BYO_PO_LOC_SKU.PO_CTRL_NBR
                        )
                        AND (
                            PRTHD.BYO_PO.ACCT_PO_TYP_CD = PRTHD.BYO_PO_LOC_SKU.ACCT_PO_TYP_CD
                        )
                        AND (
                            PRTHD.BYO_PO.MKT_DC_NBR = PRTHD.BYO_PO_LOC_SKU.MKT_DC_NBR
                        )
                        AND (
                            PRTHD.BYO_PO.MKT_DC_IND = PRTHD.BYO_PO_LOC_SKU.MKT_DC_IND
                        )
                        AND (
                            PRTHD.BYO_PO.BYO_NBR = PRTHD.BYO_PO_LOC_SKU.BYO_NBR
                        )
                    )
                    INNER JOIN PRTHD.SKU ON PRTHD.BYO_PO_LOC_SKU.SKU_NBR = PRTHD.SKU.SKU_NBR
                )
                INNER JOIN PRTHD.BYO_PO_SKU ON (PRTHD.SKU.SKU_NBR = PRTHD.BYO_PO_SKU.SKU_NBR)
                AND (
                    PRTHD.BYO_PO_LOC_SKU.BYO_NBR = PRTHD.BYO_PO_SKU.BYO_NBR
                )
                AND (
                    PRTHD.BYO_PO_LOC_SKU.MKT_DC_IND = PRTHD.BYO_PO_SKU.MKT_DC_IND
                )
                AND (
                    PRTHD.BYO_PO_LOC_SKU.MKT_DC_NBR = PRTHD.BYO_PO_SKU.MKT_DC_NBR
                )
                AND (
                    PRTHD.BYO_PO_LOC_SKU.ACCT_PO_TYP_CD = PRTHD.BYO_PO_SKU.ACCT_PO_TYP_CD
                )
                AND (
                    PRTHD.BYO_PO_LOC_SKU.PO_CTRL_NBR = PRTHD.BYO_PO_SKU.PO_CTRL_NBR
                )
                AND (
                    PRTHD.BYO_PO_LOC_SKU.ORD_SEQ_NBR = PRTHD.BYO_PO_SKU.ORD_SEQ_NBR
                )
                AND (
                    PRTHD.BYO_PO_LOC_SKU.PO_LINE_NBR = PRTHD.BYO_PO_SKU.PO_LINE_NBR
                )
                LEFT JOIN PRTHD.MVNDR  M ON M.MVNDR_NBR = PRTHD.BYO_PO.MVNDR_NBR
                INNER JOIN RQST_TYPE  RT ON TRIM(SUBSTR(BP.PO_CMT_TXT, 6, 11)) = RT.RQST_ID
        )  A
)
Select P.RQST_ID,
    P.DSVC_TYP_CD,
    CE
        WHEN P.DSVC_TYP_CD = 1 THEN 'DTS'
        WHEN P.DSVC_TYP_CD = 2 THEN 'RDC AGG'
        WHEN P.DSVC_TYP_CD = 3 THEN 'RDCX'
    END  DSVC_TYP_DESC,
    CE
        WHEN P.DSVC_TYP_CD = 2
        AND S.STR_NBR IS NOT NULL THEN 'Y'
        ELSE 'N'
    END  RDC_AGG_PEG_FLG,
    CE
        WHEN P.DSVC_TYP_CD = 2
        AND S.STR_NBR IS NOT NULL THEN P.ORD_REF_NBR
        ELSE P.PO
    END  PO_NBR,
    P.BYO_NBR,
    P.PO_CTRL_NBR,
    P.CRT_DT,
    P.HOST_TYPE,
    P.MVNDR_NBR,
    P.MVNDR_NM,
    CE
        WHEN P.DSVC_TYP_CD = 2
        AND S.STR_NBR IS NOT NULL THEN AR.MKT_DC_NBR
        ELSE P.MKT_DC_NBR
    END  MKT_DC_NBR,
    P.LOC_NBR,
    COALESCE(SKU.MER_DEPT_NBR || '-' || D.SHRT_DEPT_NM, '')  DEPT,
    COALESCE(
        SKU.MER_CLS_NBR || '-' || MC.SHRT_CLS_DESC,
        ''
    )  CLS,
    COALESCE(
        SKU.MER_SUB_CLS_NBR || '-' || MSC.SHRT_SUBCLS_DESC,
        ''
    )  SUB_CLS,
    P.SKU_NBR,
    P.ORD_QTY,
    COALESCE(
        SD.AVG_DC_LCOST_AMT * COALESCE(P.ORD_QTY, 0),
        MSM.CURR_COST_AMT * COALESCE(P.ORD_QTY, 0)
    )  TOTAL_COST,
    SD.AVG_DC_LCOST_AMT  SD_COST,
    MSM.CURR_COST_AMT  STR_CURR_COST,
    MSD.CURR_COST_AMT  DC_CURR_COST,
    P.PO_CMT_TXT,
    P.RSN_CD
FROM PO_DETAILS  P --SKU HIERARCHY INFO
    LEFT JOIN PRTHD.SKU  SKU ON SKU.SKU_NBR = P.SKU_NBR
    LEFT JOIN PRTHD.DEPT  D ON SKU.MER_DEPT_NBR = D.DEPT_NBR
    LEFT JOIN PRTHD.MER_CLS  MC ON MC.MER_DEPT_NBR = SKU.MER_DEPT_NBR
    AND MC.MER_CLS_NBR = SKU.MER_CLS_NBR
    LEFT JOIN PRTHD.MER_SUB_CLS  MSC ON MSC.MER_DEPT_NBR = SKU.MER_DEPT_NBR
    AND MSC.MER_CLS_NBR = SKU.MER_CLS_NBR
    AND MSC.MER_SUB_CLS_NBR = SKU.MER_SUB_CLS_NBR
    LEFT JOIN PRTHD.MVNDR_SKU_DC  MSD ON MSD.MVNDR_NBR = P.MVNDR_NBR
    AND MSD.SKU_NBR = P.SKU_NBR
    AND MSD.DC_NBR = P.LOC_NBR
    LEFT JOIN PRTHD.STR  S ON S.STR_NBR = P.LOC_NBR
    LEFT JOIN PRTHD.SKU_DC  SD ON SD.DC_NBR = P.MKT_DC_NBR
    AND SD.SKU_NBR = P.SKU_NBR
    LEFT JOIN PRTHD.MVNDR_SKU_MKT  MSM ON MSM.MVNDR_NBR = P.MVNDR_NBR
    AND MSM.SKU_NBR = P.SKU_NBR
    AND MSM.MKT_NBR = S.MKT_NBR
    LEFT JOIN PRTHD.AGG_RQST  AR ON AR.PO_NBR = P.ORD_REF_NBR
    AND P.MVNDR_NBR = AR.MVNDR_NBR
    AND SKU.MER_DEPT_NBR = AR.MER_DEPT_NBR
    AND AR.ORD_DT >= P.CRT_DT

展开查看全部

赞(0）回复(0）举报 2021-07-24

我来回答

db2—使用自定义sql查询将tableau连接到teradata

1条答案

相关问题

热门标签

最新问答