Where clause IN

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

Poston Fri, 20 Nov 2009 2:42 pm

Staff created a loop where perccoro a pad and game the company code in a variable, as shown in the following code:
Code: Select all
go_block('EMP');
FIRST_RECORD;
while :emp.NUM_EMP is not null loop
if v_emp_ger_real is null then
   v_emp_ger_real:=:EMP.NUM_EMP;
else
   v_emp_ger_real:=v_emp_ger_real || ',' || :EMP.NUM_EMP;
end if;
NEXT_RECORD;
end loop;
FIRST_RECORD;
and then had to pass the variable v_emp_ger_real as the select parameter, as shown below:
Code: Select all
select ID_CAD_ENTIDADE
from OFN_COLABORADOR_EMAIL
where id_cad_emp_gerencial in (v_emp_ger_real)
group by ID_CAD_ENTIDADE;
But occurs error because the variable v_emp_ger_real is a string and the field of where id_cad_emp_gerencial is an integer.

NOTE: the variable v_emp_ger_real can contain more than one code, ex: 1.2 someone knows how can I fix this?
aline_plr
Location: Rio Preto

Poston Fri, 20 Nov 2009 3:09 pm

Hello * can use dynamic SQL for this. (type, execute_immediate) with the RETURNING clause.
See: viewtopic.php?t=333 * can use one of these: tokenizer viewtopic.php?t=753 one of them including uses pipelined functions any questions, send it to us!


:-o
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Fri, 20 Nov 2009 4:06 pm

Or execute immediate or REF CURSOR.
diegolenhardt
Location: Recife

Poston Fri, 20 Nov 2009 4:40 pm

actually doing this procedure on the forms, and it just isn't working out
aline_plr
Location: Rio Preto

Poston Fri, 20 Nov 2009 4:44 pm

In Forms any way will work, since it uses the compiler version 8i, makes a database procedure called for example: execute_immediate where inside it will have:
Code: Select all
create or replace execute_immediate (instrucao varchar2) is
begin
  execute immediate instrucao;
end;
ai no forms you calls the \"execute_immediate\" by passing its dynamic sql, =]
diegolenhardt
Location: Recife

Poston Fri, 20 Nov 2009 4:48 pm

A recordgroup does not work in this case?

If you need the REFCURSOR will have to call a database procedure will make the cursor and then you return a vector.
diegolenhardt
Location: Recife

Poston Wed, 02 Dec 2009 7:56 am

This block couldn't be a list?
porque with a list you could do the join in the select, using the CAST function Hug, Thiago
thiagoaranha
Location: Santos/SP


  • See also
    Replies
    Views
    Last Post