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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Guy Hengel [angelIII / a3]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

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
SujithData 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
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.