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 5, 12: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
