Concatenating table name

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Tue, 08 Jun 2010 3:05 pm

Anyone know how can I concatenate table name.
Because I have a base in the company I work for which he is closed annually and created a new base. This base we use indirectly.
Example TESTE_2009, TESTE_2010 and need to create something ...

select * from test _ ||to_char (sysdate, ' rrrr ') someone already made something similar?
tchuck
Location: Maringá

Poston Tue, 08 Jun 2010 3:10 pm

Code: Select all
create or replace function count_in_table attr in ( attrval in varchar2, varchar2, tbl in varchar2 ) return number is cnt number;
begin execute immediate ' select count 1 from ) ( ' || tbl || ' where ' || attr || ' = : ' into cnt using attrval;
  return cnt;
end;
/
http://www.adp-gmbh.ch/ora/plsql/exec_immediate.html
victorhugomuniz
Location: Rio de Janeiro - RJ

Jesus está voltando, volte antes para Ele.

Image JavaBlackBelt

Poston Tue, 08 Jun 2010 3:21 pm

Sorry ... but not understand anything ...
I need to concatenate the table and from what I'vê seen here concatenates the fields.
I have 2 tables that only change the year ahead.
tchuck
Location: Maringá

Poston Tue, 08 Jun 2010 3:28 pm

Code: Select all
execute immediate ' select * from test _ ||to_char sysdate, ' rrrr ' ( ) ';
victorhugomuniz
Location: Rio de Janeiro - RJ

Jesus está voltando, volte antes para Ele.

Image JavaBlackBelt

Poston Tue, 08 Jun 2010 3:53 pm

Not worked.!!
:(
tchuck
Location: Maringá

Poston Tue, 08 Jun 2010 4:01 pm

tchuck, Worked in a company in which I passed through this kind of situation where there were several tables with consolidated information for the year. The tables had the number of the year in his name.
Below is an abridged version of the anonymous block I created at that time to answer queries that swept into tables from more than one year.

At the time I needed to use cursors, but there are other ways to use dynamic sql as the EXECUTE IMMEDIATE (quoted by victorhugomuniz) or the DBMS_SQL package.

The idea is there:
Code: Select all
ACCEPT v_ano_i  PROMPT 'Digite o ano INICIAL (Formato: YYYY):'
ACCEPT v_ano_f  PROMPT 'Digite o ano FINAL (Formato: YYYY):'
DECLARE
  /* Variáveis para o PROMPT */
  v_ano_i     NUMBER(4) :='&v_ano_i';
  v_ano_f     NUMBER(4) :='&v_ano_f';
  /* Cursores e Recordsets */
  TYPE C1         IS REF CURSOR;
  vC1             C1;
  StrSql          VARCHAR (200);
  LINHA           vC1%ROWTYPE;
BEGIN
  WHILE v_ano_i <= v_ano_f LOOP
    /* Instrução SQL para o Cursor */
    StrSql:= 'SELECT  CO_ANO||
                      CO_MES AS CAMPOS
                FROM  DONO.||v_ano_i||'_MES
            GROUP BY  CO_ANO,
                      CO_MES';
    OPEN vC1 FOR StrSql;
    LOOP
      FETCH vC1 INTO LINHA;
      EXIT WHEN vC1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(vC1.CO_ANO||vC1.CO_MES);
    END LOOP;
    CLOSE vC1;
  v_ano_i := v_ano_i + 1;
  END LOOP;
END;
/
thiago_r_f
Location: Rio de Janeiro - RJ

O Backup é meu pastor e nada me faltará!
Thiago Rodrigues de Farias

Poston Thu, 10 Jun 2010 4:46 pm

Thanks for your help ...
I got it from the parse pretty cool é dbms_sql. use..

I created a table in my bank playing all data from the table that I wanted.


Code: Select all
create or replace procedure prc_carrega_tabela(va_cod_ano in number) is
   va_cursor1    integer;
   va_linha        integer;
   native           constant integer := 1;
   va_des_ano   varchar(6);
begin
begin
   select lpad(ano,4)
     into va_des_ano
     from tabela_cadastro_ano
    where ano = va_cod_ano;
end;
   va_cursor1 := dbms_sql.open_cursor;
   dbms_sql.parse(va_cursor1,'insert into nova_tabela
                                         (campo1,campo2,campo3)
                                  select temp1,temp2,temp3
                                    from schema.tabela'||va_ano
                                    ||' where
                                     temp3 between ano_inicial and ano_final',native);
   va_linha := dbms_sql.execute(va_cursor1);
   commit;
   end;
tchuck
Location: Maringá


  • See also
    Replies
    Views
    Last Post


Return to SQL

Who is online

Users browsing this forum: No registered users and 13 guests