It returns a table. I found the issue to be mismatch in datatypes(char and varchar) between the table column type returned from the function MY_PACK.STR2TBL and ADDRESS_ZIP column. Once I recreated the function with return of char type, then the count was correct. Thanks for your time and response.
Sairam On Apr 6, 5:19 am, sonty <[email protected]> wrote: > Hi, > > Your problem can be easily solved by dynamic pl/sql as your final > query returns only one value(count). > But I am not sure if your function "MY_PACK.STR2TBL('46227')" is > returning a table or just a single value. > > -Sonty > > On Apr 5, 9:31 pm, Michael Moore <[email protected]> wrote: > > > Basically, the WHERE clause is evaluated first, If it is TRUE, then the > > outer SELECT pulls every record from DUAL. If it is FALSE, the the outer > > SELECT pulls no-records from DUAL. > > > In this case the WHERE clause is true because 'x' IS in ('x','x'). > > > If DUAL had 2 records in it, then your count would be 2. > > Mike > > > On Mon, Apr 5, 2010 at 9:22 AM, Michael Moore > > <[email protected]>wrote: > > > > Try this, > > > If you understand this, then you understand your problem. > > > > SELECT COUNT( * ) > > > FROM DUAL > > > WHERE dummy IN( SELECT * > > > FROM DUAL > > > UNION ALL > > > SELECT * > > > FROM DUAL ); > > > > On Mon, Apr 5, 2010 at 8:42 AM, Sairam <[email protected]> wrote: > > > >> DECLARE > > >> cnt number(10); > > >> BEGIN > > >> SELECT COUNT(*) INTO CNT 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 directly running the query with 46227 in where > > >> clause). > > > >> Cluesless so far on whats wrong. Any ideas please? Please help. > > > >> Thanks > > > >> -- > > >> You received this message because you are subscribed to the Google > > >> Groups "Oracle PL/SQL" group. > > >> To post to this group, send email to [email protected] > > >> To unsubscribe from this group, send email to > > >> [email protected] > > >> For more options, visit this group at > > >>http://groups.google.com/group/Oracle-PLSQL?hl=en > > > >> To unsubscribe, reply using "remove me" as the subject. > > -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
