AinhoaO wrote: > > Hi, > > Can any one please tell me a workaround for this issue..... > > > SAPDB version: 7.4.3.17 > > I�m new in Sapdb and also in SQL.My problem is, when I > execute this query > first time I get the good result, but the second time I get > this error: > > Base table or view already exists;-6000 POS(1) Duplicate table name. > > This is the query: > > select t.nombre, sum(tabla_importes.importe) as impor, > t.tecnologia_id_pk > from tecnologias t left join > (select co.tech_id_fk, co.importe, cl.razon_social, > co.mes, co.ano > from clientes cl, consumos co, empresas_grupo_ttt e > where co.cliente_id_fk = cl.cliente_id_pk > and e.empresa_id_pk = 1 > and co.mes =1 > and co.ano = 2000 > and cl.cliente_id_pk = 1 > ) tabla_importes > on t.tecnologia_id_pk = tabla_importes.tech_id_fk > where tecnologia_id_pk IN( > Select tecno.tecnologia_id_pk > from tecnologias tecno, consumos c, empresas_grupo_ttt egt > where tecno.tecnologia_id_pk = c.tech_id_fk > and c.empresa_id_fk = egt.empresa_id_pk > ) > group by t.nombre, t.tecnologia_id_pk > order by t.nombre, > > What is the problem?, thank you
Hi, sorry, but a workaround I do not have so far. Could you please provide a vtrace according to http://sapdb.2scale.net/moin.cgi/VTrace Please do NOT provide to me directly, I will b out of office for some days. On the other hand, if you did not shorten your select, but this is the original one, then I would like to make some remarks: >From the inner select (select co.tech_id_fk, co.importe, cl.razon_social, co.mes, co.ano from clientes cl, consumos co, empresas_grupo_ttt e where co.cliente_id_fk = cl.cliente_id_pk and e.empresa_id_pk = 1 and co.mes =1 and co.ano = 2000 and cl.cliente_id_pk = 1 ) tabla_importes outside are needed only tabla_importes.importe and tabla_importes.tech_id_fk. The last three output-columns cl.razon_social, co.mes, co.ano are there for ?? There is no join-predicate between e and another table, assuming that empresa_id_pk is the only primary key, then for each combination co-cl one row is returned (if empresa_id_pk = 1 is true) or none at all. This is, what you want? And the combination co.cliente_id_fk = cl.cliente_id_pk AND cl.cliente_id_pk = 1 means co.cliente_id_fk = 1 (if we assume, that there is a foreign key between them, I assume that because of the names) --> in the end we could get rid of clientes cl in the inner select without loosing info/qualification? Depending on the existence of e.empresa_id_pk = 1 we could get rid of that table, too, and then get rid of that select within brackets, just leaving consumos co on the right side of the left join. May be that this will help, may be I missed something, misenterpreted names, did not see the full qualification. But thinking about that and trying may help until we have some further info. Elke SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
