le compte est bon

I am back from my vacations, I was at nice places in Switzerland like Rhone Glacier, underground lake of Saint-Leonard, Salt Mines of Bex, Rhine Waterfalls and more …

To keep up with the fun, here is a little quiz :

You have the numbers 1-3-4-6 and you need to achieve the number 24. The allowed operations are +, -, * and /

If I try to achieve 49 it is easy :

SQL> /
Enter value for n1: 1
old  14:      (SELECT &n1 n
new  14:      (SELECT 1 n
Enter value for n2: 3
old  17:       SELECT &n2 n
new  17:       SELECT 3 n
Enter value for n3: 4
old  20:       SELECT &n3 n
new  20:       SELECT 4 n
Enter value for n4: 6
old  23:       SELECT &n4 n
new  23:       SELECT 6 n
Enter value for result: 49
old 143:              ) = &result
new 143:              ) = 49

result
------------------------------------
(4+3)*(6+1)
(3+4)*(6+1)
(6+1)*(4+3)
(1+6)*(4+3)
(6+1)*(3+4)
(1+6)*(3+4)
(4+3)*(1+6)
(3+4)*(1+6)

8 rows selected.

Elapsed: 00:00:11.28

But for 24 it is not that simple :-) at least for human !

Ok, in SQL I am using a plsql function to evaluate expression

CREATE OR REPLACE FUNCTION lsc_eval (expr VARCHAR2)
   RETURN NUMBER
IS
   x   NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'begin :x := ' || expr || ';end;'
               USING OUT x;
   RETURN x;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
END;
/

I will post the rest of the code as a comment later ;)

