5 Replies Latest reply on Dec 22, 2015 5:44 PM by Roger Palmen

    Case/decode alternatives

    Rashmi

      Hi, Does anyone have idea what is the alternative of case and decode functions in sql commander? My requirement is for attribute values -1 will be replaced to NaN

        • Re: Case/decode alternatives
          Roger Palmen
          1 of 1 people found this helpful
          • Re: Case/decode alternatives
            pthivierge

            Hello Rashmi,

             

            Roger is right, using the If then else syntax should work for this task, here is an example:

             

            select
                 IF  table_values.a = -1 THEN null ELSE table_values.a as filtered_value
             from
             (select -1 as a
             union all select 0
             union all select 1
             union all select 2
             ) table_values
            
              • Re: Case/decode alternatives
                Rashmi

                Hello Patrice,

                 

                Attached spread sheet format should be my output. The condition is wherever values are -1 for attributes, it will replace as NaN.

                 

                 

                height100speed90speed80speed180speed260speed30speed10speed100direction90direction80direction60direction30direction10direction80temperature10temperature80humidity10humidity80pressure10pressure
                60NaNNaNNaNNaN8.90121NaNNaNNaNNaNNaN316.76NaNNaN4.88355NaN136NaN982.145NaN

                 

                I had written this query, but seems the output is not appearing correctly. I then tried with 'else with if then else, case and coalesce etc,, seems 'Else' keyword does not recognize by 'PI SQL Commander'

                 

                SELECT eh.Name Element, tc.height,

                case tc."100speed" when -1 then 'NaN' end, "100speed",

                    case tc."90speed" when -1 then 'NaN' end, "90speed",

                    case tc."80speed1" when -1 then 'NaN' end, "80speed1",

                    case tc."80speed2" when -1 then 'NaN' end as "80speed2",

                    case tc."60speed" when -1 then 'NaN' end as "60speed",

                    case tc."30speed" when -1 then 'NaN' end as "30speed",

                    case tc."10speed" when -1 then 'NaN' end as "10speed",

                    case tc."100direction" when -1 then 'NaN' end as "100direction",

                    case tc."90direction" when -1 then 'NaN' end as "90direction",

                    case tc."80direction" when -1 then 'NaN' end as "80direction",

                    case tc."60direction" when -1 then 'NaN' end as "60direction",

                    case tc."30direction" when -1 then 'NaN' end as "30direction",

                    case tc."10direction" when -1 then 'NaN' end as "10direction",

                    case tc."80temperature" when -1 then 'NaN' end as "80temperature",

                    case tc."10tempreture" when -1 then 'NaN' end as "10tempreture",

                    case tc."80humidity" when -1 then 'NaN' end as "80humidity",

                    case tc."10humidity" when -1 then 'NaN' end as "10humidity",

                    case tc."80pressure" when -1 then 'NaN' end as "80pressure",

                    case tc."10pressure" when -1 then 'NaN' end as "10pressure"

                FROM(SELECT Date('" + $queryTimeStamp + "') Time) t, [InitialDB].[Asset].[ElementHierarchy] eh

                    CROSS APPLY [InitialDB].[DataT].[TransposeInterpolateDiscrete_F4401T0001H0060](eh.ElementID, t.Time) tc

                    WHERE eh.Path like '%\%'

                    OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)

                 

                please advise

                  • Re: Case/decode alternatives
                    Roger Palmen

                    This worked for me (in a similar query).

                     

                    Try this in SQL commander:

                    SELECT eh.Name Element, tc.height,
                    IF tc.[100speed]=-1 THEN 'NaN' ELSE [100speed] AS 100speed
                    FROM(SELECT Date('" + $queryTimeStamp + "') Time) t, [InitialDB].[Asset].[ElementHierarchy] eh
                        CROSS APPLY [InitialDB].[DataT].[TransposeInterpolateDiscrete_F4401T0001H0060](eh.ElementID, t.Time) tc
                        WHERE eh.Path like '%\%'
                        OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)
                    

                     

                    Note replacing the quotes "" around the 100speed column name with brackets []

                • Re: Case/decode alternatives
                  Rashmi

                  Hello Patrice,

                   

                  the keyword 'else' is not recognizing by PI SQL Commander.

                   

                  Patrice Thivierge

                   

                  Attached spread sheet format should be my output. The condition is wherever values are -1 for attributes, it will replace as NaN.

                   

                   

                  height100speed90speed80speed180speed260speed30speed10speed100direction90direction80direction60direction30direction10direction80temperature10temperature80humidity10humidity80pressure10pressure
                  60NaNNaNNaNNaN8.90121NaNNaNNaNNaNNaN316.76NaNNaN4.88355NaN136NaN982.145NaN

                   

                  I had written this query, but seems correct output is not appearing correctly. I then tried with else, but the error was 'else with if then else, case and coalesce etc,, seems 'Else' keyword does not recognize by 'PI SQL Commander'

                   

                  SELECT eh.Name Element, tc.height,

                  case tc."100speed" when -1 then 'NaN' end, "100speed",

                      case tc."90speed" when -1 then 'NaN' end, "90speed",

                      case tc."80speed1" when -1 then 'NaN' end, "80speed1",

                      case tc."80speed2" when -1 then 'NaN' end as "80speed2",

                      case tc."60speed" when -1 then 'NaN' end as "60speed",

                      case tc."30speed" when -1 then 'NaN' end as "30speed",

                      case tc."10speed" when -1 then 'NaN' end as "10speed",

                      case tc."100direction" when -1 then 'NaN' end as "100direction",

                      case tc."90direction" when -1 then 'NaN' end as "90direction",

                      case tc."80direction" when -1 then 'NaN' end as "80direction",

                      case tc."60direction" when -1 then 'NaN' end as "60direction",

                      case tc."30direction" when -1 then 'NaN' end as "30direction",

                      case tc."10direction" when -1 then 'NaN' end as "10direction",

                      case tc."80temperature" when -1 then 'NaN' end as "80temperature",

                      case tc."10tempreture" when -1 then 'NaN' end as "10tempreture",

                      case tc."80humidity" when -1 then 'NaN' end as "80humidity",

                      case tc."10humidity" when -1 then 'NaN' end as "10humidity",

                      case tc."80pressure" when -1 then 'NaN' end as "80pressure",

                      case tc."10pressure" when -1 then 'NaN' end as "10pressure"

                  FROM(SELECT Date('" + $queryTimeStamp + "') Time) t, [InitialDB].[Asset].[ElementHierarchy] eh

                      CROSS APPLY [InitialDB].[DataT].[TransposeInterpolateDiscrete_F4401T0001H0060](eh.ElementID, t.Time) tc

                      WHERE eh.Path like '%\%'

                      OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)