Matrix 2

[getjbb]

I am trying to create a matrix which should look like the following:

one item dumplings salad chicken-fried chicken-grilled desert-cake desert-pie

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

dumplings 1 1 0 0 2 0 1

salad 1 0 0 0 0 0 0

chicken-fried 2 0 0 0 0 4 0

chicken-grilled 3 0 2 0 0 0 0

desert-cake 7 0 0 4 0 0 0

desert-pie 0 1 0 0 0 0 0

The numbers are the count of the pairing of the different

menu items ( (dumplings,salad), (dumplings,chicken)..(chicken,desert), etc.)

per order (per_order) or sometime only one item can be selected from the menu.


The menu types dumplings, salad, chicken, desert could have sub categories,

which are be in the table subcategorys.

For example chicken could have subcategorys: Fried, Grilled and desert

could have subcategory: Cake and Pie.

The date is gathered per_order which is in the menus table

The tables involved are:

menus

-----

menu_id varchar

per_order varchar

menuitem_type number

subcategory

-------

subcategory_id varchar

menu_id varchar

subcategory_type number

The table that differentiates between menu and

subcategory information is a common table:


common

------

item_type_number number (this number is sequential per item type:
menu(1,2,3,etc); subcategory(1,2,3) and
referenced in menuitem_type and
subcategory_type in menus and subcategory

tables)

menuitem_subcategory_type varchar2

show_value varchar2



Data for the common table would look like:


item_type_number menuitem_subcategory_type show_value

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

1 menu dumplings
2 menu salad
3 menu chicken
4 menu desert
1 subcategory fried
2 subcategory grilled
3 subcategory cake
4 subcategory pie


I know this seems like a lot of information, but if anyone could help me in this matter I would greatly appreciate it.

getjbb

 

[Dagon]

And your question is?

For Oracle-related work, contact me through Linked-In.

 

[getjbb]

I am not sure how to create a matrix according to the requirements I submitted.

Again, if anyone can assist me in this matter, I would appreciate it.

Getjbb

 

[Dagon]

I'm struggling to make much sense of the table design. Can you tell us what data you would set up in these tables to represent a customer having grilled chicken and desert pie and another customer having only salad?

For Oracle-related work, contact me through Linked-In.

 

[getjbb]

The examples are as follows:

Menu Table:



example data:

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

menu_id: 1111

per_order: 2222

menuitem_type: 2 (this is a salad)



menu_id: 3434

per_order: 3333

menuitem_type: 3 (this is a chicken)



menu_id: 3435

per_order: 3333

menuitem_type: 4 (this is a desert)





Subcategory Table:

-------

subcategory_id varchar

menu_id varchar

subcategory_type number



example data:

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

subcategory_id 7777

menu_id 3435 (From menu_id 3435 in the menus table example data)

subcategory_type 3 (this person ordered cake as desert)



Thanks
getjbb

 

[getjbb]

Hi dagon,

I am not being pushy, but is there a possible solution to my matrix problem?

If you need more details, please let me know.

Getjbb

 

[Thargy]

getjbb,

can you post the create table statements and insert statements to let us build some sample data, and then see what it is you need.

I have followed this thread, and like Dagon, really can't be too sure what you're driving at.

Please let me know if you would like any advice W.R.T. the create table statements etc.

Regards

T

 

[Dagon]

After the last post, I think I do follow the table design. It was a bit unclear at the start which were reference tables and which were data tables.

It is obviously possibly to do what you want but I think it would be a struggle to do it using just SQL. For a start, you don't know how many columns there will be in the output because it depends on the number of paired items. I'd suggest a PL/SQL routine where you dynamically build a table.

I looked at it yesterday and got as far as a set of build statements for the tables and a query that returned the paired values. I was planning then to build a PL/SQL shell around it to dynamically generate the temporary table, but can't do much at the moment because all the databases at the company where I work are down.

If it's urgent, you can have a look at what I've done so far. It may be of use.

create table menus (menu_id varchar2(20), per_order varchar2(20), menuitem_type number);

create table subcategory (subcategory_id varchar2(20), menu_id varchar2(20), subcategory_type number);

create table common (item_type_number number, menuitem_subcategory_type varchar2(20), show_value VARCHAR2(20));

insert into common values(1, 'menu', 'dumplings');
insert into common values(2, 'menu', 'salad');
insert into common values(3, 'menu', 'chicken');
insert into common values(4, 'menu', 'desert');
insert into common values(1, 'subcategory', 'fried');
insert into common values(2, 'subcategory', 'grilled');
insert into common values(3, 'subcategory', 'cake');
insert into common values(4, 'subcategory', 'pie');

insert into menus values (1111, 2222, 2);
insert into menus values (3434, 3333, 3);
insert into menus values (3435, 3333, 4);
insert into menus values (3436, 3333, 1);
insert into menus values (3437, 3337, 3);
insert into menus values (3438, 3337, 4);
insert into menus values (3439, 3337, 1);
insert into menus values (3440, 3338, 3);
insert into menus values (3441, 3338, 4);

