[set]sqlmacro_26ai.sql

@connect_as_demo
set linesize 120
set pagesize 100

-- tab = gameofset_predicate macro
prompt gameofset_predicate

create or replace function gameofset_predicate
( col_in in dbms_tf.columns_t )
return varchar2 sql_macro( scalar ) is
  lsql varchar2(32767);
begin
  lsql := '
(    (     c1.' || col_in( 1 ) || '  = c2.' || col_in( 1 ) || '
       and c2.' || col_in( 1 ) || '  = c3.' || col_in( 1 ) || '
      )
   or (     c1.' || col_in( 1 ) || ' <> c2.' || col_in( 1 ) || '
        and c2.' || col_in( 1 ) || ' <> c3.' || col_in( 1 ) || '
        and c3.' || col_in( 1 ) || ' <> c1.' || col_in( 1 ) || '
      )
)
';
  dbms_output.put_line( lsql );
  return lsql;
end;
/

pause
-- tab = puzzle+solution [failes]
prompt puzzle+solution [failes]

with symbols as
  (          select 'oval'     symbol   from dual
   union all select 'squiggle'          from dual
   union all select 'diamond'           from dual
  )
,    colors   as
 (           select 'green'    color    from dual
   union all select 'purple'            from dual
   union all select 'red'               from dual
  )
,    shadings as
 (           select 'filled'   shading  from dual
   union all select 'striped'           from dual
   union all select 'open'              from dual
  )
,    numbers  as
 (           select 1          quantity from dual
   union all select 2                   from dual
   union all select 3                   from dual
  )
,    deck     as
  (select symbol, color, shading, quantity
   from       symbols
   cross join colors
   cross join shadings
   cross join numbers
  )
,    puzzle   as
  (select rownum as cardno, symbol, color
        , shading         , quantity
   from   (select *
           from   deck
           order  by dbms_random.value
          )
   where  rownum <= 12
  )
,    solution as
  (select c1.symbol   as symbol1
        , c1.color    as color1
        , c1.shading  as shading1
        , c1.quantity as quantity1
        , c2.symbol   as symbol2
        , c2.color    as color2
        , c2.shading  as shading2
        , c2.quantity as quantity2
        , c3.symbol   as symbol3
        , c3.color    as color3
        , c3.shading  as shading3
        , c3.quantity as quantity3
   from   puzzle c1
   join   puzzle c2 
     on   c1.cardno < c2.cardno
   join   puzzle c3 
     on   c2.cardno < c3.cardno
   where  1=1
   and    gameofset_predicate(columns(symbol)) = 1
   and    gameofset_predicate(columns(color)) = 1
   and    gameofset_predicate(columns(shading)) = 1
   and    gameofset_predicate(columns(quantity)) = 1
  )
select 'puzzle'   as what
     ,               symbol,          color
     ,               shading,         quantity
     , null       as symbol,  null as color
     , null       as shading, null as quantity
     , null       as symbol,  null as color
     , null       as shading, null as quantity
from   puzzle
union  all
select 'solution' as what
     ,               symbol1,         color1
     ,               shading1,        quantity1
     ,               symbol2,         color2
     ,               shading2,        quantity2
     ,               symbol3,         color3
     ,               shading3,        quantity3
from   solution
/

pause
-- tab = puzzle+solution
prompt puzzle+solution
with symbols as
  (          select 'oval'     symbol   from dual
   union all select 'squiggle'          from dual
   union all select 'diamond'           from dual
  )
,    colors   as
 (           select 'green'    color    from dual
   union all select 'purple'            from dual
   union all select 'red'               from dual
  )
,    shadings as
 (           select 'filled'   shading  from dual
   union all select 'striped'           from dual
   union all select 'open'              from dual
  )
,    numbers  as
 (           select 1          quantity from dual
   union all select 2                   from dual
   union all select 3                   from dual
  )
,    deck     as
  (select symbol, color, shading, quantity
   from       symbols
   cross join colors
   cross join shadings
   cross join numbers
  )
,    puzzle   as
  (select rownum as cardno, symbol, color
        , shading         , quantity
   from   (select *
           from   deck
           order  by dbms_random.value
          )
   where  rownum <= 12
  )
select 'puzzle'   as what
     ,               symbol,          color
     ,               shading,         quantity
     , null       as symbol,  null as color
     , null       as shading, null as quantity
     , null       as symbol,  null as color
     , null       as shading, null as quantity
from   puzzle
union  all
  select 'solution' as what
        , c1.symbol   as symbol1
        , c1.color    as color1
        , c1.shading  as shading1
        , c1.quantity as quantity1
        , c2.symbol   as symbol2
        , c2.color    as color2
        , c2.shading  as shading2
        , c2.quantity as quantity2
        , c3.symbol   as symbol3
        , c3.color    as color3
        , c3.shading  as shading3
        , c3.quantity as quantity3
   from   puzzle c1
   join   puzzle c2 
     on   c1.cardno < c2.cardno
   join   puzzle c3 
     on   c2.cardno < c3.cardno
   where  1=1
   and    gameofset_predicate(columns(symbol))   = 1
   and    gameofset_predicate(columns(color))    = 1
   and    gameofset_predicate(columns(shading))  = 1
   and    gameofset_predicate(columns(quantity)) = 1 
/

pause
-- tab = gameofset_predicate macro [26ai]
prompt gameofset_predicate macro [26ai]
create or replace function gameofset_predicate
( col_in in dbms_tf.columns_t )
return varchar2 sql_macro( scalar ) is
  lsql varchar2(32767);
begin
  lsql := '
