InterBase doesn't do temp-tables.

Perhaps you can explain what you're trying to do?

Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird
http://www.upscene.com

oh dear
ok

i have the following tables

MACHINE
machineid

ENTRY
EntryID
MachineID
EntryDate
Quantity
StartQuantity
EndQuantity

JOB
JobID
MachineID

JOBLINE
JoblineID
JobID
JobDate
JobLineCost

I need to select all entries between 2 dates, and all joblines between 2 dates, for every machine where there is an entry in one or the other.

rather than selecting all machines, then doing individual selects from each table for each machine, it was suggested that i could select the records, put them into temp tables to minimize the query count.

I am creating web reports, and would like to keep sql processing time down. (as well as the amount of records sent over the internet)

Hi,

Then why do you need a temp-table? Why not create a presistent table and add an extra column "sessionID" or something - then you can put whatever data in it for a specific web-session.

Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird
http://www.upscene.com

I dont have sessions, i use delphi6 cgi

??

You can create your own unique session ID, right?

Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird
http://www.upscene.com

oh yeah, sorry didnt read that properly

yes, that is one of the ways i am looking at.

I have managed to get all my info out in one sql, however as it is unioned, i get data in two blocks and want to combine them and then re-sort... but that sounds like another thread.

I will keep your suggestion in mind tho Martin.  

SELECT 'jobcost1',
   sum(JOBLINE.Joblinecost) jobcost , sum(0) manhours, sum(0) machinehours, machine.ref fork
FROM
  JOBLINE JOIN
  JOB ON JOBLINE.JobID = JOB.JobID
  join machine on machine.machineid = job.machineid
WHERE
  JOBLINE.workDate BETWEEN '01/01/03' AND '07/31/03'
group by machine.ref
UNION
SELECT 'entries1',
   sum(0) jobcost, sum(ENTRY.Quantity) manhours, sum(ENTRY.EndQuantity - ENTRY.StartQuantity) machinehours, machine.ref fork
FROM
  ENTRY
  join machine on entry.machineid = machine.machineid
WHERE
  ENTRY.EntryDate BETWEEN '01/01/03' AND '07/31/03'
  and entry.companyid = 1
group by machine.ref

just to update, i ended up using a clientdataset to order my resulting fields...

cheers for your time

Ah, good to know you figured something out :)

btw, you can also use ORDER BY 1 (or other column) with the UNION statement to order your results.

Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird
http://www.upscene.com

OMG!!

that would have been a lot easier...

I had tried ordering it but didnt try using the column number  

aarrgghh

It's in the docs :)

Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird
http://www.upscene.com

docs?
whose docs?

http://info.borland.com/techpubs/interbase/

Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird
http://www.upscene.com

o
thanks Martijn

(still kicking myself)