problem

I wrote a unix scrip which calls sql script.

the problem is the query runs well in toad but doesnt work in unix script.

anyone could help?
query as follows:

select sum(case when error_text like 'SystemError:Operation Time Out (Server Side)%' then 1 else 0 END) as count
from a
where trunc(start_dtg)=trunc(sysdate)-1;

in Unix script:

declare
cursor cs is
select sum(case when error_text like 'SystemError:Operation Time Out (Server Side)%' then 1 else 0 END) as count
from a
where trunc(start_dtg)=trunc(sysdate)-1;
blah..blah..blah

but it always said,
encountered the symbol "CASE" when expecting blah blah

who knows what the problem and how I can fix it.

Thanks.

Hi,

In ksh, You could try this code :

var=`sqlplus <user/pwd> << _EOF_
select sum(case when error_text like 'SystemError:Operation Time Out (Server Side)%' then 1 else 0 END) as count
from a
where trunc(start_dtg)=trunc(sysdate)-1;
_EOF_`

where $var is the result of your query.

I think the problem is when unix script called sql script it doesnt recognize the "CASE" in a right way as toad can do. but dont know how to fix it.
really need help.

try with a backslash maybe...

the reason I use cursor to fetch the result (not $var = blah blah) is because I want to reformat the output file and save it as .csv file.

okiii
so use a spool file and format it to cvs file

what is spool file?

in SQL, it permits to receive the result of your query in a file
for exemple :

spool toto
set heading off feedback off linesize 1000
<your query>;
spool off

I go to sleep
Bye

dont go please

did you resolve your problem ?

nah..

by the way how to format the output file with spool?

OK, process by order :
Please give me an extract of your spool file created after SQL code (to have an exemple of your datas)

can I email you

No but just write here 2~3 lines (or line structure), I explain you after how to translate to csv format this kind of line

The result comes back from query should be as following, how can I save it as .csv file?
TRAN_DATE,EVENT_CODE,EVENT_DESC,TIME_OUT_COUNT,COUNT(*)
24/08/2005 CREATECRD Initiate Credit Check 1 241
24/08/2005 CUSTSRCH Search for Customer 4 3157

Thanks, Mr Mad

Try to insert ';' directly in your SQL script :
select col1||';'||col2||';'||col3||';'.......
from mytable ......

You have <space> in your description field so it's impossible to use sed command on your spool file :o

nah..cos now I am using another way rather than spool to do it and i just copied the current output file I got

it still doesnt work, am I wrong?

in unix script I got

spool tt.csv

set ..off

sql query

spool off

where is the problem?

Sorry, I'm busy with Oracle database migration this night...
I hope that anybody will help you on your problem :o

Rgds.

Spool file couldn't be in cvs format except if you insert ';' in select like my exemple.