title: Not able to resolve "Numeric Value '-' is not recognized" error in Snowflake SQL Query
body: <p>I'm working on converting the sql query below over to Snowflake, and got the error &quot;Numeric Value '-' is not recognized&quot; but it did not have a line number. I assume it's in reference to the various &quot;-1&quot; 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 &quot;-1&quot; - what is the alternative in Snowflake? Try_To_Number won't work in this case, so hoping community has suggestions</p>
  <pre><code>SELECT 
  -- DOCUMENT HEADER INFORMATION
  'ORPC'                                                  AS &quot;Doc_Module&quot; 
  , DD.&quot;DocEntry&quot;                                             AS &quot;Doc_DocEntry&quot;
  , DD.&quot;ObjType&quot;                                              AS &quot;Doc_ObjectType&quot;
  , 'A/P Credit'                                              AS &quot;Doc_TypeName_DIM&quot;   
  , 'PC'                                                      AS &quot;Doc_TransTypeAbrev&quot; 
  , DH.&quot;DocNum&quot;                                                   AS &quot;Doc_Number&quot;
  , DH.&quot;DocStatus&quot;                                                AS &quot;Doc_Status&quot;     --Open 
  or Closed
  , DD.&quot;LineNum&quot;                                              AS &quot;Doc_LineNumberInternal&quot;
  , DD.&quot;VisOrder&quot;                                             AS &quot;Doc_LineNumberVisible&quot;
  , NULLIF( DD.&quot;BaseEntry&quot;, -1 )                              AS &quot;Doc_SourceEntry&quot;
  , DD.&quot;BaseType&quot;                                             AS &quot;Doc_SourceType&quot;
  , NULLIF( DD.&quot;BaseLine&quot;, -1 )                                   AS &quot;Doc_SourceLine&quot;
  , DD.&quot;TargetType&quot;                                               AS &quot;Doc_TargetType&quot;
  , NULLIF( DD.&quot;TrgetEntry&quot;, -1 )                             AS &quot;Doc_TargetEntry&quot;
  -- BP
  , DH.&quot;CardCode&quot;                                             AS &quot;Doc_BPCardCode&quot;
  --DATES
  , CAST( DH.&quot;DocDate&quot; AS DATETIME )                          AS &quot;Date_Posting_DIM&quot;
  , CAST( NULLIF(DD.&quot;ShipDate&quot;, DH.&quot;DocDueDate&quot;) AS DATETIME )    AS &quot;Date_ShipDue&quot;
  , CAST( DD.&quot;ActDelDate&quot; AS DATETIME )                           AS &quot;Date_ActDelivery&quot;
  , CAST( DH.&quot;TaxDate&quot; AS DATETIME )                          AS &quot;Date_Document&quot;
  , CAST( DATE_PART( yyyy, DH.&quot;DocDate&quot; ) AS NVARCHAR )           AS &quot;Date_Posting_Year_DIM&quot;
  , CAST( DATE_PART( yyyy, DH.&quot;DocDate&quot; ) AS NVARCHAR ) + '-'+
  CAST(RIGHT(DATE_PART( mm, DH.&quot;DocDate&quot; )+100,2) AS NVARCHAR) AS &quot;Date_Posting_YYYYMM_DIM&quot;
  , LEFT( MONTHNAME(DH.&quot;DocDate&quot;), 3 )                    AS &quot;Date_Posting_MonthShort&quot;
  , MONTHNAME(DH.&quot;DocDate&quot;)                               AS &quot;Date_Posting_Month_DIM&quot;
  , DATEDIFF( dd, NULLIF(DD.&quot;ShipDate&quot;, DH.&quot;DocDueDate&quot;), DD.&quot;ActDelDate&quot; ) AS &quot;Date_DaysLate&quot;  
  --Positive is days late, negative is days early
  --WH and LOCATION
  , CASE WHEN DD.&quot;WhsCode&quot; IS NULL OR WH.&quot;WhsName&quot; IS NULL    
  THEN '- na -' 
  ELSE DD.&quot;WhsCode&quot; +' - '+  WH.&quot;WhsName&quot; END                 AS &quot;Warehouse&quot;
  , NULLIF( WH.&quot;WhsName&quot;, '- na -' )                          AS &quot;Warehouse_Name&quot;
  , NULLIF( DD.&quot;WhsCode&quot;, '- na -' )                          AS &quot;Warehouse_Code&quot;     
  , CASE  WHEN WH.&quot;Location&quot; IS NULL OR LOC.&quot;Location&quot; IS NULL
      THEN '- na -'
      ELSE RIGHT( CAST( WH.&quot;Location&quot; + 100 AS NVARCHAR), 2 ) +' - '+ LOC.&quot;Location&quot;
                                                      END AS &quot;Warehouse_Location_DIM&quot;  
  , NULLIF( WH.&quot;Location&quot;, -1 )                                   AS &quot;Warehouse_LocationCode&quot;
  
  --LINE ITEM &amp; QTY
  , DD.&quot;LineStatus&quot;                                               AS &quot;Line_Status&quot;   --Open, 
  Closed
  , CAST( NULLIF( DD.&quot;ItemCode&quot;, '- na -' ) AS NVARCHAR ) 
   + '  ' +  NULLIF( DD.&quot;Dscription&quot;, '' )                        AS &quot;Line_Item_DIM&quot;
  , NULLIF( DD.&quot;ItemCode&quot;, '- na -' )                         AS &quot;Line_ItemCode&quot;
  , NULLIF( DD.&quot;Dscription&quot;, '' )                             AS &quot;Line_ItemName&quot;
  , NULLIF( DD.&quot;SubCatNum&quot;, '' )                              AS &quot;Line_BPItemNumber&quot;
  , CASE  WHEN LTRIM(RTRIM(NULLIF( DD.&quot;Project&quot;, '' ))) &lt;&gt; ''
      THEN LTRIM(RTRIM(DD.&quot;Project&quot;))
      ELSE LTRIM(RTRIM(NULLIF( DH.&quot;Project&quot;, '' )))       END AS &quot;Line_ProjectCode&quot;
  , NULLIF( PJ.&quot;PrjName&quot;, '' )                                    AS &quot;Line_ProjectName_DIM&quot;
  
  -- QUANTITY
  , CASE  WHEN DD.&quot;NoInvtryMv&quot; = 'Y'                      THEN 0                               
  ELSE -1 * DD.&quot;Quantity&quot;                         END AS &quot;Qty_OrderedPurchUnits_FACT&quot; 
  , CASE  WHEN DD.&quot;NoInvtryMv&quot; = 'Y'                      THEN 0                               
  ELSE -1 * DD.&quot;InvQty&quot;                           END AS &quot;Qty_OrderedInvUnits_FACT&quot; 
  , CASE  WHEN DD.&quot;LineStatus&quot; = 'O' AND DH.&quot;DocStatus&quot; = 'O' THEN -1 * DD.&quot;OpenCreQty&quot;                
  ELSE 0                                          END AS &quot;Qty_OpenUnits_FACT&quot;
  , CASE  WHEN DD.&quot;LineStatus&quot; = 'O' AND DH.&quot;DocStatus&quot; = 'O' THEN -1 * DD.&quot;OpenInvQty&quot;                
  ELSE 0                                          END AS &quot;Qty_OpenInvUnits_FACT&quot;
  
  --PRICE
  , CASE  WHEN DD.&quot;LineStatus&quot; = 'O' AND DH.&quot;DocStatus&quot; = 'O' AND DD.&quot;Quantity&quot; &lt;&gt; 0
      THEN NULLIF( ( DD.&quot;LineTotal&quot; / DD.&quot;Quantity&quot; ) * DD.&quot;OpenCreQty&quot;   , 0 )                    
  ELSE 0                                          END AS &quot;Line_ItemPriceOpenUnits_DIM&quot;  -- 
  Purch UoM
  , CASE  WHEN DD.&quot;LineStatus&quot; = 'O' AND DH.&quot;DocStatus&quot; = 'O' AND DD.&quot;InvQty&quot; &lt;&gt; 0
      THEN NULLIF( ( DD.&quot;LineTotal&quot; / DD.&quot;InvQty&quot;   ) * DD.&quot;OpenCreQty&quot;   , 0 )                    
  ELSE 0                                          END AS &quot;Line_ItemPriceOpenInvUnits_DIM&quot;
  , CASE  WHEN DD.&quot;Quantity&quot; = 0                          THEN  NULLIF( DD.&quot;LineTotal&quot;, 0 )        
  ELSE NULLIF( DD.&quot;LineTotal&quot;, 0 ) / DD.&quot;Quantity&quot;    END AS &quot;Line_ItemPrice_DIM&quot;   --Purch 
  UoM
  , CASE  WHEN DD.&quot;Quantity&quot; = 0                          THEN  NULLIF( DD.&quot;LineTotal&quot;, 0 )        
  ELSE NULLIF( DD.&quot;LineTotal&quot;, 0 ) / DD.&quot;InvQty&quot;      END AS &quot;Line_ItemPriceInvUnits_DIM&quot;   
  , -1 * DD.&quot;LineTotal&quot;                                                                                                                                
  AS &quot;Line_TotalPrice_FACT&quot;
  
  , COALESCE( DD.&quot;SlpCode&quot; , DH.&quot;SlpCode&quot; , BP.&quot;SlpCode&quot; )            AS &quot;Buyer_Code&quot;     -- 
  Line first, Doc header, BP
   , COALESCE( DSP.&quot;SlpName&quot;, HSP.&quot;SlpName&quot;, PSP.&quot;SlpName&quot; )          AS &quot;Buyer_Name_DIM&quot; -- 
  Line first, Doc header, BP
  
  FROM &quot;RPC1&quot; DD
  INNER JOIN &quot;RPC12&quot; AD ON DD.&quot;DocEntry&quot; = AD.&quot;DocEntry&quot;
  LEFT OUTER JOIN &quot;OCST&quot; ST ON AD.&quot;StateS&quot; = ST.&quot;Code&quot; AND AD.&quot;CountryS&quot; = ST.&quot;Country&quot;       --Join on State code &amp; country code in case state code appears in more than one country
  LEFT OUTER JOIN &quot;OCRY&quot; RY ON AD.&quot;CountryS&quot;=RY.&quot;Code&quot;                            --Country
  INNER JOIN &quot;ORPC&quot; DH ON DD.&quot;DocEntry&quot; = DH.&quot;DocEntry&quot;                           --Doc Header
      LEFT OUTER JOIN &quot;OSLP&quot; HSP ON DH.&quot;SlpCode&quot; = HSP.&quot;SlpCode&quot;                  --Doc Header Sales person
      LEFT OUTER JOIN &quot;OCRD&quot; BP ON BP.&quot;CardCode&quot; = DH.&quot;CardCode&quot;                  --Business Partner.  Needed here for SLP code
          LEFT OUTER JOIN &quot;OSLP&quot; PSP ON PSP.&quot;SlpCode&quot; = BP.&quot;SlpCode&quot;              --BP Sales person
  LEFT OUTER JOIN &quot;OITM&quot; IM ON DD.&quot;ItemCode&quot; = IM.&quot;ItemCode&quot;                      --Item Master
      LEFT OUTER JOIN &quot;ORC_BE_PACK_TYPE&quot; PT ON IM.&quot;U_ORC_BE_PACK_TYPE&quot; = PT.&quot;Code&quot;    --Pack type
  LEFT OUTER JOIN &quot;OWHS&quot; WH ON DD.&quot;WhsCode&quot; = WH.&quot;WhsCode&quot;                        --Warehouse
      LEFT OUTER JOIN &quot;OLCT&quot; LOC ON WH.&quot;Location&quot; = LOC.&quot;Code&quot;                    --WH Location
  LEFT OUTER JOIN &quot;OSLP&quot; DSP ON DSP.&quot;SlpCode&quot; = DD.&quot;SlpCode&quot;                      --Doc Line Sales person
  LEFT OUTER JOIN &quot;OPRJ&quot; PJ ON UPPER(LTRIM(RTRIM( PJ.&quot;PrjCode&quot; ))) = CASE WHEN       LTRIM(RTRIM(NULLIF( DD.&quot;Project&quot;, '' ))) &lt;&gt; ''
                                                                      THEN UPPER(LTRIM(RTRIM(        DD.&quot;Project&quot;     )))
                                                                      ELSE UPPER(LTRIM(RTRIM(NULLIF( DH.&quot;Project&quot;, '' ))))        END  --DH.Project is aka BP Project on the doc
  
  WHERE   DH.&quot;CANCELED&quot; = 'N'  --Do not show 'Y' cancelled doc or 'C' cancellation offset doc
  </code></pre>