SQL getting rows with a flag value without using functions-CodePudding

I have a following table

I need to only select all the values with sensorfield1 flag = r and reject the rows with corresponding IDs with flag = h

and select those values for IDs where flag value is only 'h'

I am working on denodo and somehow can not use any agg/window functions because it throws error "(function) is not executable" error. So a solution with no utilization of function would suffice my needs.

Thanks!

required output would be

CodePudding user response：

Try this query:

SELECT sensorID, timestamp, sensorField1 
    FROM table1
    WHERE sensorField1 = 'r'
UNION ALL
SELECT sensorID, timestamp, sensorField1 
    ROM table1
    WHERE sensorField1 = 'h' 
    AND sensorID NOT IN (SELECT DISTINCT sensorID FROM table1 sensorField1 = 'r')

CodePudding user response：

{imparted}

Hi,

VDP does not implement Analytic Functions itself but delegates such functions to the data source, if applicable. For instance, not all data sources support such analytics functions. Thus, when a data source does not support this function this error is thrown.

Thus, I’d recommend to check your Connection tab and make sure you selected “The against adptr ( for example "Oracle 11g")” as database adapter. Further, I’d recommend to check your Data Source Configuration if the Delegate analytic functions list is set (unchecked is fine, as default is all functions get delegated if the underlying data source support this).

Hope this helps!

CodePudding user response：

You may try correlated subquery with exists clause as the following:

Select sensorID, timestamp, sensorField1 
From tbl_name T
Where T.sensorField1 = 'r'
Or
(T.sensorField1 = 'h' And Not Exists (Select 1 From tbl_name D
                                      Where D.sensorID = T.sensorID 
                                      And D.sensorField1 = 'r')
)

See a demo from db<>fiddle.