Click here to Skip to main content
15,860,972 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

SQL
With Sqltmp 
as
(
  Select Count(*) as cnt from employee where age is null and rownum<=2
)
Select 
  case 
    When cnt>0 Then 
      Spool "Filepath"
      Select Empid from employee where rownum<=10;
      Spool off;
    Else
     Spool "filepath"
     Select Empid from employee where rownum<=10;
     Spool off;
  End
From Sqltmp


I'm getting error in strike part like

sql error ora 00905 missing keyword

i cannot able to troubleshoot this error.
Please suggest what is the error in above statement?

Thanks
Posted
Updated 12-Feb-15 6:43am
v3
Comments
Kornfeld Eliyahu Peter 12-Feb-15 12:31pm    
Please format your code - it is very hard to understand what it's all about...
PIEBALDconsult 12-Feb-15 12:44pm    
You can't do that in a CASE.
kankeyan 12-Feb-15 12:47pm    
But in a else part it's running everytime. if cnt > 0

You can't do all that in a CASE statement. It is not a programming language like C#.

See http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm[^]
 
Share this answer
 
Comments
Maciej Los 12-Feb-15 13:17pm    
+5
I'm not sure what you're trying to query but if you want to spool the result just start spool before your query and stop it in the end, like
SQL
Spool "Filepath"
With Sqltmp
as
(
  Select Count(*) as cnt from employee where age is null and rownum<=2
)
Select
  case
    When cnt>0 Then
      Select Empid from employee where rownum<=10;
    Else
     Select Empid from employee where rownum<=10;
  End
From Sqltmp
Spool off;

However, what wont run properly since you have separate statements in case so perhaps you meant to do a scalar query. SOmething like
SQL
Spool "Filepath"
With Sqltmp
as
(
  Select Count(*) as cnt from employee where age is null and rownum<=2
)
Select
  case
    When cnt<0 Then 
       (Select Empid from employee where rownum<=10)
    Else 
       (Select Empid from employee where rownum<=10)
  End
From Sqltmp
Spool off;


However I still don't understand why the select is the same in both case branches and why you select only 2 rows in the WITH clause...
 
Share this answer
 
Comments
kankeyan 13-Feb-15 0:17am    
For Example i given that same query.
I need to screenshot the query and result in two different location based on the condition.
Wendelius 15-Feb-15 5:35am    
In that case you need to build a small program. You can use PL/SQL to do it or some other language that can connect to the database.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900