Unable to execute cursor

RNV · #1 (**permalink**) 05-14-09, 09:49

Hi Friends,

I am new to informix so looking for your kind suggestion on my query.
I want to execute below written SP but it is throwing Syntax Error . Can anyone suggest me what is the possible cause of error here. Any advice will be much appreciated.

PHP Code:


			
Create procedure MSA_div_re_sp();                    

                                                     

DEFINE i INTEGER;                                    

DEFINE x INTEGER;                                    

DEFINE clid char(8);                                 

DEFINE symb char(7);                                

                                                     

select unique cl_id,symbol                           

        from penltxns                                

        where set_no="44"                            

        and cl_id not like "%-%" INTO TEMP temp_long;

                                                     

select count(*) into i from temp_long;               

                                                     

while(i >=1)                                         

select first 1 cl_id into clid from temp_long;          

select first 1 symbol into symb from temp_long;         

                                                     

select unique cl_id[8,12] from holdings              

      where holdings.cl_id[1,7] = clid               

      and symbol = symb

            and cl_id not like "%-00"                  

      and cl_id not like "%-PH"                  

      and cl_id not like "%-RJ" into TEMP t_long;

                                                 

select count(*) into x from t_long;              

                                                 

If (x == 1)                                      

THEN                                             

select penltxns.cl_id[8,12]                      

        from penltxns                    

        where penltxns.cl_id[1,7]= clid          

        and penltxns.symbol= symb                

        and penltxns.cl_id not like "%-%"        

        and penltxns.set_no = "44"               

        and penltxns.can_date = "12/31/1899";    

END IF                                           

DELETE from temp_long                            

where cl_id=clid                                 

and symbol=symb;                                 

                                                 

DROP TABLE t_long;   

END WHILE     

              

END PROCEDURE

Thnx,
Raj

stanislav.ondac · #2 (**permalink**) 05-16-09, 10:03

what is your informix version?
And where did you get this code? It's supposed to be SPL code? it looks more like esql/c statements (but without 'exec sql').

RNV · #3 (**permalink**) 05-18-09, 02:38

thanks for your reply. Informix verion is 7.32.UC2. I have written this code by my own, seeing the syntax from an informix pdf doc(Informix_Guide_to_SQL-Tutorial.pdf) found from google. I tried with other way also like using EXEC SQL before declare statement and ending the EXEC stmt properly.

stanislav.ondac · #4 (**permalink**) 05-18-09, 05:11

I still don't know if you want to write SPL or esql/c code.
Do yo know what is the difference?
In the first case, i recommend you to look at the syntax and usage of the 'foreach' statement...

RNV · #5 (**permalink**) 05-21-09, 04:08

I want to write an SPL in Unix environment with extension .sql and execute it using isql in Unix. I have changed my SP in the above way (Pl. see the code above) and I removed the cursor logic. Still I am unable to execute it.

Please advice me if I am doing it using wrong syntax as i am getting syntax error in the first line. below is the error.

Create procedure MSA_div_re_sp();
# ^
# 201: A syntax error has occurred.
#
DEFINE i INTEGER;
DEFINE x INTEGER;
DEFINE clid char(8);
DEFINE symb chari(7);

select unique cl_id,symbol
from penltxns
where set_no="44"
and cl_id not like "%-%" INTO TEMP temp_long;
select unique cl_id,symbol
from penltxns
where set_no="44"
and cl_id not like "%-%" INTO TEMP temp_long;

stanislav.ondac · #6 (**permalink**) 05-21-09, 05:37

Is this your whole code? where is the "end procedure"?

And the code above....a lot of errors there...

Code:

clid = select first 1 cl_id from temp_long;
symb = select first 2 symbol from temp_long;

use select .... into variable_name from table

and you CAN'T assign 2 lines into one normal variable...you should change the type or use loop/cursor.

Code:

select penltxns.cl_id[8,12]
from penltxns, t_long
where penltxns.cl_id[1,7]= clid
and penltxns.symbol= symb
and penltxns.cl_id not like "%-%"
and penltxns.set_no = "44"
and penltxns.can_date = "12/31/1899";

bad usage...use 'into temp' or something like that...

And if you intend to take it seriously, i recommend you to start read some manuals.(Informix guide to SQL tutorial , etc)

RNV · #7 (**permalink**) 05-21-09, 06:32

Oops sorry for the silly mistake..

. I got I changed 2 to 1, i can understand that, and used "into <var name> as written in above code . The code is complete with END PROCEDURE;

Still its showing the same syntax error pointing to the braces given after proc name.

stanislav.ondac · #8 (**permalink**) 05-21-09, 06:52

