Home » SQL & PL/SQL » SQL & PL/SQL » generic function for contains
generic function for contains [message #433679] Thu, 03 December 2009 11:45 Go to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
Is there any generic function to check if
the sub query which returns multiple records has in one of the records a column value which user passes , its more like asking a collection of names if it contains a particular name ?
Re: generic function for contains [message #433682 is a reply to message #433679] Thu, 03 December 2009 11:53 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
>Is there any generic function to check if the sub query which returns multiple records has in one of the records a column value which user passes
If it does not throw any error, then only 1 row returned.

In my opinion, application is poorly designed/implemented if sometimes 1 row returned & at other times more than 1 returned.

If you wish to compound the design problem, you could simply issue the subselect to return COUNT(*) & then branch accordingly.
Re: generic function for contains [message #433687 is a reply to message #433682] Thu, 03 December 2009 12:17 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
BlackSwan wrote on Thu, 03 December 2009 17:53
>Is there any generic function to check if the sub query which returns multiple records has in one of the records a column value which user passes
If it does not throw any error, then only 1 row returned.

In my opinion, application is poorly designed/implemented if sometimes 1 row returned & at other times more than 1 returned.


Not if you're using IN.

@miroconnect@yahoo.com - can you give an example of what you are trying to achieve here?
Re: generic function for contains [message #433692 is a reply to message #433687] Thu, 03 December 2009 12:27 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
>Not if you're using IN.
IN is not appropriate when used in UPDATE .... SET .... (SELECT
Re: generic function for contains [message #433698 is a reply to message #433692] Thu, 03 December 2009 12:50 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
and sys_audit_id in (
select a.sys_audit_id
from ea_audit_general a, ea_finding b
where a.sys_audit_id = b.sys_audit_id
{findings_filter}


this is a part of my query
I have Ea_audit_general and it has many Ea_finding

my search results page displays everything from ea_audit_general and nothing from ea_finding
but the search has a field amt which is in ea_finding if user enters a value for amt then the query should filter ea_audit_general which have ea_finding.amt (user given value).
also I use pagination so If I join with ea_finding my total records counts get distrubed ,please tell me if there is any function which can help me
Re: generic function for contains [message #433702 is a reply to message #433698] Thu, 03 December 2009 13:05 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
In general my problem is If I join to A one to B many association

sql will return the same A's based on number of b's , I want to avoid , this becasue We use pagination and total records count gets wrong , so to avoid this I need some functions which can help me , please tell me what is available
Re: generic function for contains [message #433703 is a reply to message #433702] Thu, 03 December 2009 13:06 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: generic function for contains [message #433707 is a reply to message #433703] Thu, 03 December 2009 14:05 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
I cannot give all that u requested , I will try to explain again



I have table A and table B

B has FK to A PK
and Many B's can have the same A


I have a query, for table A and one of the filter condition is on a column of B so to fulfill this search I have to join A to B and becasue of this join the result will contain same A's several times right?

I want to avoid this , so I cannot join A to B but do some other trick like use A in (some sub_query ) or something like this ,
this will work but this will not if I have to do the same approach for other table like B called C it will not work.

I am asking if there is any oracle provided sql function which
can help me do such filters ?


icon6.gif  Re: generic function for contains [message #433709 is a reply to message #433703] Thu, 03 December 2009 14:34 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Your requirement is uncomprehensible as stated, but I hear en echo:

BlackSwan wrote on Thu, 03 December 2009 14:06
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.


Mad
Re: generic function for contains [message #433710 is a reply to message #433707] Thu, 03 December 2009 14:47 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
>I am asking if there is any oracle provided sql function which can help me do such filters ?

We don't know what is actual input to this mythical/magical function.
We don't know exactly what you expect/desire as output from function.

Please keep in mind that a function returns a single value.

We need DETAILS if you are to get more specific responses.
Re: generic function for contains [message #433712 is a reply to message #433710] Thu, 03 December 2009 15:09 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
I will try again

Table Dept

Table Employee




one dept will have several employees

I want to find all depts which have employees first name like Mike

select * from dept a

where dept.contains(select first_name from employee e where e.dept_id=a.dept_id and e.first_name='mike' )


so if the subquery in contains returns values then the dept_id should be in resultset or not

hope this makes it little clear





[Updated on: Thu, 03 December 2009 15:14]

Report message to a moderator

Re: generic function for contains [message #433714 is a reply to message #433712] Thu, 03 December 2009 15:38 Go to previous message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
I am sorry for all this I remembered exsists, that will work for me .
Thanks
Previous Topic: problem with SET-Operator on COLLECTIONS
Next Topic: REGEXP_LIKE for ?
Goto Forum:
  


Current Time: Sat Oct 01 09:14:26 CDT 2016

Total time taken to generate the page: 0.12514 seconds