DEFINE businessdate = '2016-10-03';
DEFINE exportpath = 'E:\';

SET SERVEROUTPUT ON;
set head OFF;
set NEWP NONE;
SET FEEDBACK OFF;
SET TERM OFF;
SET SHOWMODE OFF;
SET VERIFY OFF;
set PAGESIZE 9000;
set LINESIZE 256;
set COLSEP ;;

col output_m new_value output_m noprint;

select
case when ('&businessdate' like '%CURRDAY%')
then ('&exportpath\' || '&property' || '_Time_Export_' || to_char(max(bussines_date), 'yyyy_mm_dd') || '.csv')
when ('&businessdate' like '%PREVDAY%')
then ('&exportpath\' || '&property' || '_Time_Export_' || to_char(max(bussines_date)-1, 'yyyy_mm_dd') || '.csv')
else ('&exportpath\' || '&property' || '_Time_Export_' || replace('&businessdate','-','_') || '.csv')
end
output_m from registration;

spool &output_m;

select
'''' || RVC.name || '''' || ';' ||
'''' || cards.zip_code || '''' || ';' ||
'''' || cards.last_name || '''' || ';' ||
'''' || cards.first_name || '''' || ';' ||
'''' || to_char(bussines_date, 'YYYY-MM-DD') || '''' || ';' ||
'''' || to_char(clockin, 'YYYY-MM-DD HH24:MI:SS') || '''' || ';' ||
'''' || to_char(clockout, 'YYYY-MM-DD HH24:MI:SS') || '''' || ';' ||
'''' || (ROUND(CLOCKOUT,'MI')- ROUND(CLOCKIN,'MI')) * 24* 60 || '''' || ';' ||
'''' || 'D' || ''''
from registration where to_char(trunc(bussines_date), 'YYYY-MM-DD') = '&businessdate'


spool off;

exit;



В таком виде работает, то есть подставляется значение даты
необходимо сделать
DEFINE businessdate='PREVIOUS_DAY'

Если businessdate='PREVIOUS_DAY' тогда результат запросы select sysdate -1 from dual должен подставляться в значение businessdate

Elic,
ТО есть добавить вот такой блок?


col output_m new_value output_m noprint;

select
case when ('&businessdate' like '%PREVIOUS_DAY%')
then select sysdate-1 from dual
end
output_m from registration;

spool &output_m;

Или прописать нужно внутрь 1-го ?
col output_m new_value output_m noprint;

select
case when ('&businessdate' like '%CURRDAY%')
then ('&exportpath\' || '&property' || '_Time_Export_' || to_char(max(bussines_date), 'yyyy_mm_dd') || '.csv')

when ('&businessdate' like '%PREVDAY%')
then ('&exportpath\' || '&property' || '_Time_Export_' || to_char(max(bussines_date)-1, 'yyyy_mm_dd') || '.csv')
and &businessdate=select to_char(max(bussines_date)-1, 'yyyy_mm_dd')
else ('&exportpath\' || '&property' || '_Time_Export_' || replace('&businessdate','-','_') || '.csv')
end
output_m from registration;

spool &output_m;

Добавил, но не работает :(

ВСЕМ спасибо заработало)))

col businessdate new_value businessdate noprint;

select case
when upper('&businessdate') = 'PREVIOUS_DAY' then to_char(trunc(max(bussines_date)-1), 'YYYY-MM-DD')
else '&businessdate'
end as businessdate
from registration;