so try to remove semicolon character(';') in the end
of the create procedure statement.

And you still don't change
your select:

Code:

select penltxns.cl_id[8,12]                      
        from penltxns, t_long                    
        where penltxns.cl_id[1,7]= clid          
        and penltxns.symbol= symb                
        and penltxns.cl_id not like "%-%"        
        and penltxns.set_no = "44"               
        and penltxns.can_date = "12/31/1899"

RNV · #9 (**permalink**) 05-21-09, 07:07

I have removed the ";" from end . and I only removed t_long from my select stmt. I am comparing one instance of cl_id / symbol of penltxns table with the value of variable clid and symb. Isn't it correct. If I am executing this select stmt seperatly in sql prompt it runs then why not here...

Still the same syntax error....

stanislav.ondac · #10 (**permalink**) 05-21-09, 07:19

did you remove ';' from the first line (..create procedure) or from the last line(end procedure)? I was 'talking' about the first line.

The select statement is not correct, because within SPL routine, you CAN'T write results on the screen(as in your sql prompt)...you can only store them...in variable, or in temporary table.

RNV · #11 (**permalink**) 05-21-09, 07:37

yes... I tried again as per your suggestion. But I am sorry its not working . please find my fresh code here. I put the output of my last select stmt in a temp table. Actually I have to do an update in place of last select query but before updating i want to see my sp executing. please find the fresh code here and the error screenshot attached (sp_error.jpg).

PHP Code:


			
Create procedure MSA_div_re_sp()                     

                                                     

DEFINE i INTEGER;                                    

DEFINE x INTEGER;                                    

DEFINE clid char(8);                                 

DEFINE symb chari(7);                                

                                                     

select unique cl_id,symbol                           

        from penltxns                                

        where set_no="44"                            

        and cl_id not like "%-%" INTO TEMP temp_long;

                                                     

                                                     

select count(*) into i from temp_long;               

                                                     

while(i >=1)                                         

                                                     

 select first 1 cl_id into clid from temp_long;      

 select first 1 symbol into symb from temp_long;     

                                                     

select unique cl_id[8,12] from holdings              

      where holdings.cl_id[1,7] = clid               

      and symbol = symb                              

            and cl_id not like "%-00"                              

      and cl_id not like "%-PH"                              

      and cl_id not like "%-RJ" into TEMP t_long;            

                                                             

select count(*) into x from t_long;                          

                                                             

If (x == 1)                                                  

THEN                                                         

select penltxns.cl_id[8,12]                                  

        from penltxns                                        

        where penltxns.cl_id[1,7]= clid                      

        and penltxns.symbol= symb                            

        and penltxns.cl_id not like "%-%"                    

        and penltxns.set_no = "44"                           

        and penltxns.can_date = "12/31/1899" INTO TEMP final;

END IF                                                       

                                                             

DELETE from temp_long                                        

where cl_id=clid                                             

and symbol=symb;                                             

                                                             

DROP TABLE t_long;



END WHILE    

             

END PROCEDURE

stanislav.ondac · #12 (**permalink**) 05-21-09, 10:06

I tried your code(after 2 little corrections) and it works

But I don't have IDS 7.3, so i tried it on IDS 10.0.

So it looks like, the syntax is OK...

here is the code, that works for me:

Code:

Create procedure MSA_div_re_sp()

DEFINE i INTEGER;
DEFINE x INTEGER;
DEFINE clid char(8);
DEFINE symb char(7);

select unique cl_id,symbol
        from penltxns
        where set_no="44"
        and cl_id not like "%-%" INTO TEMP temp_long;


select count(*) into i from temp_long;

while(i >=1)

 select first 1 cl_id into clid from temp_long;
 select first 1 symbol into symb from temp_long;

select unique cl_id[8,12] from holdings
      where holdings.cl_id[1,7] = clid
      and symbol = symb
            and cl_id not like "%-00"
      and cl_id not like "%-PH"
      and cl_id not like "%-RJ" into TEMP t_long;

select count(*) into x from t_long;

If (x == 1)
THEN
select penltxns.cl_id[8,12]
        from penltxns
        where penltxns.cl_id[1,7]= clid
        and penltxns.symbol= symb
        and penltxns.cl_id not like "%-%"
        and penltxns.set_no = "44"
        and penltxns.can_date = "12/31/1899" INTO TEMP final;
END IF

DELETE from temp_long
where cl_id=clid
and symbol=symb;

DROP TABLE t_long;

END WHILE

END PROCEDURE;

Are you using dbaccess tool? If not, try it...it's good for discovering errors in SPL routines.