2

I have a script that contains many stand alone blocks of SQL. I employ a substitution variable in the WHERE clause of each SQL block.

For Example:

DEFINE regionValue="Townsville Region"  --yes, there is a space in the attribute value.

Select ...
From ...
WHERE region = '&&regionValue';

[this is a simplified representation of the queries]

and this is repeated for each different query so one substitution variable is used many times.

I want to spool each query output to a seperate file. I would like to use the substitution variable in the filename. I thought this would be easy, alas I was mistaken.

The outcome I want is:

SPOOL c:\reports\Townsville Region_LEAVE.csv

and 

Spool c:\reports\Townsville Region_OVERTIME.csv

and there are more.

My Question: How can I concatenate the substitution variable to form part of the filename?

WoMo
  • 7,136
  • 2
  • 29
  • 36
Tony
  • 59
  • 1
  • 5

2 Answers2

3

Surround the filename with quotes, e.g.:

SPOOL "c:\reports\&&regionValue._LEAVE.csv"
Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
  • Thanks for your suggestion, I tried it without success. What happens when I try it is I am presented with the following prompt: `Enter value for regionvalue_leave: ` – Tony Oct 29 '13 at 21:22
  • Enter value for regionvalue_leave: – Tony Oct 29 '13 at 21:25
  • 1
    sorry about that, I think you need to add a "." in there, see my edit; if I get time I'll check if this is correct or not – Jeffrey Kemp Oct 30 '13 at 00:47
0

In addition to the answer given, to prevent the prompt for entering a value, use:

SET VERIFY OFF
Rob Heusdens
  • 155
  • 5
  • This will not achieve what you think it does. This will prevent the output window from showing you where the substitution was made. If you have not defined the substitution variable, you will still receive a prompt to provide one. – SandPiper Nov 17 '17 at 15:12