How do I compare multiple records?

[jadhavrajesh]

Hi,

I have a table that looks like,

RULE_ID ATT
---------- ----------
1 test1
2 test1
1 test1
2 test2
3 test2

Is it possible to write a query like,

select ATT from table
where RULE_ID "matches all" ( 1,2,3)

Here if I query with 1,2,3 my expected output is test1, and if I query with 1,2 my expected output is test2.

Because the query "array" matched all the possible RULE_IDs for that ATT.

Thanks in advance,

Rajesh

 

[jadhavrajesh]

Hi,

Sorry I messed up the data, here is the correct version,

RULE_ID ATT
---------- ----------
1 test1
2 test1
3 test1
1 test2
2 test2

Regards,

Rajesh

 

[SantaMufasa]

Rajesh,

Could you please amend your sample data to also show a case where 1 or more rows would not display? Also, could you please show, for your sample table-data set, what the actual output-result set would look like?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[jadhavrajesh]

Hi Mufasa,

Thank you for looking into this.

If I query this table for an array of rule IDs that do not match, then I do not want to display any records.

e.g. if I pass 1,2,3,4 as set of my rule_ids, there is no ATT (attribute) that has records matching all the rules.

Another example is, if I pass only "1" as set of my rules, I do not have any ATT that has only 1 as it's rule ID.

I hope this makes my problem definition clear.

Thanks again,

Rajesh

 

[SantaMufasa]

Thanks, Rajesh, I understand the logic at this point. Now, when you say...

...if I pass 1,2,3,4 as set of my rule_ids...

...how will you "pass" as set of rule_ids...as:


1) a defined replacement string (e.g., "&my_rules") in a SQL-only command or in a PL/SQL anonymous block, or

2) an argument in a PL/SQL procedure.

And if in PL/SQL code, how do you plan to "return"/display the results set?

Your responses will focus the solution that I post.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[jadhavrajesh]

I will be calling this from JDBC, so most likely I will be calling a PL/SQL if simple SQL is not possible.


Thanks,

Rajesh

 

[SantaMufasa]

Sorry, Rajesh, another couple of questions:

Are the "set of rule_ids" that you will be passing in:

1) assured to be in ascending sort order?

2) always have precisely one comma separating the multiple values (i.e., no extraneous blanks or other punctuation)?

3) always integer values?


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[jadhavrajesh]

No problem.

I am still designing/developing the application, so I can make all these 3 things happen.

I saw this link at Oracle.com

http://www.oracle.com/technology/oramag/code/tips2004/050304.html

that I was thinking of using to create a view and then query against that.

Let me know your suggestion too.

Thanks,

Rajesh

 

[SantaMufasa]

Rajesh,

(Sorry...I had an appointment to go to that kept me from responding sooner.)

If you can assure the above 3 restrictions, then following is a solution for you. If your incoming "rule set" doesn't comply with the above 3 restrictions, then you must write a user-defined function to cause the incoming "rule set" to behave in an orderly fashion.

Section 1 -- Sample data:

select * from rajesh;

RULE_ID ATT
------- -----
      1 test1
      2 test1
      3 test1
      1 test2
      2 test2

Section 2 -- User-defined function to string together rule_ids from the table for a specific ATT:

create or replace function stringer (att_in varchar2) return varchar2 is
    hold_str varchar2(100);
    delim    varchar2(1);
begin
    for x in (select rule_id
                from rajesh
               where upper(att)=upper(att_in)
               order by rule_id) loop
        hold_str := hold_str||delim||x.rule_id;
        delim    := ',';
    end loop;
    return hold_str;
end;
/

Function created.

Section 3 -- Sample query code (stored in a script named "tt_324.sql") that invokes the "stringer" function:

set verify off
accept rule_set prompt "Enter the rule_id set to locate (a,b,c,...): "
select distinct att
  from rajesh
 where '&rule_set' = stringer(att);
prompt

Section 4 -- Sample invocations of "tt_324.sql" (Note: since this script contains an "ACCEPT...PROMPT" SQL*Plus command pair, you must run the code from a script; copy and paste will not behave satisfactorily. If your application formulates the rule_id set by some other method, then the script invocation could be optional.):

SQL> @tt_324
Enter the rule_id set to locate (a,b,c,...): 1,2,3

ATT
----------
test1

SQL> @tt_324
Enter the rule_id set to locate (a,b,c,...): 1,2

ATT
----------
test2

SQL> @tt_324
Enter the rule_id set to locate (a,b,c,...): 1,2,3,4

SQL>

Let us know if this satisfactorily resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[jadhavrajesh]

Great. That is exactly what I was looking for.

Thanks a lot,

Rajesh

 

[LKBrwnDBA]

What about just:

select ATT from table
where RULE_ID IN ALL ( 1,2,3);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

LK,

I'm eager to learn how we can get your construct to work. What am I missing (besides "an expression")?:

select ATT from rajesh
 where RULE_ID IN ALL ( 1,2,3);

 where RULE_ID IN ALL ( 1,2,3)
                  *
ERROR at line 2:
ORA-00936: missing expression

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[ddiamond]

I just look up the all operator in my sql manual. I believe the correct syntax would be:

select ATT from rajesh
 where RULE_ID = ALL ( 1,2,3);

I don't think this will do what you want, however.

 

[ddiamond]

Would the following work:

select ATT
from (
  select ATT, RULE_ID 
  from rajesh
  where RULE_ID IN (1,2,3)
  group by ATT, RULE_ID) a
Group by ATT
Having Count(Rule_ID)=3;

You'd have to dynamically build the (1,2,3), however.

 

[SantaMufasa]

I do get results with your code, DD, but not only would one need to dynamically build the "1,2,3" (which we would expect anyway), but one would need to "contrive" the "HAVING ACOUNT(RULE_ID)=3" to avoid getting "test2" to print out...what happens if "test2" also has 3 rows?...both "test1" and "test2" display. But possibly you and LK are on to an idea that we can make work. I'll keep thinking about it if you folks will as well.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[ddiamond]

If test2 also has 3 rows, than it means it has rule id's 1,2, and 3, so we would want it to appear in the output. The group by on the inner query should eliminate duplicates that would throw off the count. You would still have to build it dynamically, however, in which case this solution may not be any simpler than your original one. If you had the user also enter the number of id's involved, that would simplify the task of building my having clause.

Rajesh,
What language are you coding the frontend application in?

 

[LKBrwnDBA]

Ooops, sorry for the typo, I should have consulted the manual instead of posting from memory.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb