temp-table AND insert into

[whwar9739]

I am trying to create a temp-table and then insert into it a field and the count of all fields with that same value. I was hoping to do that with an insert into statement. Here is some sample code to try to give the idea. When I try to do this I get the error that temp_table does not exist or is not accesible. I was wondering if anyone else here has ever done this. If not I know of another way using for each/break by and first-of and last-of functions.

DEF TEMP-TABLE temp_table
FIELD l-field AS DEC
FIELD l-count AS INT.

INSERT INTO temp_table (l-field, l-count)
SELECT field, count(*)
FROM actual_table
GROUP BY field
HAVING(count(*) > 1).

 

[TomBascom]

You're trying to mix 4GL and SQL. This will only lead to frustration and pain. Don't do it. The 4GL does support some limited SQL-89 statements but they are (finally!) officially deprecated and have never been anything other than an afterthought. Nothing good lies down this path.

You're probably trying to do something like this:

define temp-table ttTable no-undo
  field f1 as decimal
  field f2 as integer
.

for each realTable no-lock where someField > 1:

  create ttTable.
  assign
    ttTable.f1 = realTable.someField
    ttTable.f2 = realTable.someOtherField
  .

end.

 

[whwar9739]

I have done similar things such as below. Would anyone have any suggestions as to a better way to complete the same task?

DEFINE TEMP-TABLE ttTable
   FIELD f1 AS DEC
   FIELD f2 AS INT.

FOR EACH realTable BREAK BY realTable.f1:
   IF FIRST-OF(realTable.f1)
   THEN
      l-count = 0.
   
   l-count = l-count + 1.
   
   IF LAST-OF(realTable.f1)
   THEN DO:
      CREATE ttTable.
      ASSIGN
         ttTable.f1 = realTable.f1
         ttTable.f2 = l-count
      .
   END.
END.

 

[Casper]

If you want to count the number of records in a break group and put them in a temp-table then that is the way I would do it as well.

Another way to do this, which I'm not sure of if this is still supposed to be used, is using the accumalate and sub-count functions.

I don't use these statements because they always seem kind of illogical to me, but the language lets you use them.

An example would be:

DEFINE VARIABLE iTmp AS INTEGER NO-UNDO.
 
DEFINE TEMP-TABLE ttTable NO-UNDO.
   FIELD f1 AS DECIMAL
   FIELD f2 AS INTEGER.
 
FOR EACH realTable BREAK BY realTable.f1:
 
   ACCUMULATE iTmp (SUB-COUNT by realTable.f1).
 
   IF LAST-OF(realTable.f1)
   THEN DO:
      CREATE ttTable.
      ASSIGN
         ttTable.f1 = realTable.f1
         ttTable.f2 = ACCUMULATE SUB-COUNT by realTable.f1 iTmp.
   END.
END.

Well, that was more just to show you that this stuff exists too.

Casper.