Index On Temp Table?

L

linkin182

New Member
Good Day,

Is it possible to create an index on a temp. table?
I've tried this :-

CREATE INDEX tbl_idx1 ON tbl_temp(tbl_temp_lot).

Am I doing it wrong?
 
When you define your temp-table then you can define indexes as well.

So either:
Code: 
define temp-table ttTemp no-undo
   field ctChar as character
   field ctLog  as logical
   field itInt    as integer
index iIdx is primary unique itInt ctChar.

or (dynamic)

Code: 
DEFINE VARIABLE hTemp AS HANDLE      NO-UNDO.
 
CREATE TEMP-TABLE hTemp.
/* add fields */
hTemp:ADD-NEW-FIELD('ctChar','CHARACTER').
hTemp:ADD-NEW-FIELD('ltLog','LOGICAL').
hTemp:ADD-NEW-FIELD('itInt','INTEGER').
 
/* add index */
hTemp:ADD-NEW-INDEX('iIdx',YES,YES).
hTemp:ADD-INDEX-FIELD( 'iIdx','itInt').
hTemp:ADD-INDEX-FIELD( 'iIdx','ctChar').
 
hTemp:TEMP-TABLE-PREPARE('resultset').


HTH,

Casper
 
Oops, didn't even notice it :-)
If you need to do this with SQL then I can't help you. I wasn't even aware that it is possible to define tempory tables with SQL (is it?).

Casper.
 
Good day,

Thank you for your reply! However, I failed to inform that im currently using SQL-89 and Progress Version 9.1B...;p

However, the reason im trying to use an index into a temp table is because of the time taking it to process...Im currently extracting from 2 tables with FOR EACH and data im getting are quite substantial..etc:-

DEFINE TEMP-TABLE Tmp_tbl1
FIELD Tmp_No Like TblA.TblA_No
FIELD Tmp_Dt like TblA.TblA_Dt
FIELD Tmp_Desc like TblA.TblA_Desc
FIELD Tmp_Qty like TblA.TblA_Qty
"INDEX tmp_idx1 IS PRIMARY UNIQUE Tmp_No."

FOR EACH TblA NO-LOCK WHERE TblA_No = "ABC".
FIND LAST TblY NO-LOCK WHERE TblY_No = TblA_No.
END.

FOR EACH TblB NO-LOCK WHERE TblB_No = "ABC".
FIND LAST TblY NO-LOCK WHERE TblY_No = TblB_No.

CREATE Tmp_tbl1

IF AVAILABLE TblA THEN DO :
ASSIGN Tmp_No = TblA_No
Tmp_Dt = TblA_Dt
Tmp_Desc = TblA_Desc
Tmp_Qty = TblA_Qty.
END.

IF AVAILABLE TblB THEN DO :
ASSIGN Tmp_No = TblB_No
Tmp_Dt = TblB_Dt
Tmp_Desc = TblB_Desc
Tmp_Qty = TblB_Qty.
END.
END.

Its taking an average of 15 mins to process, but having run the 2 FOR EACH queries takes me lesser than 30 Secs. or am I not using TblA & TblB's indexes correctly?
 
linkin182 said:
Good day,

Thank you for your reply! However, I failed to inform that im currently using SQL-89 and Progress Version 9.1B...;p
Click to expand...

0 for 2...

SQL-89 from within the Progress 4gl is very bad for your mental health. Don't do that.

9.1B is, well, really old and kind of unsupported and stuff like that. You really ought to upgrade...

As for your 4gl query, I've taken the liberty of reformatting your code:

Code: 
DEFINE TEMP-TABLE Tmp_tbl1
  FIELD Tmp_No Like TblA.TblA_No
  FIELD Tmp_Dt like TblA.TblA_Dt
  FIELD Tmp_Desc like TblA.TblA_Desc
  FIELD Tmp_Qty like TblA.TblA_Qty
  [I][COLOR=blue]INDEX tmp_idx1 IS PRIMARY UNIQUE Tmp_No.[/COLOR][/I]
 