( case
    when (    (     c1.' || col_in( 1 ) || '  = c2.' || col_in( 1 ) || '
                and c2.' || col_in( 1 ) || '  = c3.' || col_in( 1 ) || '
              )
           or (     c1.' || col_in( 1 ) || ' <> c2.' || col_in( 1 ) || '
                and c2.' || col_in( 1 ) || ' <> c3.' || col_in( 1 ) || '
                and c3.' || col_in( 1 ) || ' <> c1.' || col_in( 1 ) || '
              )
         ) then true
    else false
  end
)';
  dbms_output.put_line( lsql );
  return lsql;

end;
/


pause
-- tab = puzzle+solution [26ai]
prompt puzzle+solution [26ai]
with symbols as
  (          select 'oval'     symbol   from dual
   union all select 'squiggle'          from dual
   union all select 'diamond'           from dual
  )
,    colors   as
 (           select 'green'    color    from dual
   union all select 'purple'            from dual
   union all select 'red'               from dual
  )
,    shadings as
 (           select 'filled'   shading  from dual
   union all select 'striped'           from dual
   union all select 'open'              from dual
  )
,    numbers  as
 (           select 1          quantity from dual
   union all select 2                   from dual
   union all select 3                   from dual
  )
,    deck     as
  (select symbol, color, shading, quantity
   from       symbols
   cross join colors
   cross join shadings
   cross join numbers
  )
,    puzzle   as
  (select rownum as cardno, symbol, color
        , shading         , quantity
   from   (select *
           from   deck
           order  by dbms_random.value
          )
   where  rownum <= 12
  )
select 'puzzle'   as what
     ,               symbol,          color
     ,               shading,         quantity
     , null       as symbol,  null as color
     , null       as shading, null as quantity
     , null       as symbol,  null as color
     , null       as shading, null as quantity
from   puzzle
union  all
  select 'solution' as what
        , c1.symbol   as symbol1
        , c1.color    as color1
        , c1.shading  as shading1
        , c1.quantity as quantity1
        , c2.symbol   as symbol2
        , c2.color    as color2
        , c2.shading  as shading2
        , c2.quantity as quantity2
        , c3.symbol   as symbol3
        , c3.color    as color3
        , c3.shading  as shading3
        , c3.quantity as quantity3
   from   puzzle c1
   join   puzzle c2 
     on   c1.cardno < c2.cardno
   join   puzzle c3 
     on   c2.cardno < c3.cardno
   where  1=1
   and    gameofset_predicate(columns(symbol))
   and    gameofset_predicate(columns(color))
   and    gameofset_predicate(columns(shading))
   and    gameofset_predicate(columns(quantity))
/

pause
-- tab = gameofset_predicate macro [26ai]
prompt gameofset_predicate macro[26ai]
prompt No More Case
create or replace function gameofset_predicate
( col_in in dbms_tf.columns_t )
return varchar2 sql_macro( scalar ) is
  lsql varchar2(32767);
begin
  lsql := '
(   (     c1.' || col_in( 1 ) || '  = c2.' || col_in( 1 ) || '
      and c2.' || col_in( 1 ) || '  = c3.' || col_in( 1 ) || '
    )
 or (     c1.' || col_in( 1 ) || ' <> c2.' || col_in( 1 ) || '
      and c2.' || col_in( 1 ) || ' <> c3.' || col_in( 1 ) || '
      and c3.' || col_in( 1 ) || ' <> c1.' || col_in( 1 ) || '
    )
)';
  dbms_output.put_line( lsql );
  return lsql;

end;
/

pause
-- tab = puzzle+solution [23c]
prompt puzzle+solution [26ai]
with symbols as
  (          select 'oval'     symbol   from dual
   union all select 'squiggle'          from dual
   union all select 'diamond'           from dual
  )
,    colors   as
 (           select 'green'    color    from dual
   union all select 'purple'            from dual
   union all select 'red'               from dual
  )
,    shadings as
 (           select 'filled'   shading  from dual
   union all select 'striped'           from dual
   union all select 'open'              from dual
  )
,    numbers  as
 (           select 1          quantity from dual
   union all select 2                   from dual
   union all select 3                   from dual
  )
,    deck     as
  (select symbol, color, shading, quantity
   from       symbols
   cross join colors
   cross join shadings
   cross join numbers
  )
,    puzzle   as
  (select rownum as cardno, symbol, color
        , shading         , quantity
   from   (select *
           from   deck
           order  by dbms_random.value
          )
   where  rownum <= 12
  )
select 'puzzle'   as what
     ,               symbol,          color
     ,               shading,         quantity
     , null       as symbol,  null as color
     , null       as shading, null as quantity
     , null       as symbol,  null as color
     , null       as shading, null as quantity
from   puzzle
union  all
  select 'solution' as what
        , c1.symbol   as symbol1
        , c1.color    as color1
        , c1.shading  as shading1
        , c1.quantity as quantity1
        , c2.symbol   as symbol2
        , c2.color    as color2
        , c2.shading  as shading2
        , c2.quantity as quantity2
        , c3.symbol   as symbol3
        , c3.color    as color3
        , c3.shading  as shading3
        , c3.quantity as quantity3
   from   puzzle c1
   join   puzzle c2 
     on   c1.cardno < c2.cardno
   join   puzzle c3 
     on   c2.cardno < c3.cardno
   where  1=1
   and    gameofset_predicate(columns(symbol))
   and    gameofset_predicate(columns(color))
   and    gameofset_predicate(columns(shading))
   and    gameofset_predicate(columns(quantity))
/