SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using multiple values in a where clause, for values only known at runtime

470360Jun 11 2009 — edited Jun 16 2009

Dear all

I am creating a PL/SQL program which returns multiple rows of data but only where it meets a set id values that a user has previously chosen. The id values are stored in an associative array and are chosen by a user in the preceding procedure at run time.

I know all the table and column names in advance. The only things I don't know are the exact number of ids selected from the id column and what their values will be. This will only be known at runtime. When the procedure is run by the user it prints multiple rows of data to a web browser.

I have been reading the following posting, which I understand to a large extent, 2452596 But I cannot seem to figure out how I would apply it to my work as I am dealing with multiple rows and a cursor.

The code as I have currently written it is wrong because I get an error not found message in my web browser. I think the var_user_chosen_map_list_ids in the for cursor loop could be the problem. I am using the variable_user_chosen_map_list_ids to store all the id values from my associatative array as a string. Which I modified from the code that vidyadhars posted in the other thread.

Should I be creating a OPEN FOR ref cursor and if so where would I put my associative array into it? At the moment I take the value, turning it into a string and IN part in the WHERE clause holds the string, allowing the WHERE clause to read all the values from it. I would expect the where clause to read everything in the string as 1 complete string of VARCHAR2 data but this would not be the case if this part of the code at least was correct. The code is as follows:

--Global variable section contains:
var_user_chosen_map_list_ids VARCHAR2(32767);
......
PROCEDURE PROCMAPSEARCH (par_user_chosen_map_list_ids PKG_ARR_MAPS.ARR_MAP_LIST)
IS
CURSOR cur_map_search (par_user_chosen_map_list_ids IN NUMBER)
IS
SELECT MI.map_date
       MT.map_title,
FROM map_info MI,
     map_title MT,
WHERE MI.map_title_id = MT.map_title_id
AND MI.map_publication_id IN 
 (var_user_chosen_map_list_ids);
var_map_list_to_compare VARCHAR2(32767) := '';
var_exe_imm_map VARCHAR2(32767);
BEGIN
FOR rec_user_chosen_map_list_ids IN 1 .. par_user_chosen_map_list_ids.count
LOOP
   var_user_chosen_map_list_ids := var_user_chosen_map_list_ids || 
   '''' || 
   par_user_chosen_map_list_ids(rec_user_chosen_map_list_ids) || 
   ''',' ;
END LOOP;
 var_user_chosen_map_list_ids := substr(var_user_chosen_map_list_ids,
                                        1, 
                                        length(var_user_chosen_map_list_ids)-1);
var_exe_imm_map := 'FOR rec_search_entered_details IN cur_map_search
LOOP
htp.print('Map date: ' || cur_map_search.map_date || ' Map title: ' || cur_map_search.map_title)
END LOOP;';
END PROCMAPSEARCH;

EXECUTE IMMEDIATE var_exe_imm_map;
I would be grateful of any comments or advice.

Kind regards

Tim

Rama0830

Based on some help in my previous post, I have used the below SQL. But not able to figure out logic when I have multiple keywords and then picking the first keyword.
https://community.oracle.com/tech/developers/discussion/4484475/sql-logic-for-using-like-condition-based-on-column-values-of-another-table

SELECT   *
FROM     master_table    m
JOIN     reference_table r  ON LOWER (m.location) LIKE '%' || LOWER (r.type) || '%'
ORDER BY m.name, r.type

-Rama

Frank Kulash

Hi, @rama0830
But here I have a complex scenario, if there are multiple keywords under the Location column then we should pick only the first keyword and should map it to the Reference table Type column.
For example (from the screenshot below):-
For David, the value in the Location column is **Hallway utility (**two keywords wrt Reference table). So it should first check the first keyword which is Hallway and then map it to the Reference table to give output as Hallway, Stairs, Lobby.
...
INSERT INTO MASTER_TABLE VALUES ('John','005 3rd Floor Hallway');
INSERT INTO MASTER_TABLE VALUES ('David','Hallway utility');
Okay, for 'David', the first word in the location column is 'Hallway', so you want to ignore 'utility' when matching 'David'.
For 'John', the first word in the location column is '005'. Does that mean you want to ignore '3rd', 'Floor' and 'Hallway'?

Frank Kulash

Hi, Rama0830
Do you mean when two or more rows from reference_table match the same row from master_table, then you only want the one that matched earlier in location? For example, for 'John', location = 'Hallway utility'. This matches two rows in reference_table
type = 'Hallway' (found at position 1 in 'Hallway utility', and
type = 'utility' (found at position 9 of 'Hallway utility'
So, in this case, you only want the result set to include the match from position 1. Is that right?

Here's one way to do that:

WITH  all_matches  AS
(
  SELECT  m.*, r.*
  ,	  ROW_NUMBER () OVER ( PARTITION BY m.name
			       ORDER BY	     INSTR ( LOWER (m.location)
  	  		   	  	 	   , LOWER (r.type)
					 	   )
  	  	    	      ) AS rn
  FROM  master_table   m
  JOIN  reference_table r ON INSTR ( LOWER (m.location)
  	  		   	   , LOWER (r.type)
				   ) > 0
)
SELECT   name, location, category, type
FROM	 all_matches
WHERE	 rn = 1
;

Notice this uses INSTR instead of LIKE. Whenever you're tempted to use LIKE, also consider INSTR; sometimes it can do the same job, and do it more efficiently.

User_H3J7U

select *
from master_table mt
outer apply (
 select type, category
 from reference_table rt
 where    instr(' '||translate(mt.location,',',' ')||' ' collate binary_ci, ' '||rt.type||' ') > 0
 order by instr(' '||translate(mt.location,',',' ')||' ' collate binary_ci, ' '||rt.type||' ')
 fetch first 1 row only
);

NAME       LOCATION                TYPE       CATEGORY                       
John       005 3rd Floor Hallway   Hallway    Hallway, Stairs, Lobby          
David      Hallway utility         Hallway    Hallway, Stairs, Lobby          
Ashok      playroom                playroom   Community                       
Kyle       playroom bathroom       playroom   Community                       
Srikanth   Outdoor activity        outdoor    Exterior                        
Pavan      Utility Outdoor         utility    Basement, mechanical, storage   
Mike       Other                   other      Other

1 - 4

Locked Post

New comments cannot be posted to this locked post.

Locked on Jul 14 2009

Added on Jun 11 2009

13 comments

1,378 views

SQL & PL/SQL

Using multiple values in a where clause, for values only known at runtime

Comments

Post Details