Jump to content

Cast as Text or Int based on data


Recommended Posts

Total Noob here and trying to modify a bit of SQL as my IT is working on bigger fish so i thought i'd give it a go-

I have a list of data which used to just contain numbers, and now contains text(with dashes "-") and numbers. What i want to happen is if the data is text then cast as text, if data is numeric then cast as Int. The list of data is one or the other- Numbers or Text with dashes.

 

Full SQL: 

    stSQL = "SELECT rou.WST_0, " & _
    "CASE WHEN itm.ALG_0 is NULL THEN 'n/a' WHEN itm.ALG_0 = 'Z' THEN 'n/a' WHEN itm.ALG_0 = '' THEN 'n/a' ELSE itm.ALG_0 END AS [Allergen], " & _
    "mfgh.MFGNUM_0, CASE WHEN mfgi.ITMREF_0 NOT LIKE '%[^0-9]%' THEN CAST (mfgi.ITMREF_0 AS TEXT) ELSE CAST (mfgi.ITMREF_0 AS INT) END, itm.TCLCOD_0, " & _
    "CASE WHEN itm.TSICOD_1 = 'NA' THEN itm.ITMDES1_0 ELSE 'Organic' + ' ' + itm.ITMDES1_0 END as [Description],'',  " & _
    "rou.WSTNBR_0, mfgi.EXTQTY_0, mfgh.STRDAT_0, mfgh.ENDDAT_0, " & _
    "(mfgo.EXTSETTIM_0 + mfgo.EXTOPETIM_0)/60 as [Hours], " & _
    "CASE WHEN itm.ZITMEIGHTY_0 = 2 THEN '80/20' ELSE '' END as [EightyTwentyItem], " & _
    "CASE WHEN rout.ZLASTTSUP_0 = '" & Format("1/1/1753") & "' THEN NULL ELSE rout.ZLASTTSUP_0 END [LastTimeStudyDate], " & _
    "rou.OPENUM_0, " & _
    "REPLACE((CASE WHEN itm.ZCASEQTY_0 = 0 THEN '' ELSE Str(itm.ZCASEQTY_0, 8,0) END), ' ', '') As [CaseQuantity], " & _
    "itm.ZUNITQTY_0, a.Message, '', '', zsd.ZERODAT_0 [PSO Date] " & _
    "FROM GLORYBEE.MFGHEAD mfgh " & _
    "INNER JOIN GLORYBEE.MFGOPE mfgo ON mfgh.MFGNUM_0 = mfgo.MFGNUM_0 " & _
    "INNER JOIN GLORYBEE.MFGITM mfgi ON mfgh.MFGNUM_0 = mfgi.MFGNUM_0 " & _
    "INNER JOIN GLORYBEE.ITMMASTER itm ON mfgi.ITMREF_0 = itm.ITMREF_0 " & _
    "LEFT OUTER JOIN GLORYBEE.ROUTING rout ON mfgi.ITMREF_0 = rout.ITMREF_0 and mfgh.ROUALT_0 = rout.ROUALT_0 and mfgh.MFGFCY_0 = rout.FCY_0 " & _
    "LEFT OUTER JOIN GLORYBEE.ROUOPE rou ON mfgo.MFGFCY_0 = rou.FCY_0 and mfgi.ITMREF_0 = rou.ITMREF_0 and rout.ROUALT_0 = rou.ROUALT_0 and mfgo.OPENUM_0 = rou.OPENUM_0 and mfgo.RPLIND_0 = rou.RPLIND_0 " & _
    "LEFT OUTER JOIN dbo.ZeroStockDate zsd ON mfgi.ITMREF_0 = zsd.ITMREF_0 " & _
    "INNER JOIN GLORYBEE.APLSTD_7003 a ON itm.ZUMDROP_0 = a.ID " & _
    "AND mfgh.STRDAT_0 >= '" & Format(Startdate, "yyyy-mm-dd") & "' AND mfgh.STRDAT_0 <= '" & Format(EndDate, "yyyy-mm-dd") & "' " & _
    "AND (mfgh.MFGTRKFLG_0 = '1' or mfgh.MFGTRKFLG_0 = '2' or mfgh.MFGTRKFLG_0 = '3' or mfgh.MFGTRKFLG_0 = '4') AND (mfgh.MFGSTA_0 = '1' or mfgh.MFGSTA_0 = '2') and (itm.ZPRODDEPT_0 = 1 OR itm.ZPRODDEPT_0 = 2) " & _
    "ORDER BY mfgh.MFGNUM_0, rou.OPENUM_0 " & _
    "option(recompile) "

 

My trouble is in the 3rd line:

CASE WHEN mfgi.ITMREF_0 NOT LIKE '%[^0-9]%' THEN CAST (mfgi.ITMREF_0 AS TEXT) ELSE CAST (mfgi.ITMREF_0 AS INT) END

This SQL is embedded in an Excel Macro and when I try to run this bit, i get an Operand type dash: text is incompatible with int.

 

Thanks for the help!

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...