Flatten a dataset?

[dayv2005]

I have a question. Been out of the progress world for quite some time now. Using 10.1c.

I have a simple 3 table dataset. I am wondering if it is possible to convert this dataset into a one level data set merging all fields?

DEFINE TEMP-TABLE ttEquipmentSpec LIKE EquipmentSpec.
DEFINE TEMP-TABLE ttEquipmentJoinOptions LIKE EquipmentJoinOptions.
DEFINE TEMP-TABLE ttEquipmentOption LIKE EquipmentOption.

DEFINE DATASET dsEquipment
FOR ttEquipmentSpec, ttEquipmentJoinOptions, ttEquipmentOption
    DATA-RELATION dr1 FOR ttEquipmentSpec, ttEquipmentJoinOptions NESTED
        RELATION-FIELD(EquipmentSpecID,EquipmentSpecID)
    DATA-RELATION dr2 FOR ttEquipmentJoinOptions, ttEquipmentOption NESTED
        RELATION-FIELD(EquipmentOptionID,EquipmentOptionID).

Something like the above.

 

[medu]

something like that will do for a 'straight' hierarchy, meaning it goes from one top level buffer to the end of the hierarchy but only taking into account the 'branch' of the first child... if one table in the hierarchy has multiple child relations then the result does not make too much sense imho but eventually you can get that as well.

p.s. it's just a sample so it has to be bad, in real world don't use 'like'

define variable resultSet   as handle no-undo.

define temp-table ttOrder       like Order.
define temp-table ttOrderLine  like OrderLine.

define dataset dsOrder for ttOrder, ttOrderLine
   data-relation for ttOrder, ttOrderLine relation-fields(ordernum,ordernum).

for each Order:
   create ttOrder.
   buffer-copy order to ttOrder.
   
   for each OrderLine of order:
      create ttOrderline.
      buffer-copy OrderLine to ttOrderLine.
   end.
end.
   
run getResultSet (dataset dsOrder by-reference, 1, output table-handle resultSet).
resultSet:write-xml('file', 'orders.xml', true).
delete object resultSet.

procedure getResultSet:
   define input  parameter dataset-handle data.
   define input  parameter topBuffer as integer no-undo.
   define output parameter table-handle resultSet.

   define variable cnt        as integer   no-undo.
   define variable qry        as handle    no-undo.
   define variable buf        as handle    no-undo.
   define variable sel        as character no-undo initial 'for'.
   
   buf = data:get-top-buffer(topBuffer) no-error.
   if valid-handle(buf) then do:
      create temp-table resultSet.
      create query qry.
      
      qry:forward-only = true.
      
      /* add fields from all tables in hierarchy */
      do while valid-handle(buf):
         resultSet:add-fields-from(buf).
         qry:add-buffer(buf).
         sel = substitute('&1 each &2 &3,', sel, buf:name, 
               if valid-handle(buf:parent-relation) then buf:parent-relation:where-string else ''). 
         if buf:num-child-relations eq 0 then leave.
         buf = buf:get-child-relation(1):child-buffer no-error.
      end.
      resultSet:temp-table-prepare(data:name).
      qry:query-prepare(trim(sel, ', ':u)).
      qry:query-open().
      buf = resultSet:default-buffer-handle.
      do while qry:get-next(no-lock):
         buf:buffer-create().
         do cnt = 1 to qry:num-buffers:
            buf:buffer-copy(qry:get-buffer-handle(cnt)).
         end.
      end.
      qry:query-close().
      delete object qry.
      
   end.
   delete object resultSet.
end procedure.

 

[tamhas]

One might want to ask why you want to flatten it? One of the really annoying parts of SQL queries is that they return flat data sets with lots of repeating values. Here you have a perfectly good set of relationships ... why ruin it?

 

[dayv2005]

Thank you very much I'll try that.

The reason I need a flat dataset view is i just for some data analysis visually of an existing data set.

 

[tamhas]

Depending on what you are using to display the data, it can be perfectly happy with a PDS. Having a parent child relationship, for example, pretty much implies a need for grouping in the display.

 

[medu]

Hmm, data analysis usually relates to data marts that historically were highly de-normalized data structures... this is not always the case anymore, more and more analytical tools are very happy to work with normalized data structures (even snowflake), meaning Thomas is right and you might be able to keep the fact and dimensions tables separated. However in your example it looks like you are trying to actually build the 'fact' table – equipment options; which looks more like a M:N relationship. Some olap tools can work with that, some doesn't... if you don't want to completely flatten the data set keep the 'options' as a separate dimension and add option id in the 'equipment option fact table' to skip the intermediate relation table.

Anyway, what tool do you use and how do you pass that data out of abl?