Using a variable as a tablename in from statement 1

[daddypost]

Okay, I'm new to writing in oracle, only VBA and SQL. Its normally pretty easy to define a variable and use it as a table to do a select from. I'm writing a procedure that will be used daily for 30 days, using a select from a table called nc_daily_new_customers. Once the month ends, I want it to select from a table called nc_monthly_new_customers. The next day it will then begin from nc_daily_new_customers again....
This is what I had:
create or replace procedure nc_new_customer_analysis(v_new_customer in varchar(20))
AS
begin
..
....
......
select blah
from &v_new_customer
where blah....


initially there is another procedure that checks the date to see if it is the first of the month. If it is, it passes the monthly table to select from, if not the default should be the daily table. Which leads to second question. I have the following to check for 1st of month, but it won't let me compile the procedure.
create or replace procedure "nc_start_new_customer_process"

is


begin

--****repopulate dupl_id's table

execute immediate 'cust_dupl_id';


--***********************************************************************************************
--Procedure to get daily new customers
--***********************************************************************************************
execute immediate 'nc_get_new_customers';
execute immediate 'nc_new_customer_analysis'
execute immediate 'nc_daily_stats';
execute immediate 'nc_archive_daily';


--***********************************************************************************************
--If the date is the first of the month,run daily_analysis, then run monthly analysis.
--***********************************************************************************************


case sysdate
when trunc(sysdate) then
execute immediate 'nc_monthly_new_customers',
execute immediate 'nc_new_customer_analysis(nc_monthly_new_customers)'
end;


I tried to use the search function here to find an answer, but couldn't find one. I apologize if this question has been asked before. But any help you can give would be extremely appreciated.

 

[engineer2100]

Daddy,

to call a procedure you need not have execute immediate; you can directly call the procedure by its name and pass parameters

Meanhile...

case sysdate
     when trunc(sysdate) then
     execute immediate 'nc_monthly_new_customers',
     execute immediate 'nc_new_customer_analysis(nc_monthly_new_customers)'
end;

This code snippet below will execute the monthly procedure when it is the FIRST day of the month otherwise it will execute the daily procedure

IF (EXTRACT(DAY FROM SYSDATE)=1) THEN
   nc_new_customer_analysis('nc_monthly_new_customers');
ELSE
   nc_new_customer_analysis('nc_daily_new_customers');
END IF;

Also your query

select blah
from &v_new_customer
where blah....

is wrong... Use as below

execute immediate 'select blah from :1 where blah' using v_new_customer;

i have one more question to ask about the WHERE clause. Are the columns same for daily and monthly tables? If no you might build the WHERE clause also.

Another thing is i hope you are aware that unlike SQL Server, you have to bind the output ['blah' in your case after Select] into local variables of your procedure. You cannot simply write

 select blah from table where blah;

Let me know if this helps.

Engi

 

[engineer2100]

one small thing i forgot to include

execute immediate 'select blah from :1 where blah' using v_new_customer into col1, col2,...;

 

[Beilstwh]

An easier way would be to do the following in your procedure

select col1,col2,col3
FROM nc_monthly_new_customers
WHERE TRUNC(SYSDATE,'MONTH') = TRUNC(SYSDATE)
UNION ALL
SELECT COL1,COL2,COL3
from nc_daily_new_customers
where TRUNC(SYSDATE,'MONTH') <> TRUNC(SYSDATE);



Bill
Oracle DBA/Developer
New York State, USA

 

[engineer2100]

Perfect Bill!!. I was more oriented towards his procedural requirements Assumed that there might some proprietary code!!

 

[daddypost]

Thank you for your help. It worked perfectly. I'm used to writing straight sql code, which I'm finding is a little different, especially with parameters and variable definitions. But again, thank you.