clause in

[aline_plr]

Guys, with a problem.
is the following in a Function I receive as a parameter a string that parameter is to be used in the select clause. Ex:

The value of variable CR = is 'F03120, F03141'

Select all

function relat_geral_economico ( cr varchar2) return Bollean   
cr1 varchar2(50);      
is  
 
begin 
for reg in (select o.cod_centrocusto from v_ofn_orcamento o  
where o.cod_centrocusto in ( cr )) loop 
cr1:=reg.cod_centrocusto; 
end loop; 
 
return true; 
end

How can I do to This select run without I need to play in Temporary Table or on a Table Function?

[Marciel]

You could create a function in the bank that verifies that the text is in on the string with this format. Then I would use this way:

Select all

BEGIN 
  FOR REG IN (SELECT O.COD_CENTROCUSTO 
                FROM V_OFN_ORCAMENTO O 
               WHERE F_EXISTE (O.COD_CENTROCUSTO, CR) = 1) LOOP 
    CR1 := REG.COD_CENTROCUSTO; 
  END LOOP; 
END;

I do not know if there is how to do otherwise.

[aline_plr]

but I do not understand how this function would be f_existe

[SergioLBJr]

Your problem in the case is that you want to do something like this:

Select all

in ('F03120','F03141')

But is doing something like this:

Select all

in (F03120,F03141)

? ????

If yes:

At the time of riding this CR you concatena '' 'before and after values.

I had this problem with a report called by a form.

Showing those that the CR is returning until you hit, but it is only to concatenate the amount of simple quotation marks that it works.

If it is not this problem then it explains better.

[aline_plr]

This idea of ??concatting is very good, but it did not work. My select did not return anything.
So works

Select all

select sum(v.val_lancamento) val_lancamento 
       from v_ofn_orcamento v 
       where v.tip_orcamento in ('PL', 'IAC') 
       and v.cod_centrocusto in ('F03120', 'F03141')   
       and v.num_conta_pai=24;

So does not work

Select all

select sum(v.val_lancamento) val_lancamento 
       from v_ofn_orcamento v 
       where v.tip_orcamento in ('PL', 'IAC') 
       and v.cod_centrocusto in ('''F03120'',''F03141''')   
       and v.num_conta_pai=24;

I did something wrong not to work. I need a lot of it.

[SergioLBJr]

It will not work even in the second

of a dbms_output.putline (CR);

concatena quotation marks until it is like this 'F03120', 'F03141'.

You must be concatenating more quotation marks than the requirement.

[aline_plr]

I did the Select Avixo to see what returned

Select '' 'F03120' ',' 'F03141' 'CR From Dual

He returned:' F03120 ',' F03141 '

and is correct the select below remains unworked, does not return any line

Select all

select sum(v.val_lancamento) val_lancamento  
from v_ofn_orcamento v  
where v.tip_orcamento in ('PL', 'IAC')  
and v.cod_centrocusto in (select '''F03120'',''F03141''' cr from dual)  
and v.num_conta_pai=24;  

What should I be doing wrong, the quotation marks It seems they are not.
Does this really work?

[SergioLBJr]

Post the code where you ride the cr parameter for me to take a look.

[aline_plr]

I just assign the value to variable cr.
Ex:

Select all

cr:='''F03120'',''F03141''';

[SergioLBJr]

This:

Select all

cr := '''||F03120||'',''||F03141||''';

[SergioLBJr]

And it has quotes left over or missing aí

do

Select all

cr:= ''||F03120||'',''||F03141||'';

or so

Select all

cr:= ''''||F03120||'''',''''||F03141||'''';

[aline_plr]

It does not work, are you with Oracle installed there? Take the test making the examples that passed me.

Select all

cr:= ''||F03120||'',''||F03141||''; 

or thus

Select all

cr:= ''''||F03120||'''',''''||F03141||'''';

This form F03120 and F03141 do not stand in quotation marks and thus are not string, so it gives error At allocation.
Sorry, but I do not know how this can work out. Have you ever used this in practice? If this wire would solve a problem for me

[fsitja]

To do in you can not compare with a string. For Oracle is all a variable only. The way is to share the string using a Table Function and throwing her output at IN.

Select all

 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0  
Connected as fsitja 
  
