Home » SQL & PL/SQL » SQL & PL/SQL » Function returning a table inside where clause (Oracle10g, Oracle PLSQLDeveloper2.1.1)
Function returning a table inside where clause [message #450178] Mon, 05 April 2010 11:14 Go to next message
asairam
Messages: 11
Registered: April 2010
Junior Member
DECLARE
cnt number(10);
BEGIN
SELECT COUNT(*) INTO CNT FROM TBL_ADDRESS WHERE ADDRESS_ZIP
IN (SELECT * FROM TABLE(MY_PACK.STR2TBL('46227')));
DBMS_OUTPUT.PUT_LINE (cnt);
END;

MY_PACK.STR2TBL() is a function which takes '|' delimited string,
extracts values and returns a table of zipcodes. The function works
fine and returns 46227 but the count returned is 0 instead of
280(count returned by replacing inner select with '46227').

Cluesless so far on whats wrong. Any ideas please? Please help.

Thanks

Report message to a moderator

Re: Function returning a table inside where clause [message #450179 is a reply to message #450178] Mon, 05 April 2010 11:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>MY_PACK.STR2TBL() is a function which takes '|' delimited string, extracts values and returns a table of zipcodes.

It function really does return TABLE; please realize that TABLE is not the same as quoted string ('46227').
Therefore WHERE fails to match and zero rows returned

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

[Updated on: Mon, 05 April 2010 11:29]

Report message to a moderator

Re: Function returning a table inside where clause [message #450190 is a reply to message #450179] Mon, 05 April 2010 12:30 Go to previous messageGo to next message
asairam
Messages: 11
Registered: April 2010
Junior Member
Thank you for the response. Thats surprising to hear. Isnt it the same as
SELECT * FROM 
TABLE1 A 
WHERE A.Col1 IN (SELECT B.Col1 FROM TABLE2 B)


If it is not the same, then do you have any idea on how to accept a delimited string like 'zip1|zip2|zip3' and convert it into a filter condition in WHERE clause?

Thanks.

Report message to a moderator

Re: Function returning a table inside where clause [message #450192 is a reply to message #450190] Mon, 05 April 2010 12:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>how to accept a delimited string like 'zip1|zip2|zip3'
BASIC design flaw!
Store only 1 value in a field.
Nobody wins when more than 1 value resides in a single column/field
Correct the design & then no problem exists to be solved.

Report message to a moderator

Re: Function returning a table inside where clause [message #450195 is a reply to message #450192] Mon, 05 April 2010 12:54 Go to previous messageGo to next message
asairam
Messages: 11
Registered: April 2010
Junior Member
Hi,
May be I was not clear in the last message. I was not talking about the delimited values being stored in a table column. Column stores only one zipcode value. I was talking about stored procedure.
I have a stored procedure which accepts a zipcode in a parameter and return all addresses in that zipcode. If it is more than one zipcode then a '|' delimited string is passed in parameter as shown earlier.
So procedure parameter value can be '46227' or '46227|46204|46253'.
So inside the procedure, I need to parse the '|' delimited values, extract zipcodes and get all the addresses in those zipcodes.
Hope I am clear now.
Thanks

Report message to a moderator

Re: Function returning a table inside where clause [message #450196 is a reply to message #450190] Mon, 05 April 2010 13:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please post complete source code for MY_PACK.STR2TBL() function

Report message to a moderator

Re: Function returning a table inside where clause [message #450197 is a reply to message #450178] Mon, 05 April 2010 13:03 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
asairam wrote on Mon, 05 April 2010 18:14
DECLARE
cnt number(10);
BEGIN
SELECT COUNT(*) INTO CNT FROM TBL_ADDRESS WHERE ADDRESS_ZIP
IN (SELECT * FROM TABLE(MY_PACK.STR2TBL('46227')));
DBMS_OUTPUT.PUT_LINE (cnt);
END;

MY_PACK.STR2TBL() is a function which takes '|' delimited string,
extracts values and returns a table of zipcodes. The function works
fine and returns 46227 but the count returned is 0 instead of
280(count returned by replacing inner select with '46227').

Cluesless so far on whats wrong. Any ideas please? Please help.

Thanks

What does that inner SELECT return?
What is the data type of MY_PACK.STR2TBL() return value?
Are you sure it returns strings without any whitespaces?

Report message to a moderator

Re: Function returning a table inside where clause [message #450199 is a reply to message #450196] Mon, 05 April 2010 13:16 Go to previous messageGo to next message
asairam
Messages: 11
Registered: April 2010
Junior Member
Source code:
CREATE OR REPLACE TYPE STRTABLETYPE IS TABLE OF VARCHAR2(255);   
/
FUNCTION STR2TBL
   	 (P_STR   IN VARCHAR2,
   	  P_DELIM IN VARCHAR2 DEFAULT '|'
         )
   RETURN STRTABLETYPE
 AS
   L_STR VARCHAR2(2000) DEFAULT P_STR || P_DELIM;
   L_N	NUMBER;
   L_DATA STRTABLETYPE := STRTABLETYPE();
 BEGIN
   LOOP
       L_N := INSTR( L_STR, P_DELIM );
       EXIT WHEN (NVL(L_N,0) = 0);
       L_DATA.EXTEND;
       L_DATA( L_DATA.COUNT ) :=  LTRIM(RTRIM(SUBSTR(L_STR,1,L_N-1)));
       L_STR := SUBSTR( L_STR, L_N+LENGTH(P_DELIM) );
   END LOOP;
   RETURN L_DATA;
 END;

Report message to a moderator

Re: Function returning a table inside where clause [message #450200 is a reply to message #450197] Mon, 05 April 2010 13:18 Go to previous messageGo to next message
asairam
Messages: 11
Registered: April 2010
Junior Member
Thanks for the response. Inner select is the one after IN.
The function returns a table of varchars. Heres the source code.

CREATE OR REPLACE TYPE STRTABLETYPE IS TABLE OF VARCHAR2(255);   
/
FUNCTION STR2TBL
   	 (P_STR   IN VARCHAR2,
   	  P_DELIM IN VARCHAR2 DEFAULT '|')
   RETURN      STRTABLETYPE
 AS
   L_STR VARCHAR2(2000) DEFAULT P_STR || P_DELIM;
   L_N	NUMBER;
   L_DATA STRTABLETYPE := STRTABLETYPE();
 BEGIN
   LOOP
       L_N := INSTR( L_STR, P_DELIM );
       EXIT WHEN (NVL(L_N,0) = 0);
       L_DATA.EXTEND;
       L_DATA( L_DATA.COUNT ) := LTRIM(RTRIM(SUBSTR(L_STR,1,L_N-1)));
       L_STR := SUBSTR( L_STR, L_N+LENGTH(P_DELIM) );
   END LOOP;
   RETURN L_DATA;
 END;

Report message to a moderator

Re: Function returning a table inside where clause [message #450201 is a reply to message #450199] Mon, 05 April 2010 13:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL SELECT knows nothing about STRTABLETYPE datatype
SQL is different than PL/SQL and you can not use PL/SQL datatype in vanilla SQL statements

Report message to a moderator

Re: Function returning a table inside where clause [message #450202 is a reply to message #450201] Mon, 05 April 2010 13:23 Go to previous messageGo to next message
asairam
Messages: 11
Registered: April 2010
Junior Member
OK. So what should I change to make it work?

Report message to a moderator

Re: Function returning a table inside where clause [message #450203 is a reply to message #450201] Mon, 05 April 2010 13:25 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
BlackSwan wrote on Mon, 05 April 2010 19:20
SQL SELECT knows nothing about STRTABLETYPE datatype


It's been created as a SQL type so that's manifestly wrong.
That should have been obvious from the fact that the select gives the wrong data rather than an error.

@asairam - what's the result of the following:
SELECT * FROM TABLE(MY_PACK.STR2TBL('46227'));

SELECT COUNT(*) 
FROM TBL_ADDRESS 
WHERE ADDRESS_ZIP IN ('46227');

Report message to a moderator

Re: Function returning a table inside where clause [message #450204 is a reply to message #450203] Mon, 05 April 2010 13:29 Go to previous messageGo to next message
asairam
Messages: 11
Registered: April 2010
Junior Member
Yes, I did observe that I was getting different results and hence this post. Now how do I rectify this?

Thanks.

Report message to a moderator

Re: Function returning a table inside where clause [message #450205 is a reply to message #450202] Mon, 05 April 2010 13:44 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
 
SQL> CREATE OR REPLACE TYPE t_in_list_tab AS TABLE OF VARCHAR2 (4000);
  2  /

Type created.

SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE FUNCTION in_list_pipe (p_in_list  IN  VARCHAR2)
  2    RETURN t_in_list_tab
  3  AS
  4    l_tab   t_in_list_tab := t_in_list_tab();
  5    l_text  VARCHAR2(32767) := p_in_list || '|';
  6    l_idx   NUMBER;
  7  BEGIN
  8    LOOP
  9      l_idx := INSTR(l_text, '|');
 10      EXIT WHEN NVL(l_idx, 0) = 0;
 11      l_tab.extend;
 12      l_tab(l_tab.last) := TRIM(SUBSTR(l_text, 1, l_idx - 1));
 13      l_text := SUBSTR(l_text, l_idx + 1);
 14    END LOOP;
 15    RETURN l_tab;
 16* END;
 17  /

Function created.

SQL> ed
Wrote file afiedt.buf

  1  SELECT *
  2  FROM   emp
  3  WHERE  job IN (SELECT * FROM TABLE(in_list_pipe('SALESMAN|MANAGER')))
  4* ORDER BY ename
SQL> /


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

7 rows selected.

SQL> ed
Wrote file afiedt.buf

  1  SELECT *
  2  FROM   emp
  3  WHERE  job IN ('SALESMAN','MANAGER')
  4* ORDER BY ename
SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

7 rows selected.

SQL>


IS this you are looking for ?


Dynamic IN List

sriram Smile

Report message to a moderator

Re: Function returning a table inside where clause [message #450206 is a reply to message #450204] Mon, 05 April 2010 13:54 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
asairam wrote on Mon, 05 April 2010 19:29
Yes, I did observe that I was getting different results and hence this post. Now how do I rectify this?


Dunno. Answering my questions might help to identify the problem.

Report message to a moderator

Re: Function returning a table inside where clause [message #450208 is a reply to message #450203] Mon, 05 April 2010 14:04 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
cookiemonster wrote on Mon, 05 April 2010 23:55
BlackSwan wrote on Mon, 05 April 2010 19:20
SQL SELECT knows nothing about STRTABLETYPE datatype




@asairam - what's the result of the following:
SELECT * FROM TABLE(MY_PACK.STR2TBL('46227'));

SELECT COUNT(*) 
FROM TBL_ADDRESS 
WHERE ADDRESS_ZIP IN ('46227');


so please execute these two and paste the output here ...
Then we will give you the answer whats wrong

sriram Smile

Report message to a moderator

Re: Function returning a table inside where clause [message #450209 is a reply to message #450208] Mon, 05 April 2010 14:11 Go to previous messageGo to next message
asairam
Messages: 11
Registered: April 2010
Junior Member
Sure. I didnt know that you were asking me the results of those queries. Sorry about that.
The first query result is :
SELECT * FROM TABLE(MY_PACK.STR2TBL('46227'));

COLUMN_VALUE                                                       
-------------------
46227


The second query result is 
SELECT COUNT(*) 
FROM TBL_ADDRESS 
WHERE ADDRESS_ZIP IN ('46227');


COUNT(*)               
---------------------- 
280


Thanks.

Report message to a moderator

Re: Function returning a table inside where clause [message #450211 is a reply to message #450209] Mon, 05 April 2010 14:14 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
 
SELECT COUNT(*) 
FROM TBL_ADDRESS 
WHERE ADDRESS_ZIP IN ('46227');


COUNT(*)               
---------------------- 
280



Then whats the value of
SELECT COUNT(*) 
FROM TBL_ADDRESS 
WHERE ADDRESS_ZIP IN (SELECT * FROM TABLE(MY_PACK.STR2TBL('46227')));
?


SQL> select count(*) from emp
  2  where deptno=10;

  COUNT(*)
----------
         3

SQL> select count(*) from emp
  2  where deptno in (select * FROM TABLE(in_list_pipe('10')));

  COUNT(*)
----------
         3

sriram Smile

[Updated on: Mon, 05 April 2010 14:16]

Report message to a moderator

Re: Function returning a table inside where clause [message #450213 is a reply to message #450211] Mon, 05 April 2010 14:17 Go to previous messageGo to next message
asairam
Messages: 11
Registered: April 2010
Junior Member
 

COUNT(*)               
---------------------- 
0



Even I created the function and type which you have given and used it. The result is 0 rows.

Thanks.

Report message to a moderator

Re: Function returning a table inside where clause [message #450216 is a reply to message #450213] Mon, 05 April 2010 14:21 Go to previous messageGo to next message
asairam
Messages: 11
Registered: April 2010
Junior Member
You can see the usage of type and function you defined and the results Sriram:

SELECT COUNT(*) FROM TBL_ADDRESS WHERE ADDRESS_ZIP IN 
 (SELECT * FROM TABLE(in_list_pipe('46227|46204')));
 
SELECT COUNT(*) FROM TBL_ADDRESS WHERE ADDRESS_ZIP IN 
 ('46227','46204');

COUNT(*)               
---------------------- 
0                      

COUNT(*)               
---------------------- 
541

Report message to a moderator

Re: Function returning a table inside where clause [message #450219 is a reply to message #450178] Mon, 05 April 2010 14:37 Go to previous messageGo to next message
asairam
Messages: 11
Registered: April 2010
Junior Member
Dear All,
I just found out the root cause. The ZIPCode column I was querying is of CHAR type whereas the table type I created has VARCHAR2 type and hence the issue. When I change the type as char

CREATE OR REPLACE TYPE STRTABLETYPE AS TABLE OF CHAR(10);


then the count has come correct.
Now this leaves two questions for me:
1. How does this work when we pass the values directly as '46227'?
2.What about the SQL and PL/SQL type issue which BlackSwan has pointed?
Quote:

SQL SELECT knows nothing about STRTABLETYPE datatype
SQL is different than PL/SQL and you can not use PL/SQL datatype in vanilla SQL statements


Thank you all for your time and patience.

Sairam

Report message to a moderator

Re: Function returning a table inside where clause [message #450223 is a reply to message #450219] Mon, 05 April 2010 15:20 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
asairam wrote on Mon, 05 April 2010 20:37

1. How does this work when we pass the values directly as '46227'?

Because '46227' can be transformed into a char(10) by adding a few spaces, but a varchar(255) can't. You're always going to have trouble when comparing a char to a varchar. I'd convert your char column to varchar if at all possible. Read up on impicit type convsersion in the documentation if you want more details.

asairam wrote on Mon, 05 April 2010 20:37

2.What about the SQL and PL/SQL type issue which BlackSwan has pointed?
Quote:

SQL SELECT knows nothing about STRTABLETYPE datatype
SQL is different than PL/SQL and you can not use PL/SQL datatype in vanilla SQL statements


There is no issue, Blackswan imagined it.

Report message to a moderator

Re: Function returning a table inside where clause [message #450240 is a reply to message #450219] Mon, 05 April 2010 21:16 Go to previous message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
What does that inner SELECT return?
What is the data type of MY_PACK.STR2TBL() return value?
Are you sure it returns strings without any whitespaces?


so its better to answer/respond to each post.

Good luck

sriram Smile

Report message to a moderator

Previous Topic: ORA-6508 Error
Next Topic: How to get Column Values in Pl/Sql Procedure (Merged 5 by VK)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Jun 17 01:00:37 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.