Query-By-Example and IN lists

Currently using 11.2.0.2 but will be going to 12.1.0.2 soon.

I’m rewriting some old code that uses dynamic SQL and string concatenation.

The code is pretty much a Query-By-Example (QBE) procedure.

I'm rewriting it to NOT use dynamic SQL and concatenation.

I can do this easily as long as the parameters are single column equality/like:  
col1=some_value
or
col1 like ‘%’ || some_value || ‘%’

I’m stuck when it comes to lists of values.  Consider the list to be unbounded (up to the 1000 limitation of IN lists).  The reality is probably never more than 5 to 20.

I initially created a Global Temporary Table (GTT) thinking I could use IN with a SELET and not have to create a TYPE outside of the procedure and cast the associative array in the main SELECT.

I cannot figure out how to get everything in one select if there are no values in the lookup.

I know I can do another select to get a count of the rows in the GTT into a variable and use that variable in the main select or some other kludge.  I've included this work-around at the bottom.

The Challenge:
Single select.


Setup (the var is for my sqlplus testing):
drop table main_table purge;
create table main_table(col1 char(1), col2 number);

insert into main_table values('a',1);
insert into main_table values('a',2);
insert into main_table values('a',3);
insert into main_table values('b',4);
commit;

drop table lookup_table purge;
create global temporary table lookup_table(col2_val number) on commit delete rows;

var qbe_col1 varchar2(1);

Open in new window


Example of single value columns which works well:
exec :qbe_col1 := null;
--return all
select * from main_table
where
( :qbe_col1 is null or col1=:qbe_col1 )
/

--return only 'a'
exec :qbe_col1 := 'a';

select * from main_table
where
( :qbe_col1 is null or col1=:qbe_col1 )
/

Open in new window


Now, I need to pass in a list in addition to the single columns.

For example:  1,2

Works well if I have values:

--qbe_col1 is still 'a'
insert into lookup_table values(1);
insert into lookup_table values(2);

select * from main_table
where
( :qbe_col1 is null or col1=:qbe_col1 )
and
( col2 in (select col2_val from lookup_table) )
/

Open in new window


and I get the expected results:
a 1
a 2

This is where it fails:
commit;  -- clears out the GTT

I need ALL three 'a' rows back


Yes, I know this works:
var qbe_col2_count number

insert into lookup_table values(1);
insert into lookup_table values(2);

select count(*) into :qbe_col2_count from lookup_table;

select * from main_table
where
( :qbe_col1 is null or col1=:qbe_col1 )
and
( :qbe_col2_count = 0 or col2 in (select col2_val from lookup_table) )
/

commit;


select count(*) into :qbe_col2_count from lookup_table;

select * from main_table
where
( :qbe_col1 is null or col1=:qbe_col1 )
and
( :qbe_col2_count = 0 or col2 in (select col2_val from lookup_table) )
/

Open in new window

LVL 79
slightwv (䄆 Netminder) Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
Something like this?  basically same idea you got working except without needing an additional variable

SELECT *
  FROM main_table
 WHERE ( :qbe_col1 IS NULL OR col1 = :qbe_col1)
   AND (NOT EXISTS
            (SELECT 1
               FROM lookup_table
              WHERE ROWNUM = 1)
     OR col2 IN (SELECT col2_val
                   FROM lookup_table))
1
slightwv (䄆 Netminder) Author Commented:
>>So, you don't mind getting the B record in addition to the A records?

If qbe_col1 ='a', then I do mind if 'b' is returned.

The col1 rows are controlled with:
( :qbe_col1 is null or col1=:qbe_col1 )

I'm trying to figure out col2's rows.
0
sdstuberCommented:
You may want to reverse the OR conditions based on what is most likely to occur.  Since it's an OR, short-circuit evaluation should help so you only need to execute the first subquery if it evaluates TRUE
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
or all in one query but potentially worse if there are lots of non-col2 matching values and you have to count them all

SELECT *
  FROM main_table
 WHERE ( :qbe_col1 IS NULL OR col1 = :qbe_col1)
   AND EXISTS
           (SELECT 1
              FROM (SELECT COUNT(*) cntall, COUNT(CASE WHEN col2 = col2_val THEN 1 END) cntcol FROM lookup_table)
             WHERE cntall = 0 OR cntcol > 0);

on second though, this would only work on 12c because of 11g scope-depth restrictions
0
slightwv (䄆 Netminder) Author Commented:
>>You may want to reverse the OR conditions based on what is most likely to occur.

Have thought of that but when dealing with USERS and their ability to perform QBE:
How often WILL they provide a value for X vs how often they won't?   Can be a difficult question to guess...

I've assumed that MOST of the time they will NOT provide a value so I've tried to keep the null check on the left.

It also looks prettier in the code...  ;)
0
sdstuberCommented:
is it safe to assume col2_val will not be null?
0
slightwv (䄆 Netminder) Author Commented:
>>is it safe to assume col2_val will not be null?

