In SQL Plus can I condition? 1

[MCubitt]

I found I was unable to use "if" because I get an error.

Is it possible to use "IF" withing standard SQL run within SQLPLus?


There's no need for sarcastic replies, we've not all been this sad for that long!

 

[sem]

No, IF is unknown term for sql*plus. You may dynamically evaluate script name to execute

column a new_value cmd
select decode(option,'yes','@file1','no','@file2','exit') a from dual;
@&&cmd

Regards, Dima

http://seminihin.narod.ru

 

[MCubitt]

Sem,

Thanks.. in my case it won't work because I only want to run the statement if a condition is met. Useful to know, though.




There's no need for sarcastic replies, we've not all been this sad for that long!

 

[johnherman]

SQL Plus can run PL/SQL scripts (and hence IF statements), but you need to let it know by setting up BEGIN and END blocks.

-------------------------
John Herman is available for short and long term data warehousing consulting and contracts.

 

[MCubitt]

John,

Thanks.. around ALL the code or only the code that uses the IF statement?

Does the BEGIN/END restrict use of variable outside of those blocks?

EG.

Defines
Prompts

begin
if value=1 then
run sql 1
else
run sql 2
endif
end


Thank you,



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[sem]

I can not understand why you rejected my code. You may create a file containing all stuff you're going to execute if YES, and another one containing single command EXIT;
If your condition is evaluated to YES, then call the first one, NO - the second.

Regards, Dima

http://seminihin.narod.ru

 

[MCubitt]

Sem,

Perhaps a more simple example would help me.

CopyToUser will be data or blank. If blank, the user did not want a copy, so should not execute the 2nd part:

spool from.csv;
select column1, column2, column3 from table where condition;
spool off;

spool to.csv
select column1, column2, " " form table where condition;
spool off;

So I want
spool from.csv;
select column1, column2, column3 from table where condition;
spool off;

if copytouser then
spool to.csv
select column1, column2, " " form table where condition;
spool off;
endif



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[sem]

Create file exitfile.sql with single command

exit;

followed by carriage return

Create file contfile.sql and place "optional part" to it

  spool to.csv
  select column1, column2, " " form table 
  spool off

In your main script write:

spool from.csv;
select column1, column2, column3 from table where condition;
spool off;

column a new_value cmd
select decode(copytouser, null, 'exitfile', contfile) a from dual;
@&&cmd

Regards, Dima

http://seminihin.narod.ru

 

[LKBrwnDBA]

So, you follow sem's instructions:

1) create a file 'copyto.sql' with following instructions:
spool to.csv
select column1, column2, " " form table where condition;
spool off;
2) create a file 'do_nothing.sql' with following instruction:
-- nothing --

3) your script will look like sem's example:

spool from.csv;
select column1, column2, column3 from table where condition;
spool off;

column a new_value cmd
select decode('&copytouser', NULL, 'do_nothing','copyto') a
  from dual;
@&&cmd

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Ooops. sem beat me to it...
congrats, good work.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb