Converting a varchar to int

  • spyfly

    Mr or Mrs. 500

    Points: 533

    I am running the query below but I am getting an error "Conversion failed when converting the varchar value "T" to data type int"

    Here is the query what am I missing? I also included the functions that I am using

    /* APC UPS State UnDP pollers
    APCupsAdvStateAbnormalConditions
        Status Byte 1 = 'Power Module Failure, '
        Status Byte 2 = 'Main Intelligence Module Failure, '
        Status Byte 3 = 'Redundant Intelligence Module Failure, '
        Status Byte 4 = 'Battery Failure, '
        Status Byte 5 = 'Load(kVA) Alarm Threshold Violation, '
        Status Byte 6 = 'Redundancy Lost, '
        Status Byte 7 = 'Redundancy Below Alarm Threshold, '
        Status Byte 8 = 'Bypass notin Range; Either Frequency or Voltage, '
        Status Byte 9 = 'Bypass Contactor Stuck in Bypass Condition, '
        Status Byte 10 = 'Bypass Contactor Stuck in On-Line Condition, '
        Status Byte 11 = 'In Bypass due to an Internal Fault, '
        Status Byte 12 = 'In Bypass due to an Overload, '
        Status Byte 13 = 'In Maintanence Bypass, '
        Status Byte 14 = 'Input Circuit Braker Tripped Open, '
        Status Byte 15 = 'System Level Fan Failure, '
        Status Byte 16 = 'Redundant Intelligent Module in Control, '
        Status Byte 17 = 'IIC Inter-Module Communication Failure, '
        Status Byte 18 = 'No Working Power Modules, '
        Status Byte 19 = 'Load Shutdown From Bypass; Input Frequency, '
        Status Byte 20 = 'Runtime Below Alarm Threshold, '
        Status Byte 21 = 'Extended Run Frame Fault, '
        Status Byte 22 = 'Output Voltage out of Range, '
        Status Byte 23 = 'UPS Not Synchronized, '
        Status Byte 24 = 'No Batteries Installed, '
        Status Byte 25 = 'Battery Voltage High, '
        Status Byte 26 = 'UPS Specific Fault Detected, '
        Status Byte 27 = 'Site Wiring Fault, '
        Status Byte 28 = 'Backfeed Protection Relay Opened, '
    */
    /*
    APCupsBasicStateOutputState
       Status Byte 1 = 'Abnormal Condition Present, '
       Status Byte 2 = 'On Battery, '
       Status Byte 3 = 'Low Battery, '
       Status Byte 4 = '*online*, '
       Status Byte 5 = 'Replace Battery, '
       Status Byte 6 = '*Serial Communication Established*, '
       Status Byte 7 = 'AVR Boost Active*, '
       Status Byte 8 = 'AVR Trim Active*, '
       Status Byte 9 = 'Overload, '
       Status Byte 10 = 'Runtime Calibration, '
       Status Byte 11 = 'Batteries Discharged, '
       Status Byte 12 = 'Manual Bypass, '
       Status Byte 13 = 'Software Bypass, '
       Status Byte 14 = 'In Bypass due to Internal Fault, '
       Status Byte 15 = 'In Bypass due to Supply Failure*, '
       Status Byte 16 = 'In Bypass due to Fan Failure*, '
       Status Byte 17 = 'Sleeping on a Timer, '
       Status Byte 18 = 'Sleeping until Utility Power Returns, '
       Status Byte 19 = ''*On*, '
       Status Byte 20 = 'Rebooting, '
       Status Byte 21 = 'Battery Communication Lost*, '
       Status Byte 22 = 'Graceful Shutdown Initiated, '
       Status Byte 23 = 'Smart Boost or Smart Trim Fault, '
       Status Byte 24 = 'Bad Output Voltage*, '
       Status Byte 25 = 'Battery Charger Failure*, '
       Status Byte 26 = 'High Battery Temperature, '
       Status Byte 27 = 'Warning Battery Temperature, '
       Status Byte 28 = 'Critical Battery Temperature, '
       Status Byte 29 = 'Self Test In Progress, '
       Status Byte 30 = 'Low Battery / On Battery, '
       Status Byte 31 = 'Graceful Shutdown Issued by Upstream Device, '
       Status Byte 32 = 'Graceful Shutdown Issued by Downstream Device, '
       Status Byte 33 = 'No Batteries Attached, '
       Status Byte 34 = 'Synchronized command is in progress, '
       Status Byte 35 = 'Synchronized Sleeping Command is in Progress, '
       Status Byte 36 = 'Synchronized Rebooting Command is in Progress, '
       Status Byte 37 = 'Inverter DC Imbalance, '
       Status Byte 38 = 'Transfer Relay Failure, '
       Status Byte 39 = 'Shutdown or Unable to Transfer, '
       Status Byte 40 = 'Low Battery Shutdown, '
       Status Byte 41 = 'Electronic Unit Fan Failure, '
       Status Byte 42 = 'Main Relay Failure, '
       Status Byte 43 = 'Bypass Relay Failure, '
       Status Byte 44 = 'Temporary Bypass, '
       Status Byte 45 = 'High Internal Temperature'
       Status Byte 46 = 'Battery Temperature Sensor Fault, '
       Status Byte 47 = 'Input Out of Range for Bypass, '
       Status Byte 48 = 'DC Bus Overvoltage, '
       Status Byte 49 = 'PFC Failure, '
       Status Byte 50 = 'Critical Hardware Fault, '
       Status Byte 51 = 'Green Mode, '
       Status Byte 52 = 'Flag-52 set, '
       Status Byte 53 = 'Flag-53 set, '
       Status Byte 54 = 'Flag-54 set, '
       Status Byte 55 = 'Flag-55 set, '
       Status Byte 56 = 'Flag-56 set, '
       Status Byte 57 = 'Flag-57 set, '
       Status Byte 58 = 'Flag-58 set'
       Status Byte 59 = 'Flag-59 set, '
       Status Byte 60 = 'Flag-60 set, '
       Status Byte 61 = 'Flag-61 set, '
       Status Byte 62 = 'Flag-62 set, '
       Status Byte 63 = 'Flag-63 set, '
       Status Byte 64 = 'Flag-64 set, '
    */

    SELECT Nodes.Caption AS Node_Name, Nodes.NodeID AS NodeID,
    CustomPollerStatus3.Status as Model,
    dbo.MDOT_upsAdvStateAbnormalConditions_FlagParser(CustomPollerStatus.Status) as AbnormalConditions,
    dbo.MDOT_APCupsBasicStateOutputState_FlagParser(CustomPollerStatus1.Status) as APCupsBasicStateOutputState,
    CustomPollerStatus2.Status as TestDiagnosticsResults
     FROM Nodes
       JOIN CustomPollerAssignment
          ON (CustomPollerAssignment.NodeId = Nodes.NodeId )
       JOIN CustomPollers
          ON (CustomPollers.CustomPollerId = CustomPollerAssignment.CustomPollerId AND CustomPollers.UniqueName = 'APCupsAdvStateAbnormalConditions')
       LEFT JOIN CustomPollerStatus
          ON (CustomPollerStatus.CustomPollerAssignmentId = CustomPollerAssignment.CustomPollerAssignmentId)
       JOIN CustomPollerAssignment AS CustomPollerAssignment1
          ON (CustomPollerAssignment1.NodeId = Nodes.NodeId )
       JOIN CustomPollers AS CustomPollers1
          ON (CustomPollers1.CustomPollerId = CustomPollerAssignment1.CustomPollerId AND CustomPollers1.UniqueName = 'APCupsBasicStateOutputState')
       LEFT JOIN CustomPollerStatus AS CustomPollerStatus1
          ON (CustomPollerStatus1.CustomPollerAssignmentId = CustomPollerAssignment1.CustomPollerAssignmentId)
    JOIN CustomPollerAssignment AS CustomPollerAssignment2
          ON (CustomPollerAssignment2.NodeId = Nodes.NodeId )
       JOIN CustomPollers AS CustomPollers2
          ON (CustomPollers2.CustomPollerId = CustomPollerAssignment2.CustomPollerId AND CustomPollers2.UniqueName = 'APcupsAdvTestDiagnosticsResults')
       LEFT JOIN CustomPollerStatus AS CustomPollerStatus2
          ON (CustomPollerStatus2.CustomPollerAssignmentId = CustomPollerAssignment2.CustomPollerAssignmentId)
    JOIN CustomPollerAssignment AS CustomPollerAssignment3
          ON (CustomPollerAssignment3.NodeId = Nodes.NodeId )
       JOIN CustomPollers AS CustomPollers3
          ON (CustomPollers3.CustomPollerId = CustomPollerAssignment3.CustomPollerId AND CustomPollers3.UniqueName = 'APCupsBasicIdentModel')
       LEFT JOIN CustomPollerStatus AS CustomPollerStatus3
          ON (CustomPollerStatus3.CustomPollerAssignmentId = CustomPollerAssignment3.CustomPollerAssignmentId)
    WHERE (Nodes.Status = '1')
     AND
    (
     (CustomPollerStatus1.Status <> '0001010000000000000000000000000000000000000000000000000000000000'
     AND CustomPollerStatus1.Status <> '0001010000000000001000000000000000000000000000000000000000000000'
     AND CustomPollerStatus1.Status <> '0001010000000000001000000000000000000000000000000010000000000000'
     AND CustomPollerStatus1.Status <> '0000000000000000000000000000000000000000000000000000000000000000')
       OR (CustomPollerStatus2.Status = 'failed(2)'
       OR CustomPollerStatus2.Status = 'invalidTest(3)')
    )
    ORDER by 1 ASC

    USE [NetPrefMon]
    GO
    /****** Object:  UserDefinedFunction [dbo].[MDOT_upsAdvStateAbnormalConditions_FlagParser]    Script Date: 11/29/2017 8:28:54 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:  <Spyfly>
    -- Create date: <Nov. 11, 2017>
    -- Description: <MDOT_upsAdvStateAbnormalConditions_FlagParser>
    -- =============================================
    ALTER function [dbo].[MDOT_upsAdvStateAbnormalConditions_FlagParser]
    (
        @val as varchar(33)
    )
    returns varchar(1000)
    as
    Begin
     DECLARE @counter as int;
     DECLARE @flaglist as varchar(33);
     DECLARE @flagpos as int;
     DECLARE @varlist as varchar(1000);

     SET @counter = 0;
     IF @val LIKE 'NOT SUPPORTED%'
      Return '';
     SET @flaglist = @val;
     SET @flagpos = 0;
     SET @varlist = '';
     while @counter < 32
     Begin
      set @varlist = @varlist +CASE
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 1 THEN 'Power Module Failure, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 2 THEN 'Main Intelligence Module Failure, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 3 THEN 'Redundant Intelligence Module Failure, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 4 THEN 'Battery Failure, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 5 THEN 'Load(kVA) Alarm Threshold Violation, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 6 THEN 'Redundancy Lost, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 7 THEN 'Redundancy Below Alarm Threshold, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 8 THEN 'Bypass notin Range; Either Frequency or Voltage, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 9 THEN 'Bypass Contactor Stuck in Bypass Condition, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 10 THEN 'Bypass Contactor Stuck in On-Line Condition, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 11 THEN 'In Bypass due to an Internal Fault, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 12 THEN 'In Bypass due to an Overload, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 13 THEN 'In Maintanence Bypass, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 14 THEN 'Input Circuit Braker Tripped Open, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 15 THEN 'System Level Fan Failure, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 16 THEN 'Redundant Intelligent Module in Control, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 17 THEN 'IIC Inter-Module Communication Failure, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 18 THEN 'No Working Power Modules, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 19 THEN 'Load Shutdown From Bypass; Input Frequency, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 20 THEN 'Runtime Below Alarm Threshold, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 21 THEN 'Extended Run Frame Fault, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 22 THEN 'Output Voltage out of Range, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 23 THEN 'UPS Not Synchronized, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 24 THEN 'No Batteries Installed, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 25 THEN 'Battery Voltage High, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 26 THEN 'UPS Specific Fault Detected, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 27 THEN 'Site Wiring Fault, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 28 THEN 'Backfeed Protection Relay Opened, '
       Else ''
       End
      set @counter = @counter + 1
      set @flagpos = @flagpos + 1
     End
     return substring(@varlist,0,len(@varlist))
    End

    USE [SolarWindsOrion]
    GO
    /****** Object:  UserDefinedFunction [dbo].[MDOT_APCupsBasicStateOutputState_FlagParser]    Script Date: 11/29/2017 8:31:25 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:  <Spyfly>
    -- Create date: <Nov. 11, 2017>
    -- Description: <MDOT_APCupsBasicStateOutputState_FlagParser>
    -- =============================================
    ALTER function [dbo].[MDOT_APCupsBasicStateOutputState_FlagParser]
    (
        @val as varchar(64)
    )
    returns varchar(1000)
    as
    Begin
     DECLARE @counter as int;
     DECLARE @flaglist as varchar(64);
     DECLARE @flagpos as int;
     DECLARE @varlist as varchar(1000);

     SET @counter = 0;
     SET @flaglist = @val;
     SET @flagpos = 0;
     SET @varlist = '';
     while @counter < 64
     Begin
      set @varlist = @varlist + CASE
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 1 THEN 'Abnormal Condition Present, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 2 THEN 'On Battery, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 3 THEN 'Low Battery, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 4 THEN '' /*online*/
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 5 THEN 'Replace Battery, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 6 THEN '' /*Serial Communication Established*/
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 7 THEN 'AVR Boost Active*, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 8 THEN 'AVR Trim Active*, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 9 THEN 'Overload, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 10 THEN 'Runtime Calibration, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 11 THEN 'Batteries Discharged, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 12 THEN 'Manual Bypass, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 13 THEN 'Software Bypass, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 14 THEN 'In Bypass due to Internal Fault, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 15 THEN 'In Bypass due to Supply Failure*, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 16 THEN 'In Bypass due to Fan Failure*, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 17 THEN 'Sleeping on a Timer, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 18 THEN 'Sleeping until Utility Power Returns, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 19 THEN '' /*On*/
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 20 THEN 'Rebooting, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 21 THEN 'Battery Communication Lost*, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 22 THEN 'Graceful Shutdown Initiated, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 23 THEN 'Smart Boost or Smart Trim Fault, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 24 THEN 'Bad Output Voltage*, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 25 THEN 'Battery Charger Failure*, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 26 THEN 'High Battery Temperature, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 27 THEN 'Warning Battery Temperature, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 28 THEN 'Critical Battery Temperature, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 29 THEN 'Self Test In Progress, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 30 THEN 'Low Battery / On Battery, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 31 THEN 'Graceful Shutdown Issued by Upstream Device, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 32 THEN 'Graceful Shutdown Issued by Downstream Device, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 33 THEN 'No Batteries Attached, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 34 THEN 'Synchronized command is in progress, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 35 THEN 'Synchronized Sleeping Command is in Progress, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 36 THEN 'Synchronized Rebooting Command is in Progress, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 37 THEN 'Inverter DC Imbalance, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 38 THEN 'Transfer Relay Failure, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 39 THEN 'Shutdown or Unable to Transfer, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 40 THEN 'Low Battery Shutdown, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 41 THEN 'Electronic Unit Fan Failure, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 42 THEN 'Main Relay Failure, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 43 THEN 'Bypass Relay Failure, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 44 THEN 'Temporary Bypass, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 45 THEN 'High Internal Temperature'
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 46 THEN 'Battery Temperature Sensor Fault, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 47 THEN 'Input Out of Range for Bypass, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 48 THEN 'DC Bus Overvoltage, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 49 THEN 'PFC Failure, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 50 THEN 'Critical Hardware Fault, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 51 THEN 'Green Mode, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 52 THEN 'Flag-52 set, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 53 THEN 'Flag-53 set, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 54 THEN 'Flag-54 set, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 55 THEN 'Flag-55 set, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 56 THEN 'Flag-56 set, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 57 THEN 'Flag-57 set, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 58 THEN 'Flag-58 set'
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 59 THEN 'Flag-59 set, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 60 THEN 'Flag-60 set, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 61 THEN 'Flag-61 set, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 62 THEN 'Flag-62 set, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 63 THEN 'Flag-63 set, '
       WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 64 THEN 'Flag-64 set, '
       Else ''
       End
      set @counter = @counter + 1
      set @flagpos = @flagpos + 1
     End
     return substring(@varlist,0,len(@varlist))
    End
  • Eirikur Eiriksson

    SSC Guru

    Points: 182343

    The first thing you are missing is most likely within the data, my guess would be incompatible join columns. The next thing would be properly handling the bit-mask data, much more efficiently done using bitwise logic rather than an iteration. The last thing would be posting a portion of the data, including the part causing the error. Without that, we are only guessing πŸ˜‰
    😎

  • ChrisM@Work

    SSC Guru

    Points: 186040

    Check your data:

    SELECT [Status]

    FROM CustomPollerStatus

    WHERE [Status] LIKE '%T%'

    Your @flaglist variable is varchar but you compare it (or rather, a substring of it) to a numeric:
    WHEN SUBSTRING(@flaglist,@flagpos,1)=1 and @counter = 31 THEN 'Graceful Shutdown Issued by Upstream Device, '

    You could change that to a varchar too:
    WHEN SUBSTRING(@flaglist,@flagpos,1) = '1' and @counter = 31 THEN 'Graceful Shutdown Issued by Upstream Device, '

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply