declare global temporary table workfile

[jamief]

using declare global temporary table workfile to create a workfile on the fly using SQL then writting and reading data back from this table see attached.

      *--------------------------------------------------------
      *  $CreateTable - create our workfile in qtemp
      *--------------------------------------------------------
     c     $CreateTable  begsr
      *
      *  Create Work file in Qtemp
      *
     c/Exec Sql
     c+ declare global temporary table workfile
     c+ (sqlCompany char(10), superV char(10), SuperVDesc char(30),
     c+    Emp dec(9), EmpDesc char(30), MissDate dec(8,0) ,
     c+    SQLDayofweek char(10), SQLSchdH dec(5,2) , SQLWorkH dec(5,2) ,
     c+    sqlNote char(40))
     c+ with replace
     c/End-Exec
      *
     c                   endsr

[bryce4president]

Re: declare global temporary table workfile

Well I just came across this while looking for a way to create a workfile in Qtemp. I see that at the end you say "with replace". Does that mean the workfile will always only have 1 record? Or does it tell the SQL statement to replace the table if it exists when running the command?

If the table exists in qtemp then I need to clear it, if it doesn't exist then I need to create it. This needs to be done each time I run my program. Is there a command to clear data from a table? I keep looking but it must not be a very logical keyword if it exists. If not keyword to clear then I'm guessing I have to drop the table and recreate it?

Never mind about the clearing part.... of course DELETE FROM workfile. That will do the trick

[BenThurley]

Re: declare global temporary table workfile

TRUNCATE is the SQL keyword from deleting everything. Wouldn't you have to drop the table anyway in case of program changes?

[bryce4president]

Re: declare global temporary table workfile

What do you mean in case of program changes?

[bryce4president]

Re: declare global temporary table workfile

Ok, so I found that I can attempt to drop the table from qtemp, and if it doesn't exist it will throw a warning, but it won't error out. So with that, I can issue the drop command before the declare global temporary command each time and all will be just fine.

Can anybody see any problems with this? It will send a message to the job log but its only a 10 severity so it seems harmless on the surface, I just want to know what you guys think?

[jamief]

Re: declare global temporary table workfile

this part of the command replaces the table each time...

c+ with replace

why are you doing the drop?

[bryce4president]

Re: declare global temporary table workfile

Well what I really need to do is create the file in qtemp if it doesn't exist. If it does exist I need to clear it.

I got into trying some new things out with the drop and temporarily lost sight of my objective... sorry...

I think I get what Ben was saying about program changes. Its just not customary here to put changes in during the middle of the day that would screw this up, but I see his point. I could put the drop into my clean up procedure for this service program and that will take care of it.

[BenThurley]

Re: declare global temporary table workfile

I may have this wrong but it seems that you have a program that will create a temporary table if it doesn't exist and then write records to/from this table. If the table does exist then you want to just clear the table and then continue writing records.

If the table definition in your program changes what will happen? Usually an RPG program change won't compile without the associated file changes but this file is being created and written to using SQL so the compiler won't make this check for you. This means if your program changes and you still have an old copy of your table in qtemp then your program will crash when it comes to write a record. Won't it?

[BenThurley]

Re: declare global temporary table workfile

Having said that it seems that Jamies "with replace" is exactly what you want!

[bryce4president]

Re: declare global temporary table workfile

Great! Got it. Thanks guys