SQL>  
SQL> CREATE TABLE t1 AS 
  2  SELECT 1 col1 FROM dual UNION ALL 
  3  SELECT 2 col1 FROM dual UNION ALL 
  4  SELECT 35 col1 FROM dual UNION ALL 
  5  SELECT 50 col1 FROM dual UNION ALL 
  6  SELECT 100 col1 FROM dual UNION ALL 
  7  SELECT 4000 col1 FROM dual UNION ALL 
  8  SELECT 5000 col1 FROM dual 
  9  / 
  
Table created 
SQL> CREATE OR REPLACE TYPE tab_number AS TABLE OF NUMBER 
  2  / 
  
Type created 
SQL> CREATE OR REPLACE FUNCTION in_list(p_in_list IN VARCHAR2) RETURN tab_number AS 
  2    v_tab     tab_number := tab_number(); 
  3    i         NUMBER; 
  4  BEGIN 
  5    i := 1; 
  6    LOOP 
  7      v_tab.extend; 
  8      v_tab(v_tab.last) := regexp_substr(p_in_list, '[0-9]+', 1, i); 
  9      EXIT WHEN v_tab(v_tab.last) IS NULL; 
 10      i := i + 1; 
 11    END LOOP; 
 12    v_tab.trim; 
 13    RETURN v_tab; 
 14  END; 
 15  / 
  
Function created 
SQL> SELECT * 
  2    FROM t1 
  3   WHERE col1 IN (SELECT * FROM TABLE(CAST(in_list('2, 35, 4000') AS tab_number))); 
  
      COL1 
---------- 
         2 
        35 
      4000 
  
SQL>  

[SergioLBJr]

I already used to call report.

I assembled a text parameter that received how many records the user informed a multirow block and the inside the report I put the parameter inside an in.

Select all

cr:= ''''||'F03120'||''''||','||''''||'F03141'||''''

In PL this worked for me.

[fsitja]

Yes, but it was using dynamic SQL then. It is not always possible or desirable to use dynamic SQL.

[SergioLBJr]

Yes was used for a dynamic SQL.

What I think is what Aline wants since it is using a parameter for IN.

of the contrary would only play the values ??there.

[aline_plr]

In PLSQL

Select all

cr:= ''''||'F03120'||''''||','||''''||'F03141'||''''  

worked on a Select for you?

[Marciel]

Select all

 
CREATE OR REPLACE FUNCTION F_EXISTE (YSTRING IN VARCHAR2, YCOD IN NUMBER) 
  RETURN NUMBER IS 
  YAUX    VARCHAR2 (4000) := YSTRING; 
  YCONT   NUMBER          := 0; 
  YNUM    NUMBER; 
BEGIN 
  WHILE YCONT < LENGTH (YAUX) LOOP 
    YCONT := INSTR (YAUX, ','); 
    YNUM := TO_NUMBER (SUBSTR (YAUX, 
                               1, 
                               YCONT)); 
 
    IF YNUM = YCOD THEN 
      RETURN 1; 
    END IF; 
 
    YAUX := SUBSTR (YAUX, YCONT + 1); 
  END LOOP; 
 
  RETURN 0; 
END F_EXISTE; 
 
----------- 
 
SELECT O.COD_CENTROCUSTO 
  FROM V_OFN_ORCAMENTO O 
 WHERE F_EXISTE (O.COD_CENTROCUSTO, CR) = 1 
 

[Marciel]

I think this function I posted will fail to check the last element of the string. A way to fix is ??to change the line below:

Select all

 
  YAUX    VARCHAR2 (4000) := YSTRING || ',';  

[aline_plr]

For me it did not function in plsql this:

Select all

cr:= ''''||'F03120'||''''||','||''''||'F03141'||''''

[fsitja]

Marciel,
is a creative solution, but presents a scalability problem. Imagine if the table has x million lines, you will have to call the user-defined function x million times and the select performance goes into space ... this because the query is simple, now puts joins and other operations there and it's over.

takes a look at the performance comparison of the solutions. I tested with 1 million lines, which is very little comparing with many tables out there.

Select all

 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0  
Connected as fsitja 
  
