×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

temp tables

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

???

RE: temp tables

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

RE: temp tables

(OP)
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)

RE: temp tables

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

RE: temp tables

(OP)
I dont have sessions, i use delphi6 cgi

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

(OP)
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


RE: temp tables

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

cheers for your time

RE: temp tables

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

RE: temp tables

(OP)
OMG!!

that would have been a lot easier...

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

aarrgghh

RE: temp tables

It's in the docs :)

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

RE: temp tables

(OP)
docs?
whose docs?

RE: temp tables

(OP)
o
thanks Martijn

(still kicking myself)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close