PL/SQL Using and dereferencing a variable in a select statement

I am trying to use a variable in an SQL statement and I am having trouble dereferencing the variable.  I am setting the variable equal to the name of a table and want to use that variable in a SELECT statement as follows:
DECLARE
 v_table_name VARCHAR2(22);
BEGIN
IF (v_contract_date <= v_max_date_arch) THEN
        v_table_name := 't_monthly_values_arch';
        ELSE
          v_table_name := 't_monthly_values';
      END IF;
         
      SELECT  MAX(value_asof_date) into v_valuation_month
      FROM    v_table_name                             --HOW DO I DEREFENCE THIS VARIABLE IN THIS CONTEXT?
      WHERE   SUBSTR(value_asof_date,1,6) = SUBSTR(v_contract_date,1,6);
VBStudentAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>FROM    v_table_name                            

you cannot do that without dynamic sql.


DECLARE
 v_table_name VARCHAR2(22);
BEGIN
IF (v_contract_date <= v_max_date_arch) THEN
        v_table_name := 't_monthly_values_arch';
        ELSE
          v_table_name := 't_monthly_values';
      END IF; 
EXECUTE IMMEDIATE ' SELECT  MAX(value_asof_date) 
      FROM '||   v_table_name || '
   WHERE   SUBSTR(value_asof_date,1,6) = SUBSTR(v_contract_date,1,6)
 ' INTO v_valuation_month
; 
and I assume that v_contract_date is also a variable: 
 

DECLARE
 v_table_name VARCHAR2(22);
BEGIN
IF (v_contract_date <= v_max_date_arch) THEN
        v_table_name := 't_monthly_values_arch';
        ELSE
          v_table_name := 't_monthly_values';
      END IF; 
EXECUTE IMMEDIATE ' SELECT  MAX(value_asof_date) 
      FROM '||   v_table_name || '
   WHERE   SUBSTR(value_asof_date,1,6) = SUBSTR(:1,1,6)
 ' 
 USING v_contract_date
 INTO v_valuation_month
;

Open in new window

0
 
SujithConnect With a Mentor Data ArchitectCommented:
Dynamic SQL has to be avoided as long as you can. It adversely affects the performance. Especially in trasaction oriented systems.
In your case since you face only countable tables to choose from, you may chage your code as shown below.

BEGIN
.
.
 
IF (v_contract_date <= v_max_date_arch) THEN
      SELECT  MAX(value_asof_date) into v_valuation_month
      FROM    t_monthly_values_arch  
      WHERE   SUBSTR(value_asof_date,1,6) = SUBSTR(v_contract_date,1,6);
ELSE
      SELECT  MAX(value_asof_date) into v_valuation_month
      FROM    t_monthly_values
      WHERE   SUBSTR(value_asof_date,1,6) = SUBSTR(v_contract_date,1,6);
END IF;
          

Open in new window

0
 
VBStudentAuthor Commented:
Thanks for both solutions.  
0
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.

All Courses

From novice to tech pro — start learning today.