1 2 3 Previous Next 61 Replies Latest reply on Aug 19, 2013 9:39 AM by Manik

    Trying to solve 6 Number 1 target android game using oracle (sql or pl/sql)

    Manik

      Gurus/mentors/Friends,

       

      Today I had some space in my work time, so tried to replicate an android game called "6 numbers 1 target" in oracle.

       

      All I need help from you all is to make it more efficient/robust and garnish it with a bit of awesomeness  (preferable in SQL ??? which I did not get )

       

      Please note that if you are busy, ignore this question.

       

      ------------------------------------------------------------------------------------------------

      Game goes like this:

      -------------------------

       

      computer gives you only 6 numbers ...

       

      e.g. :  50,9,5,8,6,7

       

      And using mathematical operators like '+','-','*','/'  we need to get a result like 292.  (this number 292 is also given by computer..)

       

      All we need to find is the apt formula for getting 292 out of those 6 numbers (you may/may not use all of those numbers) by using those operators (you may use them without any restiction of number of times they are used and also decimals are truncated.).

       

      So the solutions can be : 50*6-8+5/9/7     or   50*6-8  or  50*6-8+5/7/9 etc....   (multiple answers are possible, all boiling down giving 292)

       

      I just tried to write it in this way, but its taking a lot of time as the levels go up. So I thought I would dice it in this forum for better solution.( or may be suggestions to improve my solution below)

      ------------------------------------

       


      create table demo (str varchar2(100), val number);

       

       

      -----------------------------------


      DECLARE
         l_str      VARCHAR2 (100);
         l_result   NUMBER;
      BEGIN
         EXECUTE IMMEDIATE 'truncate table demo';

         WHILE (TRUE) LOOP             ----------------------------------- I know this is evil, but this is game afterall and I needed iterations to go on until I derive at a solution.
            BEGIN
               FOR rec
                  IN (WITH operators AS
                              (  SELECT COLUMN_VALUE op
                                   FROM TABLE (sys.odcivarchar2list ('+',
                                                                     '-',
                                                                     '*',
                                                                     '/'))
                               ORDER BY DBMS_RANDOM.VALUE ()),
                           t AS
                              (    SELECT SUBSTR (SYS_CONNECT_BY_PATH (letter, ','), 2)
                                             word
                                     FROM (    SELECT LEVEL LVL,
                                                      REGEXP_SUBSTR (str,
                                                                     '[^,]+',
                                                                     1,
                                                                     LEVEL)
                                                         LETTER
                                                 FROM (SELECT '7,8,5,50,9,6' str FROM DUAL) t
                                           CONNECT BY LEVEL <=
                                                         REGEXP_COUNT (STR, ',') + 1)
                                    WHERE LEVEL = 6
                               CONNECT BY NOCYCLE lvl != PRIOR lvl),
                           tt AS
                              (SELECT REGEXP_SUBSTR (word,
                                                     '[^,]*',
                                                     1,
                                                     1)
                                         col1,
                                      (SELECT val
                                         FROM (  SELECT op val
                                                   FROM operators
                                               ORDER BY DBMS_RANDOM.VALUE ())
                                        WHERE ROWNUM = 1)
                                         op1,
                                      REGEXP_SUBSTR (word,
                                                     '[^,]*',
                                                     1,
                                                     3)
                                         col2,
                                      (SELECT val
                                         FROM (  SELECT op val
                                                   FROM operators
                                               ORDER BY DBMS_RANDOM.VALUE ())
                                        WHERE ROWNUM = 1)
                                         op2,
                                      REGEXP_SUBSTR (word,
                                                     '[^,]*',
                                                     1,
                                                     5)
                                         col3,
                                      (SELECT val
                                         FROM (  SELECT op val
                                                   FROM operators
                                               ORDER BY DBMS_RANDOM.VALUE ())
                                        WHERE ROWNUM = 1)
                                         op3,
                                      REGEXP_SUBSTR (word,
                                                     '[^,]*',
                                                     1,
                                                     7)
                                         col4,
                                      (SELECT val
                                         FROM (  SELECT op val
                                                   FROM operators
                                               ORDER BY DBMS_RANDOM.VALUE ())
                                        WHERE ROWNUM = 1)
                                         op4,
                                      REGEXP_SUBSTR (word,
                                                     '[^,]*',
                                                     1,
                                                     9)
                                         col5,
                                      (SELECT val
                                         FROM (  SELECT op val
                                                   FROM operators
                                               ORDER BY DBMS_RANDOM.VALUE ())
                                        WHERE ROWNUM = 1)
                                         op5,
                                      REGEXP_SUBSTR (word,
                                                     '[^,]*',
                                                     1,
                                                     11)
                                         col6
                                 FROM t)
                        SELECT    col1
                               || op1
                               || col2
                               || op2
                               || col3
                               || op3
                               || col4
                               || op4
                               || col5
                               || op5
                               || col6
                                  formula
                          FROM tt
                      ORDER BY DBMS_RANDOM.VALUE ()) LOOP
                  EXECUTE IMMEDIATE 'begin :result := ' || rec.formula || '; end;'
                     USING OUT l_result;

                  l_str := rec.formula;

                  INSERT INTO demo
                       VALUES (rec.formula, TRUNC (l_result));
               -------COMMIT;


               END LOOP;
            END;

            IF (l_result = 292) THEN
               EXIT;
            END IF;
         END LOOP;
      END;
      /

      --------------------------------------------------------------------------------------------------

       

      Thanks in advance!!!!  (even for those who opened this question )

       

      Cheers,

      Manik.

        1 2 3 Previous Next