temporary table

[phneoix]

how do i create temporary table

thanks

[mdipierro]

what's a temporary table? What do you need to do exactly?

[rb]

If you are using SQLite then you could create a db in memory and keep
your temp table there. You can create the table outside of web2py and
then in your SQLDB call you can add the parameter migrate=False to
import the db into web2py.

--
Rb

[Fran]

On Jul 22, 9:41 pm, rb <rbspg...@gmail.com> wrote:
> If you are using SQLite then you could create a db in memory and keep
> your temp table there. You can create the table outside of web2py and
> then in your SQLDB call you can add the parameter migrate=False to
> import the db into web2py.

I guess you could achieve something similar by storing your temporary
data in either the Cache or the Session?

F

[Vidul Petrov]

The implementation of temporary tables is not MVC, but RDBMS specific.

[Hans Donner]

That's why the question was asked: what do you need to do exactly?
Perhaps a more elegant solution can be used.

[Hans Donner]

That's why the question was asked: what do you need to do exactly?
Perhaps a more elegant solution can be used.

On Thu, Jul 23, 2009 at 03:10, Vidul Petrov<vidul...@gmail.com> wrote:
>

[Vidul Petrov]

> That's why the question was asked: what do you need to do exactly?

Do this:

db._execute('CREATE TEMPORARY TABLE tmp (id INT)')
# or whatever the SQL server's dialect requires

[Fran]

On Jul 23, 7:12 am, Vidul Petrov <vidul.r...@gmail.com> wrote:
> > That's why the question was asked: what do you need to do exactly?

> db._execute('CREATE TEMPORARY TABLE tmp (id INT)')
> # or whatever the SQL server's dialect requires

This isn't what you're trying to achieve.
This is a proposed way of achieving it.

F

[phneoix]

thanks for the replies.
sorry for the delay in replying.

i am having 8 different lookup tables which contains the master list
of all the materials.(endmills, gauging instruments, drills,tooling
systems, etc).these contain the material name and respective product
id's.
there is another table which iam using for issuing material and
tracking their usage. this table contains the product ids of all the
material issued and the their respective qty.

NOW my real problem is i want to show the name of product instead of
(OR ALONG WITH) their product ID's. but i am not able to join all
these tables together. therefore i was considering of creating a
temporary table which contains all the product ids and their desc
pulled from the lookup tables and join it with material issue table.

i hope iam not confusing you guys.

On Jul 23, 3:31 pm, Fran <francisb...@googlemail.com> wrote:
> On Jul 23, 7:12 am, Vidul Petrov <vidul.r...@gmail.com> wrote:
>
> > > That's why the question was asked: what do you need to do exactly?

> > db._execute('CREATETEMPORARYTABLEtmp (id INT)')

[Fran]

On Jul 29, 3:14 pm, phneoix <neo.stea...@gmail.com> wrote:
> i hope iam not confusing you guys.

No, this is very clear & makes sense.

My first thought would be to merge all the lookup tables into 1 & add
a 'type' column, but I guess you can't do that?

Temporary tables aren't always recommended:
http://www.sql-server-performance.com/articles/per/derived_temp_tables_p1.aspx

How about doing something like using a dictionary to store the
temporary table in:

mystorage = {}
lookup_tables = [endmills, gauging, etc]
for lookup_table in lookup_tables:
rows = db(db[lookup_table].id>0).select()
for row in rows:
mystorage[row.product_id] = row.product_name

Then instead of doing the Join with the material issue table, just do
a lookup in the dict...

F

[phneoix]

thanks for the suggestion

this is what iam trying to achieve

db
(db.material_issue.product_id==created_temporary_table.product_id).select
(db.material_issue.product_id,created_temporary_table.name,db.material_issue.issued_qty)

On Jul 29, 11:21 am, Fran <francisb...@googlemail.com> wrote:
> On Jul 29, 3:14 pm, phneoix <neo.stea...@gmail.com> wrote:
>
> > i hope iam not confusing you guys.
>
> No, this is very clear & makes sense.
>
> My first thought would be to merge all the lookup tables into 1 & add
> a 'type' column, but I guess you can't do that?
>

> Temporarytables aren't always recommended:http://www.sql-server-performance.com/articles/per/derived_temp_table...

>
> How about doing something like using a dictionary to store thetemporarytablein:
>
> mystorage = {}
> lookup_tables = [endmills, gauging, etc]
> for lookup_table in lookup_tables:
>     rows = db(db[lookup_table].id>0).select()
>     for row in rows:
>         mystorage[row.product_id] = row.product_name
>

> Then instead of doing the Join with the material issuetable, just do

[mdipierro]

The best you can do is create a dictionary

d={1:'name1', 2:'name2', 3:'name3'} #where 1,2,3 are id in tmp table

db(db.material_issue.product_id.belongs(d.keys())).select
(db.material_issue.product_id,db.material_issue.issued_qty)

for row in rows:
print row.product_id, d[row.product_id], row.issued_qty

Massimo