Current Position:Home > Issue with a procedure and IN operator...

Issue with a procedure and IN operator...

Update:11-30Source: network consolidation
Advertisement
Hi,
Data
Service_Request_id function_activity_ids
102092     2     2 -- Query works fine as v_function_activity_ids(i) = 22
102094 24,25,29 -- Query does not work and returns null as v_function_activity_ids(i) =24,25,29 is passed.
102152 23,24 -- Query does not work and returns null as v_function_activity_ids(i) =23,22 is passed.
CURSOR c_service_request_null IS
SELECT service_request_id,function_activity_ids,service_region_id      
       FROM temp_sop_service_request
       WHERE function_activity_ids IS NOT NULL AND SERVICE_REQUEST_ID IN ( '102092','102094','102152');
<all the relevant vars. declared here...>
begin
OPEN c_service_request_null;
  LOOP
    FETCH c_service_request_null
    BULK COLLECT INTO v_service_req_id,v_platform_type,v_service_country,v_service_state,
                      v_function_activity_ids,v_service_region_id
    LIMIT 500;
    EXIT WHEN v_row_count = c_service_request_null%ROWCOUNT;
    v_row_count := c_service_request_null%ROWCOUNT;
FOR i IN 1..v_service_req_id.count LOOP
        SELECT  max(decode(upper(trim(region_id)),NULL,'UR',upper(trim(region_id))))
        INTO v_region_id(i)
        FROM SOP_REGION_ACTIVITY_MAP
     WHERE
     (UPPER(TRIM(ACTIVITY_NAME)), UPPER(TRIM(SUB_ACTIVITY_NAME))) IN
               (SELECT UPPER(TRIM(ACTIVITY_NAME)), UPPER(TRIM(SUB_ACTIVITY_NAME))
                  FROM SOP_FUNCTION_ACTIVITY_MAP
                 WHERE TRIM(FUNCTION_ACTIVITY_ID) IN (TRIM(v_function_activity_ids(i)))); --> Problem Here
END LOOP;
END;Now, when variable v_function_activity_ids(i) contains string 24,25,29 the above query does not give anything..i.e. it does not find the match and hence v_region_id(i) is returned as null.
But when request contains function_activity_ids as single i.e. 25 it works and v_region_id(i) is returned correctly.
Also, If I hardcode the value as under then it works :
  SELECT  max(decode(upper(trim(region_id)),NULL,'UR',upper(trim(region_id))))
        INTO v_region_id(i)
        FROM SOP_REGION_ACTIVITY_MAP
     WHERE
     (UPPER(TRIM(ACTIVITY_NAME)), UPPER(TRIM(SUB_ACTIVITY_NAME))) IN
               (SELECT UPPER(TRIM(ACTIVITY_NAME)), UPPER(TRIM(SUB_ACTIVITY_NAME))
                  FROM SOP_FUNCTION_ACTIVITY_MAP
                 WHERE TRIM(FUNCTION_ACTIVITY_ID) IN (TRIM(24,25,29)));Can you pls help me with this..
Thx..

The Best Answer

Advertisement
Looks like You totally misunderstand the IN() clause.
In your scenario resulting query looks like
... in ( '24,25,29' ) - that's why You will never get result if two or more values comes from function.
Two solutions:
1) You can put values in the temporary table or table pl/sql and rewrite query appropriate way:
... in (select value from my_temp_table)
... in (select value from table(my_plsql_table))
2) While using 10g, You can format regexp pattern instead of 'val1,val2,val3' and use
... regexp_like(FUNCTION_ACTIVITY_ID, PATTERN)
regexp docs: http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/conditions007.htm#SQLRF00501
  • Issue with a procedure and IN operator... Update:11-30

    Hi, Data Service_Request_id function_activity_ids 102092     2     2 -- Query works fine as v_function_activity_ids(i) = 22 102094 24,25,29 -- Query does not work and returns null as v_function_activity_ids(i) =24,25,29 is passed. 102152 23,24 -- Que

  • Issue with calling procedure remotely Update:11-30

    Hello, Within the stored procedure, I am calling a procedure remotely but the '@v_remote_db' variable does not resolve: begin for i in cur_database_list loop v_remote_db := i.database_name; p_trunc_remote_[email protected]_remote_db; <<<------does not reso

  • External HD issue. Finder cannot complete the operation because some data.. Update:10-11

    Last night I had a drive throw the error "finder cannot complete the operation because some data in "<file>" could not be read or written. Error code-36". I thought it was a bad disk. Then tonight I plugged in a flash drive, form

  • How to use stored procedures in AJAX operations? Update:10-11

    Hi, First of all I hope this is the right place to post my question. I am creating an application which involves AJAX style update of a specific page. I managed to make it work via the "classical JavaScript calling an On Demand Application Process th

  • WCF OData Service stored procedure call generates "Operation could destabilize the runtime" error with $select option Update:11-30

    I've been trying to call a stored procedure through Entity Framework and WCF Data Services (OData). It returns an entity not a complex type. Following walkthroughs found all over the web, I came up with this code inside my service: [WebGet] public IQ

  • Getting NLS setting issues when calling procedure Update:11-30

    Hi, Can anybody suggest me what could the problem in the below issue?. i am invokeing procedure using DBAdapter. My soa version is 11.1.1.5. When i am calling procedure first time it is working fine. But when i am invoke second time it is showing bel

  • DateTime parameter issue with Stored Procedure CR XI Update:11-30

    Hello everyone. This error was touched on here previously but no answer provided. I am using Crystal Reports XI with SAP's BUSINESS ONE product. I have a report using a SQL Stored Procedure (SQL 2005). It has datetime parameters. The Stored Procedure

  • Looping issue in stored procedure Update:11-30

    Hi Experts, currently i am using below version : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE     11.2.0.1.0     Production TNS for 32-bit Windows: Version 11.2.0.1.0 - Production

  • Work flow issue in Guided Procedures Update:11-30

    Hi, I have one issue in work flow implemented in Guide procedures with Webdynpro java. I have webdynpro application which developed and deployed in NW 7.0 SP3 version. The particular application request has suppose to be get approved by 5 users but a

  • Issue with Stored procedure Update:11-30

    Hi, I am trying to create a stored procedure and am trying to execute it in 2 different ways.. One works and the other throws a syntax error. Can you please explain why the first one has issues and the second one works fine? Also please suggest what