Monday, July 28, 2008

Conditional Spool with SQL+


There's a way to jerry-rig an if/then using the decode or case statement in SQL+.

The query shown above is the one we want to have a conditional spool option in.
  • Line 1 shows the spool file name being defined. It will be used in the query that is called.
  • Line 2 calls the query that sets up the spooling (or not)
  • Line 3-5 is the actual query.
  • Line 6 Will run a query that was generated that is either blank or says 'spool off'

Condtional_Spool.SQL:

--+------------------------------------------------------------------
--| Store all the settings so we don't loose them. To write a file to
--| be executed, the following settings need to be in place.
--+------------------------------------------------------------------
Store Set InitSets Replace
Set Echo Off Verify Off Feedback Off Newpage None Pagesize 0

Accept Spooler Prompt "Spool (N/Y): "

--+------------------------------------------------------------------
--| The New Value will set up an amper variable. Based on how they
--| answered the above question, set the spool on statement.
--+------------------------------------------------------------------
Column Spool_On New_Value Spool_On

Spool Spool_On.Sql Replace
Select Case Upper('&Spooler')
When 'Y' Then 'Spool &Output_File Replace'
Else ' '
End As Spool_On
From Dual;
Spool Off

--+------------------------------------------------------------------
--| Set up the spool off statement based on the user's response.
--| This is called from the calling program.
--+------------------------------------------------------------------
Column Spool_Off New_Value Spool_Off

Spool Spool_Off.Sql Replace
Select Case Upper('&Spooler')
When 'Y' Then 'Spool Off'
Else ' '
End As Spool_Off
From Dual;
Spool Off

--+------------------------------------------------------------------
--| Start the spooling before going back to the calling program
--+------------------------------------------------------------------
@Spool_On

--+------------------------------------------------------------------
--| Clean up: Undefine amper variables and reset settings.
--+------------------------------------------------------------------
Undefine Spooler Spool_On Spool_Off Output_File
@InitSets

No comments:

Post a Comment