insert into subcategory values (8881, 3434, 2);
insert into subcategory values (8882, 3435, 4);
insert into subcategory values (8883, 3437, 1);
insert into subcategory values (8884, 3438, 3);
insert into subcategory values (8881, 3440, 2);
insert into subcategory values (8882, 3441, 4);

select ordered_item1, ordered_item2, count(*)
from
(with meal as
(
select m.menu_id,
         m.per_order, 
         count(1) over (partition by m.per_order) as num_items,
            rank() over (partition by m.per_order order by case when c2.show_value is not null then c2.show_value|| ' ' end||C1.SHOW_VALUE) as rn,
         case when c2.show_value is not null then c2.show_value|| ' ' end||C1.SHOW_VALUE as ordered_item
from (select * from common where menuitem_subcategory_type = 'menu') c1, 
        (select * from common where menuitem_subcategory_type = 'subcategory') c2,
        menus m, 
        subcategory s
where M.MENU_ID = s.menu_id(+)
and M.MENUITEM_TYPE = C1.item_type_number(+)
and S.SUBCATEGORY_TYPE = C2.ITEM_TYPE_NUMBER (+)
)
select m1.per_order,
         m1.ordered_item as ordered_item1,
         'one item' as ordered_item2
from meal m1
where num_items = 1
union all
select m1.per_order, 
         m1.ordered_item as ordered_item1, 
         m2.ordered_item as ordered_item2
from (select * from meal where num_items > 1) m1, (select * from meal where num_items > 1) m2
where m1.per_order = m2.per_order
and m1.ordered_item <> m2.ordered_item
and m1.rn > m2.rn)
group by ordered_item1, ordered_item2

For Oracle-related work, contact me through Linked-In.

 

[Dagon]

After installing Oracle XE, I was able to plod on and here is the completed solution.

create table menus (menu_id varchar2(20), per_order varchar2(20), menuitem_type number);

create table subcategory (subcategory_id varchar2(20), menu_id varchar2(20), subcategory_type number);

create table common (item_type_number number, menuitem_subcategory_type varchar2(20), show_value VARCHAR2(20), display_order integer);

insert into common values(1, 'menu', 'dumplings', 0);
insert into common values(2, 'menu', 'salad', 0);
insert into common values(3, 'menu', 'chicken', 0);
insert into common values(4, 'menu', 'dessert', 1);
insert into common values(1, 'subcategory', 'fried', 0);
insert into common values(2, 'subcategory', 'grilled', 0);
insert into common values(3, 'subcategory', 'cake', 0);
insert into common values(4, 'subcategory', 'pie', 0);

insert into menus values (1111, 2222, 2);
insert into menus values (3434, 3333, 3);
insert into menus values (3435, 3333, 4);
insert into menus values (3436, 3333, 1);
insert into menus values (3437, 3337, 3);
insert into menus values (3438, 3337, 4);
insert into menus values (3439, 3337, 1);
insert into menus values (3440, 3338, 3);
insert into menus values (3441, 3338, 4);
insert into menus values (3442, 3339, 2);
insert into menus values (3443, 3339, 1);
insert into menus values (3444, 3340, 1);
insert into menus values (3445, 3340, 3);
insert into menus values (3446, 3340, 4);

insert into subcategory values (8881, 3434, 2);
insert into subcategory values (8882, 3435, 4);
insert into subcategory values (8883, 3437, 1);
insert into subcategory values (8884, 3438, 3);
insert into subcategory values (8885, 3440, 2);
insert into subcategory values (8885, 3441, 3);
insert into subcategory values (8886, 3445, 2);
insert into subcategory values (8887, 3446, 3);

drop table combinations;

create table combinations as
select ordered_item1, ordered_item2, count(*) pair_count
from
(with meal as
(
select m.menu_id,
         m.per_order, 
         count(1) over (partition by m.per_order) as num_items,
         rank() over (partition by m.per_order order by  case when c2.show_value is null then c1.show_value
                                                                                when c1.display_order = 0 then c2.show_value|| '-'||C1.SHOW_VALUE 
                                                                                ELSE c1.show_value|| '-' ||C2.SHOW_VALUE END) as rn,
         case when c2.show_value is null then c1.show_value
                 when c1.display_order = 0 then c2.show_value|| '-'||C1.SHOW_VALUE 
                 ELSE c1.show_value|| '-' ||C2.SHOW_VALUE END as ordered_item
from (select * from common where menuitem_subcategory_type = 'menu') c1, 
        (select * from common where menuitem_subcategory_type = 'subcategory') c2,
        menus m, 
        subcategory s
where M.MENU_ID = s.menu_id(+)
and M.MENUITEM_TYPE = C1.item_type_number(+)
and S.SUBCATEGORY_TYPE = C2.ITEM_TYPE_NUMBER (+)
)
select m1.per_order,
         m1.ordered_item as ordered_item1,
         'one item' as ordered_item2
from meal m1
where num_items = 1
union all
select m1.per_order, 
         m1.ordered_item as ordered_item1, 
         m2.ordered_item as ordered_item2
from (select * from meal where num_items > 1) m1, (select * from meal where num_items > 1) m2
where m1.per_order = m2.per_order
and m1.ordered_item <> m2.ordered_item
and m1.rn > m2.rn)
group by ordered_item1, ordered_item2;

