1

I'm fetching value using cursor:

CURSOR Colcond
   IS
      SELECT   CONDITION
        FROM   CONDITION_TAB
       WHERE   PROCEDURE_NAME = 'CALL_VOL';

In first iteration it would fetch "SUM(CASE WHEN CALL_REF=0 THEN 1 ELSE 0 END)".

In my program:

OPEN Colcond;

FETCH Colcond INTO cond_val;

    SELECT  Appnum, customer_num,'"cond_val"'
    INTO   iappnum, icustnum,icond_val
    FROM   CALL_DETAILS WHERE APPNUM = val_appl
    AND customer_num = val_cust
    Group by APPLICATION_NUM,CUST_SGMT_NUM,DNIS;

INSERT INTO S_CALL_VOLUME VALUES   (iappnum, icustnum, SYSDATE, icond_val);

The record thRough the variable "icond_val" inserted is SUM(CASE WHEN CALL_REF=0 THEN 1 ELSE 0 END) instead of the value (10 or 20 or 50).

How to get the value instead of that Sum case statement?

Improve this question
3
  • 1
    You'll need to use dynamic SQL, since the query isn't known at compile time. Commented Apr 11, 2014 at 13:14
  • @AlexPoole As per your suggestion i tried the below dynamic cursor quer_str varchar2(2000); TYPE cur_type IS REF CURSOR; CursorDyn cur_typ; q_string:='SELECT CONDITION FROM CONDITION WHERE PROC= 'CAL_VOL' GROUP BY Appnum,colum'; OPEN CursorDyn FOR query_str; then I'm trying to fetch into a variable: FETCH into icond_val; but my proceudre doesn't compile..Getting error of " Found 'CAL_VOL', Expecting: ; -or- OR -or- AND -or- BETWEEN IN LIKE.. can you please suggest the correct way of defining the dynamic cursor in my procedure. Commented Apr 14, 2014 at 9:47
  • It worke now using dynamic cursor..sorry...Please write your comment in the answer i will accept it. Commented Apr 14, 2014 at 10:16

1 Answer 1

Reset to default
1

You need to use dynamic SQL to incorporate the value you selected from the condition_tab table into the next query. Here's an example in an anonymous block rather than a procedure:

declare
  val_appl number; -- procedure argument in your version?
  val_cust number; -- procedure argument in your version?

  query_string varchar2(2000);
  cond_val condition_tab.condition%type;
  iappnum call_details.appnum%type;
  icustnum call_details.customer_num%type;
  icond_val number;
  cursordyn sys_refcursor;
  cursor colcond is
    select condition
    from condition_tab
    where procedure_name = 'CALL_VOL';

begin
  open colcond;
  fetch colcond into cond_val;
  close colcond;

  query_string:='select appnum, customer_num, ' || cond_val || ' from call_details '
    || 'where appnum = :val_appl and customer_num = :val_cust '
    || 'group by application_num,cust_sgmt_num,dnis';

  open cursordyn for query_string using val_appl, val_cust;
  fetch cursordyn into iappnum, icustnum, icond_val;
  close cursordyn;

  insert into s_call_volume values (iappnum, icustnum, sysdate, icond_val);
end;
/

Your column names seem to be a bit inconsistent so it probably needs some tweaking.

For both cursors you're only selecting one row, so (a) they don't really need to be cursors, they can just be select into statements; and (b) the second one is selecting the two columns from the where clause which seems a bit pointless - when you use iappnum in the insert, you could just use val_app, etc. So I think you could simplify this to:

declare
  val_appl number; -- procedure argument in your version?
  val_cust number; -- procedure argument in your version?

  query_string varchar2(2000);
  cond_val condition_tab.condition%type;
  icond_val number;
begin
  select condition
  into cond_val
  from condition_tab
  where procedure_name = 'CALL_VOL';

  query_string:='select ' || cond_val || ' from call_details '
    || 'where appnum = :val_appl and customer_num = :val_cust '
    || 'group by application_num,cust_sgmt_num,dnis';

  execute immediate query_string into icond_val using val_appl, val_cust;

  insert into s_call_volume values (val_appl, val_cust, sysdate, icond_val);
end;
/

This will error if either query doesn't return exactly one row. Your cursor version will error if the condition_tab query finds no data, and will only use one 'condition' if there are multiples; and will only use the first result from the second query if there are multiples. If you're expecting multiples from either (not sure what your actual grouping is supposed to be, it looks inconsistent too) then you need to loop over the cursor, fetching repeatedly.

Hopefully this will get you started though.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I'm getting error at EXECUTE IMMEDIATE query_string USING val_appl,val_cust; saying ORA-00933: SQL command not properly ended. Could you please suggest me if anything went wrong?
@RaviSK - you can add dbms_output.put_line(query_string); to see exactly what it is trying to run. This was OK whenI tried it. Is it finding a condition - if not that would null and could cause this. Or, it's hard to tell from your question, is cond_val actually enclosed indouble-quotes?
I've added dbms_output.put_line(query_string); before the Execute Immediate statement and here is the result for the dbms_output.put_line...select SUM(CASE WHEN CALL_REF IN (0) THEN 1 ELSE 0 END) from CALLS_DET where appnum = :val_appl and customer_num = :val_cust group by application_num,cust_sgmt_num,dnis; i think the val_appl and val_cust is not being fetched. I'm fetching these values from a cusror actually, CURSOR Curappl IS SELECT APP_NUM FROM APP_LIST_NUM; CURSOR Curcust IS SELECT CUST_SGMT FROM CUST_SGMT_TAB;
@RaviSK - if the bind values are null then you'd just get no data found, not this error. This code works for me, and the output from dbms_output works for me if I run that directly in SQL*Plus. Does it work for you, or does it still error? Are you sure the error is coming from the execute immediate, not later - your insert, say?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.