temp tables 1

[Tracey]

hi

quick question, can i do this without using a stored procedure? (ie runtime IBQuery)

Select stuff
from table
where thishappens
into temptable

???

 

[MartijnTonies]

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

 

[Tracey]

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)

 

[MartijnTonies]

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

 

[Tracey]

I dont have sessions, i use delphi6 cgi

 

[MartijnTonies]

??

You can create your own unique session ID, right?

Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird

http://www.upscene.com

 

[Tracey]

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

 

[Tracey]

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

cheers for your time

 

[MartijnTonies]

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

 

[Tracey]

OMG!!

that would have been a lot easier...

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

aarrgghh

 

[MartijnTonies]

It's in the docs

Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird

http://www.upscene.com

 

[Tracey]

docs?
whose docs?

 

[MartijnTonies]

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

Martijn Tonies
Database Workbench - the developer tool for InterBase and Firebird

http://www.upscene.com

 

[Tracey]

o
thanks Martijn

(still kicking myself)