20 thoughts on “le compte est bon”

  1. If every number should be used only once and every operator can be reused, the only thing you need to find it – is RPN calculator. There are some interesting approaches on the net regarding the latter, but with very simple brute force attempt – it seems, you have only 56 positive results for your puzzle and 24 is not among of them… (of course, if my logic is not flawed ;-))

    /* Formatted on 2009/08/20 09:34 (Formatter Plus v4.8.8) */
    WITH t AS
         (SELECT 1 n
            FROM DUAL
          UNION ALL
          SELECT 3
            FROM DUAL
          UNION ALL
          SELECT 4
            FROM DUAL
          UNION ALL
          SELECT 6
            FROM DUAL),
         o AS
         (SELECT ' + ' op
            FROM DUAL
          UNION ALL
          SELECT ' - '
            FROM DUAL
          UNION ALL
          SELECT ' * '
            FROM DUAL
          UNION ALL
          SELECT ' / '
            FROM DUAL)
    SELECT DISTINCT RESULT
               FROM (SELECT t1.n n1, o1.op op1, t2.n n2, o2.op op2, t3.n n3,
                            o3.op op3, t4.n n4,
                            lsc_eval (   lsc_eval (   lsc_eval (   t1.n
                                                                || o1.op
                                                                || t2.n
                                                               )
                                                   || o2.op
                                                   || t3.n
                                                  )
                                      || o3.op
                                      || t4.n
                                     ) RESULT,
                               '(('
                            || t1.n
                            || o1.op
                            || t2.n
                            || ')'
                            || o2.op
                            || t3.n
                            || ')'
                            || o3.op
                            || t4.n expr
                       FROM t t1, t t2, t t3, t t4, o o1, o o2, o o3
                      WHERE t1.n <> t2.n
                        AND t1.n <> t3.n
                        AND t2.n <> t3.n
                        AND t1.n <> t4.n
                        AND t2.n <> t4.n
                        AND t3.n <> t4.n
                     UNION ALL
                     SELECT t1.n n1, o1.op op1, t2.n n2, o2.op op2, t3.n n3,
                            o3.op op3, t4.n n4,
                            lsc_eval (   lsc_eval (t1.n || o1.op || t2.n)
                                      || o2.op
                                      || lsc_eval (t3.n || o3.op || t4.n)
                                     ) RESULT,
                               '('
                            || t1.n
                            || o1.op
                            || t2.n
                            || ')'
                            || o2.op
                            || '('
                            || t3.n
                            || o3.op
                            || t4.n
                            || ')'
                       FROM t t1, t t2, t t3, t t4, o o1, o o2, o o3
                      WHERE t1.n <> t2.n
                        AND t1.n <> t3.n
                        AND t2.n <> t3.n
                        AND t1.n <> t4.n
                        AND t2.n <> t4.n
                        AND t3.n <> t4.n)
              WHERE MOD (RESULT, 1) = 0 AND RESULT > 0
           ORDER BY RESULT

    [laurent schneider] indeed you miss 24 :)

  2. WITH lp AS
         (SELECT '' l
            FROM DUAL
          UNION ALL
          SELECT '('
            FROM DUAL
          UNION ALL
          SELECT '(('
            FROM DUAL),
         digit AS
         (SELECT 1 n
            FROM DUAL
          UNION ALL
          SELECT 3 n
            FROM DUAL
          UNION ALL
          SELECT 4 n
            FROM DUAL
          UNION ALL
          SELECT 6 n
            FROM DUAL),
         rp AS
         (SELECT '' r
            FROM DUAL
          UNION ALL
          SELECT ')'
            FROM DUAL
          UNION ALL
          SELECT '))'
            FROM DUAL),
         op AS
         (SELECT '+' o
            FROM DUAL
          UNION ALL
          SELECT '-'
            FROM DUAL
          UNION ALL
          SELECT '*'
            FROM DUAL
          UNION ALL
          SELECT '/'
            FROM DUAL)
    SELECT    l1.l
           || d1.n
           || o1.o
           || l2.l
           || d2.n
           || r2.r
           || o2.o
           || l3.l
           || d3.n
           || r3.r
           || o3.o
           || d4.n
           || r4.r result
      FROM lp l1,
           digit d1,
           op o1,
           lp l2,
           digit d2,
           rp r2,
           op o2,
           lp l3,
           digit d3,
           rp r3,
           op o3,
           digit d4,
           rp r4
     WHERE 1 = 1
       AND (l2.l IS NULL OR r2.r IS NULL)
       AND (l3.l IS NULL OR r3.r IS NULL)
       AND (   r2.r IS NOT NULL
            OR l3.l IS NOT NULL
            OR (LENGTH (NVL (l2.l, 0)) < 2 OR LENGTH (NVL (r3.r, 0)) < 2)
           )
       AND (   r2.r IS NOT NULL
            OR l3.l IS NOT NULL
            OR r3.r IS NOT NULL
            OR (LENGTH (NVL (l2.l, 0)) < 2 OR LENGTH (NVL (r4.r, 0)) < 2)
           )
       AND (   r2.r IS NOT NULL
            OR l3.l IS NOT NULL
            OR l2.l IS NOT NULL
            OR (LENGTH (NVL (l1.l, 0)) < 2 OR LENGTH (NVL (r3.r, 0)) < 2)
           )
       AND (   l2.l IS NOT NULL
            OR (LENGTH (NVL (l1.l, 0)) < 2 OR LENGTH (NVL (r2.r, 0)) < 2)
           )
       AND (   r3.r IS NOT NULL
            OR (LENGTH (NVL (l3.l, 0)) < 2 OR LENGTH (NVL (r4.r, 0)) < 2)
           )
       AND (   l2.l IS NOT NULL
            OR r2.r IS NOT NULL
            OR l3.l IS NOT NULL
            OR r3.r IS NOT NULL
            OR l1.l IS NULL
            OR r4.r IS NULL
           )
       AND LENGTH (l1.l || l2.l || l3.l) = 2
       AND LENGTH (r2.r || r3.r || r4.r) = 2
       AND (l1.l IS NULL OR r2.r IS NOT NULL OR r3.r IS NOT NULL)
       AND (r4.r IS NULL OR l2.l IS NOT NULL OR l3.l IS NOT NULL)
       AND LENGTH (l1.l || r4.r) <= 2
       AND NVL (LENGTH (l1.l), 0) + NVL (LENGTH (l2.l), 0) >=
                                                            NVL (LENGTH (r2.r), 0)
       AND NVL (LENGTH (l1.l), 0) + NVL (LENGTH (l2.l), 0)
           + NVL (LENGTH (l3.l), 0) >=
                                    NVL (LENGTH (r2.r), 0)
                                    + NVL (LENGTH (r3.r), 0)
       AND NVL (LENGTH (l1.l), 0) + NVL (LENGTH (l2.l), 0)
           + NVL (LENGTH (l3.l), 0) =
                NVL (LENGTH (r2.r), 0)
              + NVL (LENGTH (r3.r), 0)
              + NVL (LENGTH (r4.r), 0)
       AND d1.n NOT IN (d2.n, d3.n, d4.n)
       AND d2.n NOT IN (d3.n, d4.n)
       AND d3.n NOT IN (d4.n)
       AND NVL (LENGTH (r2.r), 0) <= 1
       AND NVL (LENGTH (r3.r), 0) <= 2
       AND NVL (LENGTH (l2.l), 0) <= 2
       AND NVL (LENGTH (l3.l), 0) <= 1
       AND (d1.n<d2.n or l2.l is not null or o1.o not in ('*','+'))
       AND (d2.n<d3.n or l3.l is not null or r2.r is not null or o2.o not in ('*','+'))
       AND (d3.n<d4.n or r3.r is not null or o3.o not in ('*','+'))
       AND (l1.l is null or l2.l is null or r3.r is null or r4.r is null)
       and (o1.o!='+' or o2.o!='+' or r2.r is not null)
       and (o1.o!='*' or o2.o!='*' or r2.r is not null)
       and (o1.o!='+' or o2.o!='+' or o3.o!='+' or (d1.n<d2.n and d2.n<d3.n and d3.n<d4.n and r4.r is null))
       and (o1.o!='*' or o2.o!='*' or o3.o!='*' or (d1.n<d2.n and d2.n<d3.n and d3.n<d4.n and r4.r is null))
       and (o1.o!='+' or o2.o!='+' or r3.r is null or r2.r is null or (d1.n<d2.n and d2.n<d3.n))
       and (o1.o!='*' or o2.o!='*' or r3.r is null or r2.r is null or (d1.n<d2.n and d2.n<d3.n))
       and (o2.o!='+' or o3.o!='+' or l2.l!='((' or l2.l is null or (d2.n<d3.n and d3.n<d4.n))
       and (o2.o!='*' or o3.o!='*' or l2.l!='((' or l2.l is null or (d2.n<d3.n and d3.n<d4.n))
       and (o2.o!='+' or o3.o!='+' or o1.o='+')
       and (o2.o!='*' or o3.o!='*' or o1.o='*')
       and (l1.l is not null or o1.o not in ('+','*'))
       and (l2.l is null or r3.r is null or o1.o!='*' or o3.o!='*')
       and (l2.l is null or r3.r is null or o1.o!='+' or o3.o!='+')
       and (l1.l is null or l2.l is null or l1.l='((' or l2.l='((' or (o1.o!='+' and o2.o!='*'))
       and (l1.l is null or l2.l is null or l1.l='((' or l2.l='((' or (o1.o!='*' and o2.o!='+'))
       and (o2.o not in ('+','*') or r2.r is null or l3.l is null or l1.l is null or r4.r is null or d1.n<d3.n)
          AND ROUND (lsc_eval (   l1.l
                            || d1.n
                            || o1.o
                            || l2.l
                            || d2.n
                            || r2.r
                            || o2.o
                            || l3.l
                            || d3.n
                            || r3.r
                            || o3.o
                            || d4.n
                            || r4.r
                           ),
                  4
                 ) = &n;

    most of the code is to avoid solution like (1)+(1) ((1+1)) and other meaningless duplicates :)

    runs in one second now

  3. an untuned version would be

    
    WITH lp AS (SELECT column_value l
            FROM table(sys.odcivarchar2list('','(','(('))),
         digit AS (SELECT 
    column_value n FROM table(sys.odcinumberlist(1,3,4,6))),
         rp AS (SELECT replace(l,'(',')') r FROM lp),
         op AS (SELECT column_value o
            FROM table(sys.odcivarchar2list('+','-','*','/')))
    SELECT  * FROM lp l1, digit d1, op o1, lp l2, digit d2, rp r2, op o2,
           lp l3, digit d3, rp r3, op o3, digit d4, rp r4
     WHERE  d1.n NOT IN (d2.n, d3.n, d4.n)
       AND d2.n NOT IN (d3.n, d4.n)
       AND d3.n NOT IN (d4.n)
       AND lsc_eval ( l1.l|| d1.n|| o1.o|| l2.l|| d2.n|| r2.r|| o2.o|| l3.l|| d3.n|| r3.r|| 
          o3.o|| d4.n|| r4.r) = 24;
    

    but it will get a lot of duplicates and takes ages

  4. Pingback: le compte est bon | Oracle
  5. Laurent,
    nice fun indeed !

    I like the idea of your lsc_eval function and I remember a thread on the dizwell.com forum where I used the same function to solve a similar question.
    However, we all know, that you should probably not use it due to sql injection and so on, so here is a solution of your question using polish notation http://en.wikipedia.org/wiki/Polish_notation
    and not inflating or even poisoning the shared pool :

    create or replace function ev(xpr in varchar2) return number is
    i int;
    op1 varchar2(32); op1n number;
    op2 varchar2(32); op2n number;
    op varchar2(1);
    n varchar2(1);
    nco int;
    nnco int;
    begin
    op := substr(xpr, 1, 1);
    if op not in (‘+’, ‘-’, ‘*’, ‘/’) then
    begin
    return to_number(xpr);
    exception when others then null;
    end;
    end if;
    nco := 0; nnco := 0;
    for i in 2..length(xpr) loop
    n := substr(xpr, i, 1);
    op1 := concat(op1, n);
    if n between ’0′ and ’9′ then
    nco := nco + 1;
    else
    nnco := nnco + 1;
    end if;
    if nco = nnco + 1 then
    op1n := ev(op1);
    op2n := ev(substr(xpr, i + 1));
    return
    case op
    when ‘+’ then op1n + op2n
    when ‘-’ then op1n – op2n
    when ‘*’ then op1n * op2n
    when ‘/’ then op1n / op2n
    end;
    end if;
    end loop;
    exception when others then return null;
    end ev;
    /

    with data as
    (
    select
    decode(
    level,
    1, ’1+’,
    2, ’3-’,
    3, ’4*’,
    4, ’6/’
    ) d
    from dual
    connect by level <= 4
    ),
    n as
    (
    select
    substr(d, 1, 1) n
    from data
    ),
    o as
    (
    select
    substr(d, 2, 1) o
    from data
    ),
    ps_ as
    (
    select
    o1.o || n1.n || o2.o || n2.n || o3.o || n3.n || n4.n xpr1,
    o1.o || o2.o || n1.n || o3.o || n2.n || n3.n || n4.n xpr2,
    o1.o || n1.n || o2.o || o3.o || n2.n || n3.n || n4.n xpr3,
    o1.o || o2.o || o3.o || n1.n || n2.n || n3.n || n4.n xpr4
    from
    o o1, o o2, o o3,
    n n1, n n2, n n3, n n4
    where n1.n not in (n2.n, n3.n, n4.n)
    and n2.n not in (n3.n, n4.n)
    and n3.n not in (n4.n)
    ),
    ps as
    (
    select xpr1 xpr
    from ps_
    union all
    select xpr2
    from ps_
    union all
    select xpr3
    from ps_
    union all
    select xpr4
    from ps_
    )
    select * from ps
    where ev( xpr ) = &n
    /

    Regards
    Matthias Rogel

  6. small correction (forgot xpr5)

    with data as
    (
    select
    decode(
    level,
    1, ’1+’,
    2, ’3-’,
    3, ’4*’,
    4, ’6/’
    ) d
    from dual
    connect by level <= 4
    ),
    n as
    (
    select
    substr(d, 1, 1) n
    from data
    ),
    o as
    (
    select
    substr(d, 2, 1) o
    from data
    ),
    ps_ as
    (
    select
    o1.o || n1.n || o2.o || n2.n || o3.o || n3.n || n4.n xpr1,
    o1.o || o2.o || n1.n || o3.o || n2.n || n3.n || n4.n xpr2,
    o1.o || n1.n || o2.o || o3.o || n2.n || n3.n || n4.n xpr3,
    o1.o || o2.o || o3.o || n1.n || n2.n || n3.n || n4.n xpr4,
    o1.o || o2.o || n1.n || n2.n || o3.o || n3.n || n4.n xpr5
    from
    o o1, o o2, o o3,
    n n1, n n2, n n3, n n4
    where n1.n not in (n2.n, n3.n, n4.n)
    and n2.n not in (n3.n, n4.n)
    and n3.n not in (n4.n)
    ),
    ps as
    (
    select xpr1 xpr
    from ps_
    union all
    select xpr2
    from ps_
    union all
    select xpr3
    from ps_
    union all
    select xpr4
    from ps_
    union all
    select xpr5
    from ps_
    )
    select * from ps
    where round(ev( xpr) , 4 ) = &n
    /

  7. Pingback: TSQL Challenge #12 Using Date Functions And Recursive CTE, Laurent Schneider Fun Stuff at Waldar’s SQLing and Datawarehousing Place
  8. we do not need dynamic sql actually. dynamic sql is slow and evil :)

    
    /* Formatted on 2009/08/25 15:53 (Formatter Plus v4.8.8) */
    WITH t AS
         (SELECT 1 x
            FROM DUAL
          UNION ALL
          SELECT 3
            FROM DUAL
          UNION ALL
          SELECT 4
            FROM DUAL
          UNION ALL
          SELECT 6
            FROM DUAL),
         o AS
         (SELECT '+' o
            FROM DUAL
          UNION ALL
          SELECT '*'
            FROM DUAL
          UNION ALL
          SELECT '-'
            FROM DUAL
          UNION ALL
          SELECT '/'
            FROM DUAL),
         t1 AS
         (SELECT x x1
            FROM t),
         t2 AS
         (SELECT x1, o o1, x x2,
                 DECODE (o,
                         '+', x1 + x,
                         '*', x1 * x,
                         '/', x1 / NULLIF (x, 0),
                         '-', x1 - x
                        ) r1
            FROM t1, t, o
           WHERE x1 != x),
         t3 AS
         (SELECT 1 id1, x1, o1, x2, o o2, x x3, r1,
                 DECODE (o,
                         '+', r1 + x,
                         '*', r1 * x,
                         '/', r1 / NULLIF (x, 0),
                         '-', r1 - x
                        ) r2
            FROM t2, t, o
           WHERE x NOT IN (x1, x2)
          UNION ALL
          SELECT 2 id1, x1, o1, x2, o o2, x x3, r1,
                 DECODE (o,
                         '+', x + r1,
                         '*', x * r1,
                         '/', x / NULLIF (r1, 0),
                         '-', x - r1
                        ) r2
            FROM t2, t, o
           WHERE x NOT IN (x1, x2)),
         t4 AS
         (SELECT id1, 1 id2, x1, o1, x2, o2, x3, o o3, x x4, r1, r2,
                 DECODE (o,
                         '+', r2 + x,
                         '*', r2 * x,
                         '/', r2 / NULLIF (x, 0),
                         '-', r2 - x
                        ) r3
            FROM t3, t, o
           WHERE x NOT IN (x1, x2, x3)
          UNION ALL
          SELECT id1, 2 id2, x1, o1, x2, o2, x3, o o3, x x4, r1, r2,
                 DECODE (o,
                         '+', x + r2,
                         '*', x * r2,
                         '/', x / NULLIF (r2, 0),
                         '-', x - r2
                        ) r3
            FROM t3, t, o
           WHERE x NOT IN (x1, x2, x3)
          UNION ALL
          SELECT NULL, 3 id2, t21.x1, t21.o1, t21.x2, o o2, t22.x1 x3, t22.o1 o3,
                 t22.x2 x4, t21.r1 r1, t22.r1 r2,
                 DECODE (o,
                         '+', t21.r1 + t22.r1,
                         '*', t21.r1 * t22.r1,
                         '/', t21.r1 / NULLIF (t22.r1, 0),
                         '-', t21.r1 - t22.r1
                        ) r3
            FROM t2 t21, t2 t22, o
           WHERE t21.x1 NOT IN (t22.x1, t22.x2) AND t21.x2 NOT IN
                                                                 (t22.x1, t22.x2))
    SELECT DECODE (id2,
                   1,  '('
                    || DECODE (id1,
                               1, '(' || x1 || o1 || x2 || ')' || o2 || x3,
                               2, x3 || o2 || '(' || x1 || o1 || x2 || ')'
                              )
                    || ')'
                    || o3
                    || x4,
                   2,  x4
                    || o3
                    || '('
                    || DECODE (id1,
                               1, '(' || x1 || o1 || x2 || ')' || o2 || x3,
                               2, x3 || o2 || '(' || x1 || o1 || x2 || ')'
                              )
                    || ')',
                   3, '(' || x1 || o1 || x2 || ')' || o2 || '(' || x3 || o3 || x4
                    || ')'
                  ) expr,
           r3
      FROM t4
     WHERE ROUND (r3, 6) = 24;
    
    Elapsed: 00:00:00.00
    

    :):)

  9. Nine line from the end, you should transpose o1 and o2 :
    2, x3 || o1 || ‘(‘ || x1 || o2 || x2 || ‘)’
    –>
    2, x3 || o2 || ‘(‘ || x1 || o1 || x2 || ‘)’

  10. Here’s mine, I did it one year ago:

    create table t24 (n number);
    INSERT INTO t24 VALUES (1);
    INSERT INTO t24 VALUES (6);
    INSERT INTO t24 VALUES (3);
    INSERT INTO t24 VALUES (4);

    create table o24 (o varchar2 (1));
    INSERT INTO o24 VALUES (‘+’);
    INSERT INTO o24 VALUES (‘-’);
    INSERT INTO o24 VALUES (‘*’);
    INSERT INTO o24 VALUES (‘/’);

    COMMIT;

    WITH vw_tmp AS (
    SELECT DECODE(o1.o,’+',a.n+b.n
    ,’-',a.n-b.n
    ,’*',a.n*b.n
    ,’/',DECODE(b.n,0,NULL,a.n/b.n)
    ) AS ab
    ,DECODE(o2.o,’+',b.n+c.n
    ,’-',b.n-c.n
    ,’*',b.n*c.n
    ,’/',DECODE(c.n,0,NULL,b.n/c.n)
    ) AS bc
    ,DECODE(o3.o,’+',c.n+d.n
    ,’-',c.n-d.n
    ,’*',c.n*d.n
    ,’/',DECODE(d.n,0,NULL,c.n/d.n)
    ) AS cd
    ,a.n as a
    ,o1.o as o1
    ,b.n as b
    ,o2.o as o2
    ,c.n as c
    ,o3.o as o3
    ,d.n as d
    from t24 a, t24 b,t24 c,t24 d,o24 o1,o24 o2,o24 o3
    WHERE a.rowid NOT IN (b.rowid,c.rowid,d.rowid)
    AND b.rowid NOT IN (c.rowid,d.rowid)
    AND c.rowidd.rowid
    )
    ,vw_tmp2 AS (
    SELECT vw_tmp.*
    ,DECODE( o2,’+',ab+c
    ,’-',ab-c
    ,’*',ab*c
    ,’/',DECODE(c,0,NULL,ab/c)
    ) AS ab_c
    ,DECODE( o1,’+',a+bc
    ,’-',a-bc
    ,’*',a*bc
    ,’/',DECODE(bc,0,NULL,a/bc)
    ) AS a_bc
    ,DECODE( o3,’+',bc+d
    ,’-',bc-d
    ,’*',bc*d
    ,’/',DECODE(d,0,NULL,bc/d)
    ) AS bc_d
    ,DECODE( o2,’+',b+cd
    ,’-',b-cd
    ,’*',b*cd
    ,’/',DECODE(cd,0,NULL,b/cd)
    ) AS b_cd
    FROM vw_tmp
    )
    ,vw_tmp3 AS (
    SELECT ‘((‘||a||o1||b||’)'||o2||c||’)'||o3||d as formula
    ,DECODE( o3,’+',ab_c+d
    ,’-',ab_c-d
    ,’*',ab_c*d
    ,’/',DECODE(d,0,NULL,ab_c/d)
    ) AS result
    FROM vw_tmp2
    UNION ALL
    SELECT ‘(‘||a||o1||’(‘||b||o2||c||’))’||o3||d as formula
    ,DECODE( o3,’+',a_bc+d
    ,’-',a_bc-d
    ,’*',a_bc*d
    ,’/',DECODE(d,0,NULL,a_bc/d)
    ) AS result
    FROM vw_tmp2
    UNION ALL
    SELECT ‘(‘||a||o1||b||’)'||o2||’(‘||c||o3||d||’)’ as formula
    ,DECODE( o2,’+',ab+cd
    ,’-',ab-cd
    ,’*',ab*cd
    ,’/',DECODE(cd,0,NULL,ab/cd)
    ) AS result
    FROM vw_tmp2
    UNION ALL
    SELECT a||o1||’((‘||b||o2||c||’)'||o3||d||’)’ as formula
    ,DECODE( o1,’+',a+bc_d
    ,’-',a-bc_d
    ,’*',a*bc_d
    ,’/',DECODE(bc_d,0,NULL,a/bc_d)
    ) AS result
    FROM vw_tmp2
    UNION ALL
    SELECT a||o1||’(‘||b||o2||’(‘||c||o3||d||’))’ as formula
    ,DECODE( o1,’+',a+b_cd
    ,’-',a-b_cd
    ,’*',a*b_cd
    ,’/',DECODE(b_cd,0,NULL,a/b_cd)
    ) AS result
    FROM vw_tmp2
    )
    SELECT formula,result FROM vw_tmp3
    WHERE result=24;

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>