spooling

[prodadmin]

Hello

I have two questions

1.what is the where condtion for generating last months data .I don't want to hard code month instead on the first of the month it will generate data for previous month(of course i will cron)

2.i have orders table
orderid,createddate and client_id
currently i am spooling data for each client like this
----------
spool \u01\client123.txt
select * from orders where client_id=123;
spool off
-------------
everytime we add a client or clients , it's just pain to go and edit this sql file to spool

Is there way to automatically generates data into different file based on clientid automatically?

Thanks for your time and help

[PAVB]

1 - Use sysdate
2 - Yes

[prodadmin]

hello PAVB

Do you have a sample query

Thanks

[PAVB]

Follows a template for each one of your needs...

#1 - Select Last Month Data

select  *
from    schema_name.table_name
where   to_char(your_date_column, 'YYYYMM') = to_char(sysdate - 30, 'YYYYMM')
;

#2 - Create Query on-the-fly

spool \u01\myquery.sql
set pagesize 0
set linesize 180
select  'select * from orders where client_id=' ||
        "CLIENT" ||
        ';' 
from
(        
select  distinct client_id "CLIENT"        
from    orders
);
spool off
spool \u01\client123.txt
@\u01\myquery.sql
spool off

Hope it helps.

[prodadmin]

thanks for your time .i will try these