FOR EACH TblA NO-LOCK WHERE TblA_No = "ABC":

  FIND LAST TblY NO-LOCK WHERE TblY_No = TblA_No.

END.  /* are you sure?  this looks like a mistake... */
 
FOR EACH TblB NO-LOCK WHERE TblB_No = "ABC":

  FIND LAST TblY NO-LOCK WHERE TblY_No = TblB_No.
 
  CREATE Tmp_tbl1
 
  IF AVAILABLE TblA THEN
    DO:
      ASSIGN
        Tmp_No = TblA_No
        Tmp_Dt = TblA_Dt
        Tmp_Desc = TblA_Desc
        Tmp_Qty = TblA_Qty
      .
    END.
 
  IF AVAILABLE TblB THEN
    DO:
      ASSIGN
        Tmp_No = TblB_No
        Tmp_Dt = TblB_Dt
        Tmp_Desc = TblB_Desc
        Tmp_Qty = TblB_Qty
      .
    END.

END.

Its taking an average of 15 mins to process, but having run the 2 FOR EACH queries takes me lesser than 30 Secs. or am I not using TblA & TblB's indexes correctly?
Click to expand...

I don't know -- what is with the FIND LAST stuff? That seems odd. Do TblA and TblB have indexes on TblA_No and TblB_No?

As my inline comment indicates, the code above looks wrong. I think you probably coded:

Code: 
DEFINE TEMP-TABLE Tmp_tbl1
  FIELD Tmp_No Like TblA.TblA_No
  FIELD Tmp_Dt like TblA.TblA_Dt
  FIELD Tmp_Desc like TblA.TblA_Desc
  FIELD Tmp_Qty like TblA.TblA_Qty
  [I][COLOR=blue]INDEX tmp_idx1 IS PRIMARY UNIQUE Tmp_No.[/COLOR][/I]
 
FOR EACH TblA NO-LOCK WHERE TblA_No = "ABC":

  FIND LAST TblY NO-LOCK WHERE TblY_No = TblA_No.

  FOR EACH TblB NO-LOCK WHERE TblB_No = "ABC":

    FIND LAST TblY NO-LOCK WHERE TblY_No = TblB_No.
 
    CREATE Tmp_tbl1
 
    IF AVAILABLE TblA THEN
      DO:
        ASSIGN
          Tmp_No = TblA_No
          Tmp_Dt = TblA_Dt
          Tmp_Desc = TblA_Desc
          Tmp_Qty = TblA_Qty
        .
      END.
 
    IF AVAILABLE TblB THEN
      DO:
        ASSIGN
          Tmp_No = TblB_No
          Tmp_Dt = TblB_Dt
          Tmp_Desc = TblB_Desc
          Tmp_Qty = TblB_Qty
        .
      END.

  END.
END.

Which would take forever because each iteration of the TblA loop would contain a full pass through TblB.

What you probably want is something like this:

Code: 
DEFINE TEMP-TABLE Tmp_tbl1
  FIELD Tmp_No Like TblA.TblA_No
  FIELD Tmp_Dt like TblA.TblA_Dt
  FIELD Tmp_Desc like TblA.TblA_Desc
  FIELD Tmp_Qty like TblA.TblA_Qty
  [I][COLOR=blue]INDEX tmp_idx1 IS PRIMARY UNIQUE Tmp_No.[/COLOR][/I]
 
FOR EACH TblA NO-LOCK WHERE TblA_No = "ABC":

  FIND LAST TblY NO-LOCK WHERE TblY_No = TblA_No.

  CREATE Tmp_tbl1
 
  IF AVAILABLE TblA THEN    /* not really a need for the if avail... */
    DO:
      ASSIGN
        Tmp_No = TblA_No
        Tmp_Dt = TblA_Dt
        Tmp_Desc = TblA_Desc
        Tmp_Qty = TblA_Qty
      .
    END.

END.
 
