temp-table and indexes

W

whwar9739

Member
Here is the situation.
For a simple query I would like to create a temp-table that is like the original and then just add an index to it. The reason for this is that it is not necessary to add the index to the actual table.

Here is my code example:
Code: 
DEF TEMP-TABLE tt-cust LIKE cust
   INDEX tt-idx fname[1] lname[1] prim-nm[1] addr[1] addr[2] addr[3] addr[4] city state.

FOR EACH cust NO-LOCK:
   CREATE tt-cust.
   BUFFER-COPY cust TO tt-cust.
END.

FOR EACH cust NO-LOCK:
   FIND FIRST tt-cust NO-LOCK
      WHERE tt-cust.fname[1] = cust.fname[1]
          AND tt-cust.lname[1] = cust.lname[1]
          AND tt-cust.prim-nm[1] = cust.prim-nm[1]
          AND tt-cust.addr[1] = cust.addr[1]
          AND tt-cust.addr[2] = cust.addr[2]
          AND tt-cust.addr[3] = cust.addr[3]
          AND tt-cust.addr[4] = cust.addr[4]
          AND tt-cust.city      = cust.city
          AND tt-cust.state    = cust.state
          AND tt-cust.key     <> cust.key
      NO-ERROR.
END.

When I try to run the above I get the error 'Unknown or Ambiguous Table'. Any help would be appreciated.

Thanks,
 
1., I abhor the use of LIKE.

2. What line are you getting the error?

3. What in the world are you doing. It sounded reasonable until I saw the code. Having just copied the *entire* customer table into tt-cust, you are then checking that it is there. Why?
 
LIKE would be one of those things I forgot to mention in my screed regarding keyword forget lists the other day ;) EXTENT would be another.

The error message should say which table it thinks is ambiguous and what line the problem occurs on. That would be valuable information to share. It would also be nice to know what specific version of Progress this is -- it might be a known bug, LIKE has had more than a few bugs associated with it over the years.

FWIW very similar code (without the array fields and other specifics of your schema but using the "customer" table) works just fine with the sports2000 database on Progress 10.2A.
 
The error is as follows:

Unknown or ambiguous table tt-cust. (725)
** Could not understand line 10. (196)

With the following code:
Code: 
DEF TEMP-TABLE tt-cust LIKE cust
   INDEX tt-idx fname[1] lname[1] prim-nm[1] addr[1] addr[2] addr[3] addr[4] city state.

FOR EACH cust NO-LOCK:
   CREATE tt-cust.
   BUFFER-COPY cust TO tt-cust.
END.

FOR EACH cust NO-LOCK:
   FIND FIRST tt-cust NO-LOCK
      WHERE tt-cust.fname[1] = cust.fname[1]
          AND tt-cust.lname[1] = cust.lname[1]
          AND tt-cust.prim-nm[1] = cust.prim-nm[1]
          AND tt-cust.addr[1] = cust.addr[1]
          AND tt-cust.addr[2] = cust.addr[2]
          AND tt-cust.addr[3] = cust.addr[3]
          AND tt-cust.addr[4] = cust.addr[4]
          AND tt-cust.city      = cust.city
          AND tt-cust.state    = cust.state
          AND tt-cust.key     <> cust.key
      NO-ERROR.
   IF AVAILABLE tt-cust
   THEN
      DISPLAY cust.customer tt-cust.customer.
END.

Basically what I am trying to do is find duplicates where the above criteria are meet. The code is more for testing than anything else. Also the version of progress is 10.2A
 
I am surprised that the compiler accepts array elements as index components. It doesn't in 9.1; in 9.1, you get an error 247. My guess is that the error you are seeing is some derivative of this problem and that in 10.x the error message is different and less meaningful.
 
I believe that it is, indeed, the array fields being in the index.

I just tried the sample code using sports2000 and substituting SalesRep for cust and using MonthQuota[1] for the index. That blows it up with the same error.

Commenting out everything except the TT definition will also give you a message that the compiler cannot understand the TT definition.
 
Thanks for all the help.
Would anyone then know of any way to create a temporary index? Or another way to do what I am trying to do?
 
whwar9739 said:
Thanks for all the help.
Would anyone then know of any way to create a temporary index? Or another way to do what I am trying to do?
Click to expand...

I believe this should work for you...
Code: 
DEF TEMP-TABLE tt-cust LIKE cust
 field tt-fname as char
 field tt-lname as char
 ... repeat for other fields ...
  INDEX tt-idx tt-fname tt-lname tt-prim-nm tt-addr1 tt-addr2 tt-addr3 tt-addr tt-city tt-state.

FOR EACH cust NO-LOCK:
   CREATE tt-cust.
   BUFFER-COPY cust TO tt-cust
        ASSIGN tt-cust.tt-fname = cust.fname[1]
               tt-cust.tt-lname = cust.lname[1]
               .. and so on ..
END.

FOR EACH cust NO-LOCK:
   FIND FIRST tt-cust NO-LOCK
      WHERE tt-cust.tt-fname = cust.fname[1]
          AND tt-cust.tt-lname = cust.lname[1]
          AND tt-cust.tt-prim-nm = cust.prim-nm[1]
          AND tt-cust.tt-addr1 = cust.addr[1]
          AND tt-cust.tt-addr2 = cust.addr[2]
          AND tt-cust.tt-addr3 = cust.addr[3]
          AND tt-cust.tt-addr4 = cust.addr[4]
          AND tt-cust.tt-city      = cust.city
          AND tt-cust.tt-state    = cust.state
          AND tt-cust.key     <> cust.key
      NO-ERROR.
   IF AVAILABLE tt-cust
   THEN
      DISPLAY cust.customer tt-cust.customer.
END.
 
You must log in or register to reply here.
Back
Top