temp tables
temp tables
(OP)
hi
quick question, can i do this without using a stored procedure? (ie runtime IBQuery)
Select stuff
from table
where thishappens
into temptable
???
quick question, can i do this without using a stored procedure? (ie runtime IBQuery)
Select stuff
from table
where thishappens
into temptable
???
RE: 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
RE: temp tables
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)
RE: temp tables
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
RE: temp tables
RE: temp tables
You can create your own unique session ID, right?
Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird
http://www.upscene.com
RE: temp tables
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
RE: temp tables
cheers for your time
RE: temp tables
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
RE: temp tables
that would have been a lot easier...
I had tried ordering it but didnt try using the column number
aarrgghh
RE: temp tables
Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird
http://www.upscene.com
RE: temp tables
whose docs?
RE: temp tables
Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird
http://www.upscene.com
RE: temp tables
thanks Martijn
(still kicking myself)