FOR EACH TblB NO-LOCK WHERE TblB_No = "ABC":

  FIND LAST TblY NO-LOCK WHERE TblY_No = TblB_No.
 
  CREATE Tmp_tbl1
 
  IF AVAILABLE TblB THEN    /* not really a need for the if avail... */
    DO:
      ASSIGN
        Tmp_No = TblB_No
        Tmp_Dt = TblB_Dt
        Tmp_Desc = TblB_Desc
        Tmp_Qty = TblB_Qty
      .
    END.

END.

Which is more compactly written as:

Code: 
DEFINE TEMP-TABLE Tmp_tbl1
  FIELD Tmp_No Like TblA.TblA_No
  FIELD Tmp_Dt like TblA.TblA_Dt
  FIELD Tmp_Desc like TblA.TblA_Desc
  FIELD Tmp_Qty like TblA.TblA_Qty
  [I][COLOR=blue]INDEX tmp_idx1 IS PRIMARY UNIQUE Tmp_No.[/COLOR][/I]

/* collect TblA data into the temp-table */ 
FOR EACH TblA NO-LOCK WHERE TblA_No = "ABC":
  FIND LAST TblY NO-LOCK WHERE TblY_No = TblA_No.
  CREATE Tmp_tbl1
  ASSIGN
    Tmp_No = TblA_No
    Tmp_Dt = TblA_Dt
    Tmp_Desc = TblA_Desc
    Tmp_Qty = TblA_Qty
  .
END.

/* collect TblB data into the temp-table */ 
FOR EACH TblB NO-LOCK WHERE TblB_No = "ABC":
  FIND LAST TblY NO-LOCK WHERE TblY_No = TblB_No.
  CREATE Tmp_tbl1
  ASSIGN
    Tmp_No = TblB_No
    Tmp_Dt = TblB_Dt
    Tmp_Desc = TblB_Desc
    Tmp_Qty = TblB_Qty
  .
END.

Of course that assumes that TblA and TblB have no common Tmp_No values -- if they do then you're going to have collisions and nasty error messages. To avoid that I would do something like this:

Code: 
DEFINE TEMP-TABLE Tmp_tbl1
  FIELD Tmp_Src as character
  FIELD Tmp_No Like TblA.TblA_No
  FIELD Tmp_Dt like TblA.TblA_Dt
  FIELD Tmp_Desc like TblA.TblA_Desc
  FIELD Tmp_Qty like TblA.TblA_Qty
  [I][COLOR=blue]INDEX tmp_idx1 IS PRIMARY UNIQUE Tmp_Src Tmp_No.[/COLOR][/I]

/* collect TblA data into the temp-table */ 
FOR EACH TblA NO-LOCK WHERE TblA_No = "ABC":
  FIND LAST TblY NO-LOCK WHERE TblY_No = TblA_No.
  CREATE Tmp_tbl1
  ASSIGN
    Tmp_Src = "TblA"
    Tmp_No = TblA_No
    Tmp_Dt = TblA_Dt
    Tmp_Desc = TblA_Desc
    Tmp_Qty = TblA_Qty
  .
END.

/* collect TblB data into the temp-table */ 
FOR EACH TblB NO-LOCK WHERE TblB_No = "ABC":
  FIND LAST TblY NO-LOCK WHERE TblY_No = TblB_No.
  CREATE Tmp_tbl1
  ASSIGN
    Tmp_Src = "TblB"
    Tmp_No = TblB_No
    Tmp_Dt = TblB_Dt
    Tmp_Desc = TblB_Desc
    Tmp_Qty = TblB_Qty
  .
END.
 
Oh DANG! should've noted that edit error..:eek:

That FIND LAST is supposed to go into :

Code: 
Assign Tmp_Desc = TblY_Desc

The reason is, im trying to gather from 2 tables and insert them into 1 general temp table.

And yes..im getting

Code: 
"TblA_No already has ""."

when im trying to assign it.
 
You must log in or register to reply here.
Back
Top