SQL>  
SQL> SET SERVEROUTPUT ON 
SQL> CREATE TABLE tab1 AS 
  2  SELECT LEVEL col1 FROM dual 
  3  CONNECT BY LEVEL < 1000000 
  4  / 
  
Table created 
SQL> CREATE OR REPLACE FUNCTION F_EXISTE (YSTRING IN VARCHAR2, YCOD IN NUMBER) 
  2    RETURN NUMBER IS 
  3    YAUX    VARCHAR2 (4000) := YSTRING || ','; 
  4    YCONT   NUMBER          := 0; 
  5    YNUM    NUMBER; 
  6  BEGIN 
  7    WHILE YCONT < LENGTH (YAUX) LOOP 
  8      YCONT := INSTR (YAUX, ','); 
  9      YNUM := TO_NUMBER (SUBSTR (YAUX, 
 10                                 1, 
 11                                 YCONT)); 
 12   
 13      IF YNUM = YCOD THEN 
 14        RETURN 1; 
 15      END IF; 
 16   
 17      YAUX := SUBSTR (YAUX, YCONT + 1); 
 18    END LOOP; 
 19   
 20    RETURN 0; 
 21  END F_EXISTE; 
 22  / 
  
Function created 
SQL> CREATE OR REPLACE TYPE tab_number AS TABLE OF NUMBER 
  2  / 
  
Type created 
SQL> CREATE OR REPLACE FUNCTION in_list(p_in_list IN VARCHAR2) RETURN tab_number AS 
  2    v_tab     tab_number := tab_number(); 
  3    i         NUMBER; 
  4  BEGIN 
  5    i := 1; 
  6    LOOP 
  7      v_tab.extend; 
  8      v_tab(v_tab.last) := regexp_substr(p_in_list, '[0-9]+', 1, i); 
  9      EXIT WHEN v_tab(v_tab.last) IS NULL; 
 10      i := i + 1; 
 11    END LOOP; 
 12    v_tab.trim; 
 13    RETURN v_tab; 
 14  END; 
 15  / 
  
Function created 
SQL> DECLARE 
  2    tab1   NUMBER; 
  3    t2   NUMBER; 
  4    qtty NUMBER; 
  5  BEGIN 
  6    tab1 := dbms_utility.get_time; 
  7    SELECT COUNT(*) INTO qtty FROM tab1 WHERE col1 IN (SELECT * FROM TABLE(in_list('2, 35, 4000'))); 
  8    t2 := dbms_utility.get_time; 
  9    dbms_output.put_line(qtty || ' registros em ' || to_char((t2 - tab1) / 100, '990.000') || ' segundos'); 
 10    tab1 := dbms_utility.get_time; 
 11    SELECT count(*) INTO qtty FROM tab1 WHERE f_existe('2, 35, 4000', col1) = 1; 
 12    t2 := dbms_utility.get_time; 
 13    dbms_output.put_line(qtty || ' registros em ' || to_char((t2 - tab1) / 100, '990.000') || ' segundos'); 
 14  END; 
 15  / 
  
3 registros em    0.280 segundos 
3 registros em   10.410 segundos 
  
PL/SQL procedure successfully completed 
SQL> DROP TABLE TAB1; 
  
Table dropped 
SQL> DROP FUNCTION F_EXISTE; 
  
Function dropped 
SQL> DROP FUNCTION in_list; 
  
Function dropped 
SQL> DROP TYPE tab_number; 
  
Type dropped 
  
SQL>  

of 0.28 seconds to 10.41.

Aline, to be able to use string as Sérgio suggested, you need to use dynamic SQL. So it's not working.

I would rather be with static SQL not to pack rope to hang me. Dynamic SQL debug is an unnecessary headache in that case.

[RodrigoValentim]

Aline, did you take your doubts?

[Bel-Kyor]

Good morning,
This is one of the possible ways

Select all

declare 
 
 vWhere varchar2(500) := 'FORD,MILLER'; 
 
begin 
  for xemp in ( SELECT * 
                  FROM SCOTT.EMP 
                 WHERE Instr( vWhere, Ename ) > 0 ) 
  loop 
    dbms_output.put_line( xemp.empno||' '||xemp.ename||' '||xemp.job ); 
  end loop; 
  
end;

Return:

Select all

7902 FORD ANALYST 
7934 MILLER CLERK

Embrace
Belk's