Resolved Looping to populate a temp-table

[Tracy Hall]

Afternoon everyone,

I need a tiny bit of help. I have brought in two csv files and put them each in their own temp table. Now I need to put them in one together. The thing is that not all of the data is going into the final product.

I am sure that there is an easier way to do this, but as many of you know, when you are desperately looking for something, it seems to hide from you.

Anyway, this is roughly what I have and it just sits there thinking about it..... forever.
Anyone see the problem

FOR EACH ttProd NO-LOCK:
       CREATE ttNAAendResult.
      
       ASSIGN 
                  * BUNCH OF FIELDS *  (to end result table)
      .
       FOR EACH ttAttr NO-LOCK:
              IF ttProd.field1 = ttAttr.field1 THEN
                DO:
                   ASSIGN
                          * BUNCH OF FIELDS *    (to end result table)
                     .
                 END.
               ELSE
               DO:
       

                END.
          END.
END.
[code]

 

[Stefan]

Looks ok.

How many records in both temp-tables - you will be looping records in one times records in the other.

Possibly use the debugger to step through your code, is it doing what you expect it to?

Alternatively strip the code down to something complete (including sample record creation) and post it here - if it then also just sits there.

NO-LOCK on a temp-table has no relevance and is ignored - lock it from who?

 

[Tracy Hall]

I will upload the code. There is about 150 - 200. I have not iterated through and counted them, but I can. There may be more. I will try the debugger. I am not a pro with that, so no time better than the present to learn.

Good to know I had the right idea at least. I have not finished the output at the end, it is commented out because I just laid it out with the field names, I have to reference the table.

Let me know if you see anything.

I appreciate it. The longer I look at it and get frustrated, the cloudier the brain gets.

 

[Stefan]

Not in the vicinity of a PC for a few days:

1. Add NO-UNDO to your temp-tables, I doubt you want rollback handling on them
2. Your import files match your temp-table definition so you can just IMPORT tt. Without specifying each field
3. None of your temp-tables have an index, to trap silliness it can be a good idea to have one primary unique index - the AVM will the error if you have created the same record twice

With sample data and code I meant the temp-tables stripped back to one or two fields and some create tt, assign statements - you should only need about four records to illustrate your issue.

 

[Marian EDU]

first you should close the input streams, the input close statement does not close all named input streams (in case you were under this impression).

as Stefan said you might want to try to define the temp-tables so that the field order in definition match the one in import files, I'll use the same field name in the final 'aggregate' temp-table and just use buffer-copy from the two source temp-tables.

looks like you expect to be only (at most) one 'attribute' record for each 'product', if that is correct you just add the filter to the inner for each look instead of using the IF inside of it... if not (more attributes exists for one product) then you have to rethink how do you want to store that in the final temp-table, keep the first attribute, keep the last value (override as you do now) or de-normalize the two tables in the final result one (have all products even without attributes, for those were multiple attributes exists one record for each attribute will be created and product data will be duplicated on all those records)

 

[RealHeavyDude]

Additionally: You should always use defined buffers when updating Temp-Tables because the buffer scope of the default buffer that comes with a Temp-Table behaves slightly different than the one that comes with database table. The scope of the default buffer of the Temp-Table is always the procedure block ( hence, you can't define a Temp-Table in an internal procedure or user defined function ). Using defined buffers you make sure that the last record you've created in the Temp-Table is flushed ( which automatically happens at the end of the buffer scope or - implicitely - when you create the next record in it ... ) avoiding all sorts of issues.

Heavy Regards, RealHeavyDude.

 

[Tracy Hall]

Thanks everyone! I got through that part and now I have another question, I will ask that in the appropriate place though.