[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1143
  • Last Modified:

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);
0
VBStudent
Asked:
VBStudent
2 Solutions
 
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
 
sujith80Commented:
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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now