I’m working on converting the sql query below over to Snowflake, and got the error “Numeric Value ‘-‘ is not recognized” but it did not have a line number. I assume it’s in reference to the various “-1” instances throughout the query but there’s no line referenced so it’s unclear what’s causing the problem. If it’s the various references to “-1” – what is the alternative in Snowflake? Try_To_Number won’t work in this case, so hoping community has suggestions
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
Advertisement
Answer
As David hinted in the comments, this line gives you that error:
select CAST('1' AS NVARCHAR ) + '-' + CAST('2' AS NVARCHAR);
Fixed:
select CAST('1' AS NVARCHAR ) || '-' || CAST('2' AS NVARCHAR);
As seen in the original query:
CAST( DATE_PART( yyyy, DH."DocDate" ) AS NVARCHAR ) + '-'+ CAST(RIGHT(DATE_PART( mm, DH."DocDate" )+100,2) AS NVARCHAR)
(and other concatenations with +, they should be ||)