decode, accept, and select 3

[ASmee]

I want to execute a external SQL script, i.e. @sript.sql, dependent on the answer to my accept prompt, which I am determining by decode.

accept xanother promt 'Again, Yes/No: '

select decode (&xanother, 'YES',@script) from dual

Is this the best way? If so, what's wrong with it?

 

[SantaMufasa]

ASmee,

Frankly, I use the same technique on a script I that I usually execute iteratively. I presume you are spooling the results of your SELECT to some sort of "file.sql", which you execute as part of your main script, right?

The problem you will find doing it this way is that by answering "Yes" enough times to the prompt, you will eventually encounter this error:
"SP2-0309: SQL*Plus command procedures may only be nested to a depth of 20."

Cheers,

Dave
Sandy, Utah, USA @ 09:58 GMT< 02:58 Mountain Time.

 

[ASmee]

I am running this interactively, this is the error I get on the first run.

Another query, Yes/No: yes
SELECT DECODE (YES,'YES',@ANOTHER) FROM DUAL
*
ERROR at line 1:
ORA-00936: missing expression

 

[SantaMufasa]

ASmee,

Your code should read:

SELECT DECODE (upper('&xanother'),'YES','@ANOTHER') FROM DUAL;

Let us know what you discover,

Dave
Sandy, Utah, USA @ 10:10 GMT, 03:11 Mountain Time

 

[ASmee]

I still get the same error message. Here are my scripts:

A_TEST.SQL:

set sqlcase upper
set verify off

column foo new_value foo

accept xanother prompt 'Another query, Yes/No: '

select decode (upper('&xanother'),'YES',@ANOTHER.sql) from dual;

ANOTHER.SQL:

column closetoday heading 'Close|Today' format 999.90
column Closeyesterday heading 'Close|Yest.' format 999.90
column volume format 999,999,999
column company format a20

set verify off
set echo off
set sqlcase upper


accept xcompany prompt 'Enter Compnay Name: '

select company, closeyesterday, closetoday, volume from stock where company = '&xCompany';

 

[SantaMufasa]

ASmee,

The reason you get the same error is because you didn't fix the script like I suggested yet:

SELECT DECODE (upper('&xanother'),'YES','@ANOTHER') FROM DUAL;

Your code:

select decode (upper('&xanother'),'YES',@ANOTHER.sql) from dual;

As the song goes on Sesame Street, &quot;...Which of these things is not like the other?...&quot; Notice the single quotes around &quot; '@ANOTHER' &quot; in my code? Notice the quotes are missing from your script?

Try again with quotes and let us know. I haven't &quot;desk checked&quot; the rest of your code, but at least we'll make it past that error.

Dave
Sandy, Utah, USA @ 10:31 GMT, 03:31 Mountain Time

 

[ASmee]

Ha, I apologise, I missed your '. With the ' added the result is interesting, I have switched verify on to give more detail:

Another query, Yes/No: yes

old 1: select decode (upper'&xanother'),'YES','@ANOTHER.sql') from dual
new 1: SELECT DECODE (UPPER('YES'),'YES','@ANOTHER.SQL') FROM DUAL


DECODE(UPPER
------------
@ANOTHER.SQL

1 row selected.

 

[SantaMufasa]

ASmee,

Now that I look closer at your code, I believe you need to make some logic changes. Here are my change suggestions:

A_TEST.SQL:

set sqlcase upper
set verify off
set pagesize 0
set echo off
set trimspool on
column foo new_value foo
accept xanother prompt 'Another query, Yes/No: '
spool temp.sql
select decode (upper('&xanother')
,'YES','@ANOTHER.sql'
,'SELECT ''*** Done ***'' from dual;') from dual;
spool off
@temp.sql

ANOTHER.SQL:

column closetoday heading 'Close|Today' format 999.90
column Closeyesterday heading 'Close|Yest.' format 999.90
column volume format 999,999,999
column company format a20
set verify off
set echo off
set sqlcase upper
accept xcompany prompt 'Enter Company Name: '
select company, closeyesterday, closetoday, volume from stock where company = '&xCompany';
@A_TEST.SQL

Try this code and advise.

Dave
Sandy, Utah, USA @ 10:43 GMT, 03:43 Mountain Time

 

[ASmee]

PERFECT!!!! Thank you very much.

 

[sem]

You may also avoid spooling at all (in some cases file permissions may be an issue):

column a new_value cmd

accept xanother prompt &quot;Again, Yes/No: &quot;
select decode (upper('&xanother'), 'YES','script_yes','script_no') a from dual;
@&cmd

In this case you have 2 (or multiple) scripts for each xanother value. Instead of spooling into file you fetch the last value of a column into lexical variable cmd by defining new_value column property.

Regards, Dima