Question Define temp-table like another table without an index/field

jf365

New Member
I have a custom report that uses a temp table defined like a database table that is receiving this error:
** temp_ld_det already exists with 0. (132).

It seems that since our last upgrade, QAD has added an index to that table which is causing the issue. Is there a way for me to exclude this index from being inherited by the temp table? I've seen that I could have the temp table use only one index but it seems to be preferred that we leave all others in place and just exclude the one. I thought I had seen something about a field exception list? Maybe I'm wrong or I just didn't have the syntax correct. I'm pretty new to QAD and Progress so any guidance would be appreciated.
 

Cringer

ProgressTalk.com Moderator
Staff member
No you can't exclude indexes or fields. A lot of people would say you shouldn't be defining a temp-table LIKE anything for this very reason. But I can see why it would be attractive to.
As far as I know, if your temp-table is defined LIKE a database table it inherits all the indexes, unless you specify your own index in which case it inherits none. So to do what you want, identify the indexes you actually need, and define those implicitly on the definition. It's a pain to do it, I know, but it's the only way.
 

TheMadDBA

Active Member
I am confused on how the index is unique in the database but you are getting duplicates in your temp-table. Might want to take a look at your code again.

You can use the USE-INDEX clause to only copy over certain indexes from a table.

DEFINE TEMP-TABLE mycust LIKE Customer
USE-INDEX CustNum USE-INDEX country-post AS PRIMARY.
 

TheMadDBA

Active Member
No problem at all :)

If you are a true glutton for punishment you can take a look at the various add methods for a dynamic temp-table and selectively copy over fields too.
 

jf365

New Member
I am confused on how the index is unique in the database but you are getting duplicates in your temp-table. Might want to take a look at your code again.

You can use the USE-INDEX clause to only copy over certain indexes from a table.

DEFINE TEMP-TABLE mycust LIKE Customer
USE-INDEX CustNum USE-INDEX country-post AS PRIMARY.

Ahh, thank you! USE-INDEX did fix it, I was considering just going with the primary index in the data dictionary but this way will make it most like how it was in the old version. Thank you both for the quick replies. As for the unique index issue I am confused as well, the QAD table I'm defining from is ld_det and the index giving me the problem is oid_ld_det which is unique. I am using two separate loops (with a create in each) to create records for the temp-table under different conditions... maybe that could be why? I thought progress populated that field for you, correct? If not, then that would most likely be the cause.
 

TheMadDBA

Active Member
If you are doing a buffer copy without a list of fields then the fields should get populated from the database table.

It is possible you are creating the same record(s) twice from each loop. Or you could be creating empty temp-table records in certain situations. It has to be a code issue of some kind since the DB will enforce uniqueness on non null (? mark for the value) fields.
 

Cringer

ProgressTalk.com Moderator
Staff member
I thought progress populated that field for you, correct? If not, then that would most likely be the cause.
I see what the problem is now. You're creating tt records and not populating the oid_ld_det field because for a real table you don't do that. The real table will have a CREATE trigger associated with it that will populate this field with the next value of a sequence. Temp Tables do not have triggers, so you will need to populate this field with your own sequence value to ensure uniqueness (obviously making sure you don't write this value back to the DB later!) .
Hope that makes sense.
 
Top