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

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

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

Hello Patrice,

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

height 100speed 90speed 80speed1 80speed2 60speed 30speed 10speed 100direction 90direction 80direction 60direction 30direction 10direction 80temperature 10temperature 80humidity 10humidity 80pressure 10pressure 60 NaN NaN NaN NaN 8.90121 NaN NaN NaN NaN NaN 316.76 NaN NaN 4.88355 NaN 136 NaN 982.145 NaN 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

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 []

Hello Patrice,

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

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

height 100speed 90speed 80speed1 80speed2 60speed 30speed 10speed 100direction 90direction 80direction 60direction 30direction 10direction 80temperature 10temperature 80humidity 10humidity 80pressure 10pressure 60 NaN NaN NaN NaN 8.90121 NaN NaN NaN NaN NaN 316.76 NaN NaN 4.88355 NaN 136 NaN 982.145 NaN 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)

You can do IF-THEN-ELSE, NULLIF and COALESCE in the queries: https://livelibrary.osisoft.com/LiveLibrary/content/en/oledb-ent-v1/GUID-C45C60B1-0C93-4AD5-A09D-83F23AD2DA30