无法解析雪花SQL查询中的“数值”-“无法识别”错误

Question

我正在将下面的sql查询转换为Snowflake，并得到错误“numeric Value”-“未识别”，但它没有行号。我假设它在整个查询中引用了各种“-1”实例，但没有引用任何行，所以不清楚是什么导致了这个问题。如果是对“-1”的各种引用--雪花中的替代方案是什么？Try_To_Number在这种情况下不起作用，所以希望社区有建议

SELECT 
-- DOCUMENT HEADER INFORMATION
'ORPC'                                                  AS "Doc_Module" 
, DD."DocEntry"                                             AS "Doc_DocEntry"
, DD."ObjType"                                              AS "Doc_ObjectType"
, 'A/P Credit'                                              AS "Doc_TypeName_DIM"   
, 'PC'                                                      AS "Doc_TransTypeAbrev" 
, DH."DocNum"                                                   AS "Doc_Number"
, DH."DocStatus"                                                AS "Doc_Status"     --Open 
or Closed
, DD."LineNum"                                              AS "Doc_LineNumberInternal"
, DD."VisOrder"                                             AS "Doc_LineNumberVisible"
, NULLIF( DD."BaseEntry", -1 )                              AS "Doc_SourceEntry"
, DD."BaseType"                                             AS "Doc_SourceType"
, NULLIF( DD."BaseLine", -1 )                                   AS "Doc_SourceLine"
, DD."TargetType"                                               AS "Doc_TargetType"
, NULLIF( DD."TrgetEntry", -1 )                             AS "Doc_TargetEntry"
-- BP
, DH."CardCode"                                             AS "Doc_BPCardCode"
--DATES
, CAST( DH."DocDate" AS DATETIME )                          AS "Date_Posting_DIM"
, CAST( NULLIF(DD."ShipDate", DH."DocDueDate") AS DATETIME )    AS "Date_ShipDue"
, CAST( DD."ActDelDate" AS DATETIME )                           AS "Date_ActDelivery"
, CAST( DH."TaxDate" AS DATETIME )                          AS "Date_Document"
, CAST( DATE_PART( yyyy, DH."DocDate" ) AS NVARCHAR )           AS "Date_Posting_Year_DIM"
, CAST( DATE_PART( yyyy, DH."DocDate" ) AS NVARCHAR ) + '-'+
CAST(RIGHT(DATE_PART( mm, DH."DocDate" )+100,2) AS NVARCHAR) AS "Date_Posting_YYYYMM_DIM"
, LEFT( MONTHNAME(DH."DocDate"), 3 )                    AS "Date_Posting_MonthShort"
, MONTHNAME(DH."DocDate")                               AS "Date_Posting_Month_DIM"
, DATEDIFF( dd, NULLIF(DD."ShipDate", DH."DocDueDate"), DD."ActDelDate" ) AS "Date_DaysLate"  
--Positive is days late, negative is days early
--WH and LOCATION
, CASE WHEN DD."WhsCode" IS NULL OR WH."WhsName" IS NULL    
THEN '- na -' 
ELSE DD."WhsCode" +' - '+  WH."WhsName" END                 AS "Warehouse"
, NULLIF( WH."WhsName", '- na -' )                          AS "Warehouse_Name"
, NULLIF( DD."WhsCode", '- na -' )                          AS "Warehouse_Code"     
, CASE  WHEN WH."Location" IS NULL OR LOC."Location" IS NULL
    THEN '- na -'
    ELSE RIGHT( CAST( WH."Location" + 100 AS NVARCHAR), 2 ) +' - '+ LOC."Location"
                                                    END AS "Warehouse_Location_DIM"  
, NULLIF( WH."Location", -1 )                                   AS "Warehouse_LocationCode"

--LINE ITEM & QTY
, DD."LineStatus"                                               AS "Line_Status"   --Open, 
Closed
, CAST( NULLIF( DD."ItemCode", '- na -' ) AS NVARCHAR ) 
 + '  ' +  NULLIF( DD."Dscription", '' )                        AS "Line_Item_DIM"
, NULLIF( DD."ItemCode", '- na -' )                         AS "Line_ItemCode"
, NULLIF( DD."Dscription", '' )                             AS "Line_ItemName"
, NULLIF( DD."SubCatNum", '' )                              AS "Line_BPItemNumber"
, CASE  WHEN LTRIM(RTRIM(NULLIF( DD."Project", '' ))) <> ''
    THEN LTRIM(RTRIM(DD."Project"))
    ELSE LTRIM(RTRIM(NULLIF( DH."Project", '' )))       END AS "Line_ProjectCode"
, NULLIF( PJ."PrjName", '' )                                    AS "Line_ProjectName_DIM"

