Click here to Skip to main content
14,240,435 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi,

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
Rate this:
Please Sign up or sign in to vote.

Solution 1

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[^]
   
Comments
Maciej Los 12-Feb-15 13:17pm
   
+5
Rate this:
Please Sign up or sign in to vote.

Solution 2

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
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
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...
   
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, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100