Yes.
0
sdstuberCommented:
More compact, but probably still not as efficient as the two query method.

SELECT *
  FROM main_table
 WHERE ( :qbe_col1 IS NULL OR col1 = :qbe_col1)
   AND col2 =
           COALESCE((SELECT MAX(CASE WHEN col2_val = col2 THEN col2 END) FROM lookup_table), col2)
0
slightwv (䄆 Netminder) Author Commented:
>>More compact, but probably still not as efficient as the two query method.

If I insert 1 and 2 into my lookup table, I still get all 3 rows.
0
sdstuberCommented:
ah, you're right, oh well, I think the 2 query method is still best then, especially if  there is a "most common" method and you can put it first.

even if you get it wrong though I expect you'll probably still have fairly decent performance.
The

not exists (select 1 from lookup_table where rownum=1) should be pretty lightweight, so even if you have to double on on executing that in addition to the bind variable criteria it should be almost negligible
0
slightwv (䄆 Netminder) Author Commented:
I had experimented with NOT EXISTS but couldn't get it working.

I like it in that it fits with the same basic pattern as the single value:
("check the negative" or "check the positive").
0
sdstuberCommented:
one more idea,  instead of using a global temporary table,  use a collection to hold the multi-selected values

Then your code pattern will look more like the scalar value conditions.

Note, I've given 2 different "get them all" variations,  one with a NULL collection and one with an empty collection.  Neither is significantly better than the other, but depending on your code construction one might be more convenient to use.


DECLARE
    qbe_col1     main_table.col1%TYPE;
    v_collection ora_mining_number_nt;
BEGIN
    DBMS_OUTPUT.put_line('------ check populated collection ---------');
    v_collection := ora_mining_number_nt(3, 4);

    FOR x
        IN (SELECT col1, col2
              FROM main_table
             WHERE (qbe_col1 IS NULL OR col1 = qbe_col1)
               AND (v_collection IS NULL OR col2 MEMBER OF v_collection))
    LOOP
        DBMS_OUTPUT.put_line(x.col1 || ' ' || x.col2);
    END LOOP;

    DBMS_OUTPUT.put_line('------ check NULL collection ---------');
    v_collection := NULL;

    FOR x
        IN (SELECT col1, col2
              FROM main_table
             WHERE (qbe_col1 IS NULL OR col1 = qbe_col1)
               AND (v_collection IS NULL OR col2 MEMBER OF v_collection))
    LOOP
        DBMS_OUTPUT.put_line(x.col1 || ' ' || x.col2);
    END LOOP;

    DBMS_OUTPUT.put_line('------ check empty collection ---------');
    v_collection := ora_mining_number_nt();

    FOR x
        IN (SELECT col1, col2
              FROM main_table
             WHERE (qbe_col1 IS NULL OR col1 = qbe_col1)
               AND (CARDINALITY(v_collection) = 0 OR col2 MEMBER OF v_collection))
    LOOP
        DBMS_OUTPUT.put_line(x.col1 || ' ' || x.col2);
    END LOOP;

    DBMS_OUTPUT.put_line(RPAD('-', 80, '-'));
END;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Author Commented:
It is interesting and I'm playing with it now.

As always, reality is more complex.

I have additional fields and would need a record/object collection.  I'm thinking that would have to be created outside the code for the context switch from PL/SQL to the SELECT?

If not, I don't need the GTT object in the schema which is a bonus.

It's not a TON of code to rework.  once I get one part working it is several copy/paste/change column names.
0
sdstuberCommented:
>>> I'm thinking that would have to be created outside the code

yes,  create the object type, then create collection as table of that object.
and yes, the object and collection types could be in other schemas, just need to grant privileges on them if used by a different one.
0
slightwv (䄆 Netminder) Author Commented:
I ran out of time today but I think I'm over-complicating what I need by trying to be fancy.

I need 4 lists and was trying to make the GTT handle all 4 with flags to access the correct values to reduce the number of objects.

With the collection, I'm not sure I'm saving anything with the consolidation.  I can just create 4 separate ORA_MINING_VARCHAR2_NT lists instead of a manually created table of objects.
0
slightwv (䄆 Netminder) Author Commented:
I like the collection method.  I think I'll end up using that one.

One last question:
Any reason you went with "CARDINALITY(v_collection) = 0" instead of "v_collection IS EMPTY"?
0
sdstuberCommented:
>>> Any reason you went with "CARDINALITY(v_collection) = 0" instead of "v_collection IS EMPTY"?

brain fart

functionally shouldn't matter, but I think "IS EMPTY" makes more sense, since it's semantically what I'm looking for.
I even referred to it as an "empty collection" in my description, then didn't use the syntax.

 good catch
0
slightwv (䄆 Netminder) Author Commented:
lol...

I had to look up CARDINALITY.  That was a new one for me.  IS EMPTY was beside it in the text.  I just wasn't sure if there was some fringe/edge case difference.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.