declare
   type t_col_rec is record
      (menu_item_name varchar2(30),
       menu_item_colname varchar2(30));
   type t_col_list is table of t_col_rec index by pls_integer;
   l_col_list t_col_list;
   l_sql  varchar2(4000);
   l_pair_count integer;
   --
   cursor c_pair_count (p_item1 in varchar2,
                                 p_item2 in varchar2) is
      select pair_count
        from combinations
        where (ordered_item1 = p_item1 and ordered_item2 = p_item2) or
                  (ordered_item1 = p_item2 and ordered_item2 = p_item1);
   --
begin
  for r_col in (select menu_item_name,
                             menu_item_colname
                       from (select ordered_item1 as menu_item_name,
                                          trim(translate(ordered_item1, ' - ', '__')) as menu_item_colname 
                                  from combinations 
                                union 
                                select ordered_item2,
                                         trim(translate(ordered_item2, ' -', '__')) 
                                 from combinations)
                                order by decode(menu_item_name, 'one item', 'aaaa', menu_item_name)) loop
    l_col_list(l_col_list.COUNT+1).menu_item_name := r_col.menu_item_name;
    l_col_list(l_col_list.COUNT).menu_item_colname := r_col.menu_item_colname;
  end loop;
  --
  begin
     execute immediate 'drop table paired_values';
  exception when others then
    null;
  end;
  l_sql := 'create table paired_values (menu_item varchar2(30) ';
  for i in l_col_list.first..l_col_list.last loop
    l_sql := l_sql || ', '||l_col_list(i).menu_item_colname||'_c integer';
  end loop;
  --
  l_sql := l_sql || ')'; 
 --
  dbms_output.put_line(l_sql);
  execute immediate l_sql;
  --
  for i in l_col_list.first..l_col_list.last loop
    l_sql := null;
    l_sql := l_sql ||'insert into paired_values(menu_item';
    for j in l_col_list.first..l_col_list.last loop
      l_sql := l_sql ||','||l_col_list(j).menu_item_colname||'_c';
    end loop;
    l_sql := l_sql || ') values ( '''||l_col_list(i).menu_item_name||'''';
    --
   for j in l_col_list.first..l_col_list.last loop
      open c_pair_count(l_col_list(i).menu_item_name, l_col_list(j).menu_item_name);
      fetch c_pair_count into l_pair_count;
      if c_pair_count%notfound then
        l_pair_count := 0;
      end if;
      close c_pair_count;
      l_sql := l_sql || ',' || l_pair_count;
    end loop;
    l_sql := l_sql || ')';
    dbms_output.put_line(l_sql);
    execute immediate l_sql;
  end loop;
end; 
/

select * from paired_values
where menu_item <> 'one item'

MENU_ITEM                      ONE_ITEM_C DESSERT_CAKE_C DESSERT_PIE_C DUMPLINGS_C FRIED_CHICKEN_C GRILLED_CHICKEN_C    SALAD_C
------------------------------ ---------- -------------- ------------- ----------- --------------- ----------------- ----------
dessert-cake                            0              0             0           2               1                 2          0
dessert-pie                             0              0             0           1               0                 1          0
dumplings                               0              2             1           0               1                 2          1
fried-chicken                           0              1             0           1               0                 0          0
grilled-chicken                         0              2             1           2               0                 0          0
salad                                   1              0             0           1               0                 0          0

For Oracle-related work, contact me through Linked-In.

 

[getjbb]

Dagon,

Thank you, so much, for the solution.

getjbb

 

[getjbb]

Dagon,

The common table I will be working with will not have a display_order column.

How critical is that to the process?

Is there a work around without using it?

Thanks

getjbb

 

[getjbb]

Also, I want the menu item value to come before the subcaterogy value, if possible. For example, chicken-grilled instead of grilled-chicken.

Thanks.

getjbb

 

[Dagon]

I put that in as a way of getting the "grilled chicken" and "dessert cake" the right way round. If you can't alter the common table, then you could create another table that has that information in it and join to that. Or you could hard-code the rules in the case expression, although that wouldn't be as elegant.

For Oracle-related work, contact me through Linked-In.

 

[getjbb]

Thanks.

getjbb