Results 1 to 14 of 14
  1. #1
    Join Date
    May 2009
    Posts
    7

    Unanswered: Unable to execute SP

    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(
    >=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,12from 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 (
    == 1)                                      
    THEN                                             
    select penltxns
    .cl_id[8,12]                      
            
    from penltxns                    
            where penltxns
    .cl_id[1,7]= clid          
            
    and penltxns.symbolsymb                
            
    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
    Last edited by RNV; 05-21-09 at 07:03. Reason: changing title

  2. #2
    Join Date
    Aug 2005
    Posts
    140
    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').

  3. #3
    Join Date
    May 2009
    Posts
    7
    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.

  4. #4
    Join Date
    Aug 2005
    Posts
    140
    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...

  5. #5
    Join Date
    May 2009
    Posts
    7
    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;
    Last edited by RNV; 05-21-09 at 04:15.

  6. #6
    Join Date
    Aug 2005
    Posts
    140
    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)

  7. #7
    Join Date
    May 2009
    Posts
    7
    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.
    Last edited by RNV; 05-21-09 at 06:38.

  8. #8
    Join Date
    Aug 2005
    Posts
    140
    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"

  9. #9
    Join Date
    May 2009
    Posts
    7
    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....

  10. #10
    Join Date
    Aug 2005
    Posts
    140
    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.

  11. #11
    Join Date
    May 2009
    Posts
    7
    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(
    >=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,12from 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 (
    == 1)                                                  
    THEN                                                         
    select penltxns
    .cl_id[8,12]                                  
            
    from penltxns                                        
            where penltxns
    .cl_id[1,7]= clid                      
            
    and penltxns.symbolsymb                            
            
    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 
    Attached Thumbnails Attached Thumbnails sp_error.JPG  

  12. #12
    Join Date
    Aug 2005
    Posts
    140
    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.

  13. #13
    Join Date
    Aug 2005
    Posts
    140
    Try to run this code in dbaccess and paste here the screenshot.

  14. #14
    Join Date
    May 2009
    Posts
    7
    THANKS U SO MUCH !!!!

    I tried with Rapid SQL tool and its working for me as well..

    Now I will put UPDATE stmt and check....


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •