CODEWITHSUNDEEP
sqlsyntaxprocedureinformix
Stuart
Stuart

Reputation: 11

informix stored procedure error on return "A syntax error has occurred."

I have written this stored procedure and I keep getting the following error "A syntax error has occurred." I am its saying this at RETURN I have marked it with a ** (this is not in the code I have used) Can you please help this is driving me mad. I do not want help with this bit but I am being told that 'It looks like your post is mostly code; please add some more details.' And to this end I am typing more just so I can post this question rrrrrrg more Sh!t that is driving me mad today.

drop procedure CustomerSOPDetails(char(10));

create procedure CustomerSOPDetails(account char(10))

returning


char(10),
char(3),
char(3),
char(3),
char(40),
char(10),
char(30),
char(30),
char(30),
char(30),
char(30),
char(10),
char(30),
char(8),
char(10);


define account char(10);
define uzbusman1 char(3);
define uzsalrep1 char(3);
define uzsalcor1 char(3);
define name char(40);
define alphcode char(10);
define addr1 char(30);
define addr2 char(30);
define addr3 char(30);
define addr4 char(30);
define addr5 char(30);
define postcode char(10);
define telephone char(30);
define lastdate char(8);
define addtype char(10);



foreach
select
oecus.oecus_account,
oecus.oecus_uzbusman1,
oecus.oecus_uzsalrep1,
oecus.oecus_uzsalcor1,
ndmas.ndm_name,
ndmas.ndm_alphcode,
ndmas.ndm_addr1,
ndmas.ndm_addr2,
ndmas.ndm_addr3,
ndmas.ndm_addr4,
ndmas.ndm_addr5,
ndmas.ndm_postcode,
ndmas.ndm_telephone,
ndmas.ndm_lastdate,
ndmas.ndm_addtype

from oecus
inner join ndmas 
on oecus.oecus_account=ndmas.ndm_ndcode

where oecus_account = account


RETURN **
 account,
 uzbusman1,
 uzsalrep1,
 uzsalcor1,
 name,
 alphcode, 
 addr1,
 addr2,
 addr3,
 addr4,
 addr5,
 postcode, 
 postcode,
 lastdate,
 addtype
with resume;

end foreach

end procedure;

Upvotes: 1

Views: 424

Answers (1)

jsagrera
jsagrera

Reputation: 1998

Try with an INTO clause:

--drop procedure CustomerSOPDetails(char(10));

create procedure CustomerSOPDetails(account char(10))

returning


char(10),
char(3),
char(3),
char(3),
char(40),
char(10),
char(30),
char(30),
char(30),
char(30),
char(30),
char(10),
char(30),
char(8),
char(10);


define uzbusman1 char(3);
define uzsalrep1 char(3);
define uzsalcor1 char(3);
define name char(40);
define alphcode char(10);
define addr1 char(30);
define addr2 char(30);
define addr3 char(30);
define addr4 char(30);
define addr5 char(30);
define postcode char(10);
define telephone char(30);
define lastdate char(8);
define addtype char(10);



foreach
    select
    oecus.oecus_account,
    oecus.oecus_uzbusman1,
    oecus.oecus_uzsalrep1,
    oecus.oecus_uzsalcor1,
    ndmas.ndm_name,
    ndmas.ndm_alphcode,
    ndmas.ndm_addr1,
    ndmas.ndm_addr2,
    ndmas.ndm_addr3,
    ndmas.ndm_addr4,
    ndmas.ndm_addr5,
    ndmas.ndm_postcode,
    ndmas.ndm_telephone,
    ndmas.ndm_lastdate,
    ndmas.ndm_addtype

    into 
     account,
     uzbusman1,
     uzsalrep1,
     uzsalcor1,
     name,
     alphcode, 
     addr1,
     addr2,
     addr3,
     addr4,
     addr5,
     postcode, 
     telephone,
     lastdate,
     addtype

    from oecus
    inner join ndmas 
    on oecus.oecus_account=ndmas.ndm_ndcode
    where oecus_account = account

    RETURN 
     account,
     uzbusman1,
     uzsalrep1,
     uzsalcor1,
     name,
     alphcode, 
     addr1,
     addr2,
     addr3,
     addr4,
     addr5,
     postcode, 
     telephone,
     lastdate,
     addtype
    with resume;

end foreach

end procedure;

And next time, try reducing the SQL, it makes thing a lot easier to see ;)

Upvotes: 3

Related Questions