Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Posts
    32

    Question Unanswered: Foreach syntax error?

    Hi,

    I am trying out a simple foreach and it is is throwing syntax error. Even if I dont specify anything inside the foreach block, it is showing me syntax error. I dont see anything suspicious. Pl help.

    Code:
    create procedure sp_getMovieTypesInfo(pass_theatreID varchar(5), pass_movieID varchar(10), pass_showDate varchar(8), pass_listOAVBL char(5)) returning char(255);
    
    BEGIN
    
    DEFINE t_perfVoucher varchar(1);
    DEFINE t_irnMovieID varchar(10);
    DEFINE t_movieIRN, t_irnName varchar(255);
    DEFINE t_irnIRN varchar(255);
    DEFINE t_irnAttr VARCHAR(255);
    DEFINE t_irnDesc VARCHAR(255);
    DEFINE t_returnString char(255);
    
    LET t_perfVoucher = 'o';
    LET t_returnString = '';
    
    --If List Only Available is false, then put perf voucher to %.
    IF (lower(pass_listOAVBL) = 'false')
    	THEN LET t_perfVoucher = '%';
    END IF;
    
    -- taking a loop for fetching mutiple values, if any.
    FOREACH IRN_cursor FOR
    	select DISTINCT(FEATURE_CODE_IRN), PERFORMANCE_VOUCHER
    	from boxofc_theatre_showschedules  
    	where THEATRE_ID = pass_theatreID
    	and  SHOW_DATE  = pass_showDate  
    	and  FEATURE_CODE is  not null 
    	and PERFORMANCE_VOUCHER like t_perfVoucher
    	and ALLOW_REMOTE_TICKETING is not null 
    	and ACTIVE= 'true';
    
    
            IF (t_movieIRN != null)
    	       then
    	       select movie_id, name, irn, attr, description 
    	       into t_irnMovieID, t_irnName, t_irnIRN, t_irnAttr, t_irnDesc 
    	       from cinemasource_movie_irns_info
    	       where irn = t_movieIRN;
    	
    -- append the string with comma delimiter for fields and pipe delimiter for  
    -- rows
    
    	       Let t_returnString = t_irnMovieID || ',' || t_irnName  || ',' || t_irnIRN  || ',' || t_irnAttr  || ',' || t_irnDesc  || '|' || ;
            
           end if;
    
    return t_returnString with resume;
    
    END FOREACH;
    
    end;
    end procedure;

  2. #2
    Join Date
    Aug 2011
    Posts
    32
    Can I use a while ... exists loop here instead of foreach? How?

  3. #3
    Join Date
    Aug 2011
    Posts
    32
    Ok. Got it working. I am supposed not to use any semicolon after select statement inside the foreach block. thanks if any had looked into this

Tags for this Thread

Posting Permissions

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