I have a table with qualities of items which must meet must-have values. But the relations for checking is in a column.

Sample table:

Item	Quality	ActualValue	Relation	MustValue
A	weight	10	<	20
A	volume	5	>=	50
B	volume	12	<=	10

I look for a SELECT that brings up row 2 and 3, as in these rows the ActualValue is out of range of Relation & MustValue.

The result should be:

Item	Quality	ActualValue	Relation	MustValue
A	volume	5	>=	50
B	volume	12	<=	10

I expect there is something like WHERE ActualValue CONVERT(Relation MustValue). Let's assume, Relation and MustValue are text fields and Relation is a valid SQL-relation.

I have no clue what function to search for.

How can be done this? I'm working on an Oracle Db system. Db model is given, I have only access for SELECT.

CodePudding user response：

Nested CASE in the WHERE clause should do the job:

with data(Item, Quality, ActualValue, Relation, MustValue) as (
    select 'A', 'weight', 10, '<', 20 from dual union all
    select 'A', 'volume', 5, '>=', 50 from dual union all
    select 'B', 'volume', 12, '<=', 10 from dual -- union all
)
select * from data
where
    1 = 
    case relation
    when '<' then
        case when MustValue < ActualValue then 1 else 0 end
    when '>=' then
        case when MustValue >= ActualValue then 1 else 0 end
    when '<=' then
        case when MustValue <= ActualValue then 1 else 0 end
    end
;

CodePudding user response：

I have no clue what function to search for.

There is no function in SQL (comparable to EVAL in other languages) that will dynamically evaluate an expression as part of an SQL statement.

Instead, you can use CASE expressions and white-list the relationships you wish to use:

SELECT *
FROM   table_name
WHERE  CASE
       WHEN relation = '<'  THEN CASE WHEN actualvalue <  mustvalue THEN 1 ELSE 0 END
       WHEN relation = '<=' THEN CASE WHEN actualvalue <= mustvalue THEN 1 ELSE 0 END
       WHEN relation = '>'  THEN CASE WHEN actualvalue >  mustvalue THEN 1 ELSE 0 END
       WHEN relation = '>=' THEN CASE WHEN actualvalue >= mustvalue THEN 1 ELSE 0 END
       WHEN relation = '='  THEN CASE WHEN actualvalue =  mustvalue THEN 1 ELSE 0 END
       WHEN relation = '!=' THEN CASE WHEN actualvalue != mustvalue THEN 1 ELSE 0 END
       END = 0;

Which, for the sample data:

CREATE TABLE table_name (Item, Quality, ActualValue, Relation, MustValue) AS
SELECT 'A', 'weight', 10, '<',  20 FROM DUAL UNION ALL
SELECT 'A', 'volume',  5, '>=', 50 FROM DUAL UNION ALL
SELECT 'B', 'volume', 12, '<=', 10 FROM DUAL;

Outputs:

ITEM	QUALITY	ACTUALVALUE	RELATION	MUSTVALUE
A	volume	5	>=	50
B	volume	12	<=	10

fiddle

CodePudding user response：

You can also use xmlquery to evaluate an expression:

with data(Item, Quality, ActualValue, Relation, MustValue) as (
    select 'A', 'weight', 10, '<', 20 from dual union all
    select 'A', 'volume', 5, '>=', 50 from dual union all
    select 'B', 'volume', 12, '<=', 10 from dual -- union all
)
select Item, Quality, ActualValue, Relation, MustValue
from (
    select Item, Quality, ActualValue, Relation, MustValue, MustValue || ' ' || Relation || ' ' || ActualValue
    as Expr from data
)
where xmlquery(Expr returning content).getStringVal() = 'true' 
;


A   volume  5   >=  50
B   volume  12  <=  10

CodePudding user response：

You could write your own function to evaluate the expression:

WITH
    Function eval_expr(expr VarChar2) RETURN Number IS
        BEGIN
            -- returns 1 if True  |  0 if False  |  9 if error
            Declare 
                cmd     VarChar2(512) := 'Select Case When ' || expr || ' Then 1 Else 0 End From Dual' ;
                chk     Number;
            Begin
                execute immediate cmd InTo chk;
                RETURN chk;
            Exception 
                When Others Then RETURN 9;
            End;
        END eval_expr;
    tbl AS    --  S a m p l e    D a t a :
        (   Select 'A' "ITEM", 'weight' "QUALITY", 10 "ACTUAL_VALUE", '<' "RELATION", 20 "MUST_VALUE" From Dual Union All
            Select 'A' "ITEM", 'volume' "QUALITY",  5 "ACTUAL_VALUE", '>=' "RELATION", 50 "MUST_VALUE" From Dual Union All
            Select 'B' "ITEM", 'volume' "QUALITY", 12 "ACTUAL_VALUE", '<=' "RELATION", 10 "MUST_VALUE" From Dual 
        )
--  M a i n   S Q L :
Select  * 
From    tbl
Where   eval_expr(ACTUAL_VALUE || RELATION || MUST_VALUE) = 0
      
/*    R e s u l t :
ITEM QUALITY ACTUAL_VALUE RELATION MUST_VALUE
---- ------  ------------ -------- ----------
A    volume             5 >=               50
B    volume            12 <=               10   */

This will check any valid expression that is used within Case expression's When clause including multiple conditions with AND/OR ...