-- QUANTITY
, CASE  WHEN DD."NoInvtryMv" = 'Y'                      THEN 0                               
ELSE -1 * DD."Quantity"                         END AS "Qty_OrderedPurchUnits_FACT" 
, CASE  WHEN DD."NoInvtryMv" = 'Y'                      THEN 0                               
ELSE -1 * DD."InvQty"                           END AS "Qty_OrderedInvUnits_FACT" 
, CASE  WHEN DD."LineStatus" = 'O' AND DH."DocStatus" = 'O' THEN -1 * DD."OpenCreQty"                
ELSE 0                                          END AS "Qty_OpenUnits_FACT"
, CASE  WHEN DD."LineStatus" = 'O' AND DH."DocStatus" = 'O' THEN -1 * DD."OpenInvQty"                
ELSE 0                                          END AS "Qty_OpenInvUnits_FACT"

--PRICE
, CASE  WHEN DD."LineStatus" = 'O' AND DH."DocStatus" = 'O' AND DD."Quantity" <> 0
    THEN NULLIF( ( DD."LineTotal" / DD."Quantity" ) * DD."OpenCreQty"   , 0 )                    
ELSE 0                                          END AS "Line_ItemPriceOpenUnits_DIM"  -- 
Purch UoM
, CASE  WHEN DD."LineStatus" = 'O' AND DH."DocStatus" = 'O' AND DD."InvQty" <> 0
    THEN NULLIF( ( DD."LineTotal" / DD."InvQty"   ) * DD."OpenCreQty"   , 0 )                    
ELSE 0                                          END AS "Line_ItemPriceOpenInvUnits_DIM"
, CASE  WHEN DD."Quantity" = 0                          THEN  NULLIF( DD."LineTotal", 0 )        
ELSE NULLIF( DD."LineTotal", 0 ) / DD."Quantity"    END AS "Line_ItemPrice_DIM"   --Purch 
UoM
, CASE  WHEN DD."Quantity" = 0                          THEN  NULLIF( DD."LineTotal", 0 )        
ELSE NULLIF( DD."LineTotal", 0 ) / DD."InvQty"      END AS "Line_ItemPriceInvUnits_DIM"   
, -1 * DD."LineTotal"                                                                                                                                
AS "Line_TotalPrice_FACT"

, COALESCE( DD."SlpCode" , DH."SlpCode" , BP."SlpCode" )            AS "Buyer_Code"     -- 
Line first, Doc header, BP
 , COALESCE( DSP."SlpName", HSP."SlpName", PSP."SlpName" )          AS "Buyer_Name_DIM" -- 
Line first, Doc header, BP

FROM "RPC1" DD
INNER JOIN "RPC12" AD ON DD."DocEntry" = AD."DocEntry"
LEFT OUTER JOIN "OCST" ST ON AD."StateS" = ST."Code" AND AD."CountryS" = ST."Country"       --Join on State code & country code in case state code appears in more than one country
LEFT OUTER JOIN "OCRY" RY ON AD."CountryS"=RY."Code"                            --Country
INNER JOIN "ORPC" DH ON DD."DocEntry" = DH."DocEntry"                           --Doc Header
    LEFT OUTER JOIN "OSLP" HSP ON DH."SlpCode" = HSP."SlpCode"                  --Doc Header Sales person
    LEFT OUTER JOIN "OCRD" BP ON BP."CardCode" = DH."CardCode"                  --Business Partner.  Needed here for SLP code
        LEFT OUTER JOIN "OSLP" PSP ON PSP."SlpCode" = BP."SlpCode"              --BP Sales person
LEFT OUTER JOIN "OITM" IM ON DD."ItemCode" = IM."ItemCode"                      --Item Master
    LEFT OUTER JOIN "ORC_BE_PACK_TYPE" PT ON IM."U_ORC_BE_PACK_TYPE" = PT."Code"    --Pack type
LEFT OUTER JOIN "OWHS" WH ON DD."WhsCode" = WH."WhsCode"                        --Warehouse
    LEFT OUTER JOIN "OLCT" LOC ON WH."Location" = LOC."Code"                    --WH Location
LEFT OUTER JOIN "OSLP" DSP ON DSP."SlpCode" = DD."SlpCode"                      --Doc Line Sales person
LEFT OUTER JOIN "OPRJ" PJ ON UPPER(LTRIM(RTRIM( PJ."PrjCode" ))) = CASE WHEN       LTRIM(RTRIM(NULLIF( DD."Project", '' ))) <> ''
                                                                    THEN UPPER(LTRIM(RTRIM(        DD."Project"     )))
                                                                    ELSE UPPER(LTRIM(RTRIM(NULLIF( DH."Project", '' ))))        END  --DH.Project is aka BP Project on the doc

WHERE   DH."CANCELED" = 'N'  --Do not show 'Y' cancelled doc or 'C' cancellation offset doc

Answer 1

正如David在评论中暗示的那样，这一行给了您一个错误：

select CAST('1' AS NVARCHAR ) + '-' + CAST('2' AS NVARCHAR);

已修正：

select CAST('1' AS NVARCHAR ) || '-' || CAST('2' AS NVARCHAR);

如原始查询所示：

CAST( DATE_PART( yyyy, DH."DocDate" ) AS NVARCHAR ) + '-'+
CAST(RIGHT(DATE_PART( mm, DH."DocDate" )+100,2) AS NVARCHAR)

（以及与+的其他连接，它们应该是)