SQL variable definition question 2

[DCCoolBreeze]

I want to be able to define a variable that will hold the resultant value from an expression. For example

define l_start_date = LAST_DAY(ADD_MONTHS(SYSDATE,-1))

l_start_date needs to have the resulting date calculated by the expression.

 

[sem]

Can you provide more info about your environment?

Regards, Dima

 

[DCCoolBreeze]

OK. Oracle 8i

Let me give a little more detail. I am trying to create an SQL script that will create a .CSV (comma delimited) file for import into excel. I have the select statement setup properly but now I need to write the first two lines (description of the file) of the csv. It would look something like:

REPORT X DATA
From 1/2/03 to 4/1/03
123,dsaf,3245,asdf
...

I need to fill in the From l_start_date to l_end_date

I tried define but that only sets values to character strings

define l_start_date = LAST_DAY(ADD_MONTHS(SYSDATE,-2))+16
define l_end_date = LAST_DAY(ADD_MONTHS(SYSDATE,-1))+15

From &l_start_date to &l_end_date

so how to I get the value of the expression above into a variable in plain sql.

I hope this helps

 

[stefanhei]

Hi.
Why put that into uservariables? You could just issue a select giving you the needed text before your main-query
(something like SELECT 'From '||LAST_DAY(ADD_MONTHS(SYSDATE,-1))'...' FROM dual

If do need that values in a variable use new_value clause of the column command:
column x_start_date noprint new_value start_date
select LAST_DAY(ADD_MONTHS(SYSDATE,-1)) x_start_date from dual;

Stefan

 

[sem]

You may use BIND variables instead of LEXICAL ones:

var l_start_date varchar2(10)
exec :l_start_date:=LAST_DAY(ADD_MONTHS(SYSDATE,-2))+16
...

... between :l_start_date and :l_end_date


Regards, Dima

 

[DCCoolBreeze]

yes I need to define some variables. For example when I get the output correct, I will need to spool it to a file with the name spool fileName_YYMMDD.csv where YYMMDD is the current date.

 

[sem]

In this case I'd recommend to spool your COMMAND. Something like this:

set feedback off
set serverout off
set termout off
set heading off
spool cmd.sql
select 'spool fileName_'||LAST_DAY(ADD_MONTHS(SYSDATE,-2))+16||'.csv'
/
spool off
@cmd.sql


Regards, Dima