Re: Problem creating Stored Procedure

["Luqman" <pearlsoft@xxxxxxxxxxxx>]

I found the problem, DB2 stored procedures does not support using subqueries 
to generate a field.

For example:
Select code,name,
(select sum(amount) from myTable) myAmount
from myTable

Instead, you have to use the sub query as a table in from clause, like :-
Select a.code,a.name,b.myAmount
from myTable a,
(select sum(amount) myAmount from myTable) b

Thanks to all of you for your support.

Best Regards,

Luqman



"colin williams" <colin.williams@xxxxxxxxxxxx> 
wrote in message news:002401c4c6ed$08b2c960$6402a8c0@xxxxxxxxxxxxxxxx
> luqman
>
> do a "select * from sysprocs" ans see if your sp is listed there
>
> cheers
> Colin.W
> ----- Original Message ----- 
> From: "Luqman" <pearlsoft@xxxxxxxxxxxx>
> To: <rpg400-l@xxxxxxxxxxxx>
> Sent: Tuesday, November 09, 2004 9:43 PM
> Subject: Problem creating Stored Procedure
>
>
>> I am trying to create following procedure in DB2 through Iseries
> Navigator,
>> (Run Sql Script Editor) and it says statement ran successfully
>> but when I check my library, or try to drop that procedure, system says,
>> procedure does not exist.
>>
>> Create Procedure LUQMAN.Asset
>> (IN Dt1 Date, IN Dt2 Date)
>> Language Sql Reads Sql Data
>> P1:Begin
>> Declare C1 Cursor WITH RETURN for
>> select a.acode,rtrim(a.aname),
>> (select sum(amount) from trans
>> where acode like substr(a.acode,1,4) concat '%'
>>  and purdt <Dt1) OB,
>> sum(case when b.amount>0 and
>> purdt between Dt1 and Dt2
>> then b.amount else 0 end) Add,
>> sum(case b.type when '3' then
>> (case when purdt between Dt1 and Dt2
>> then b.amount else 0 end) else 0 end) Del,
>> sum(b.amount) Closing
>> from acc a,trans b
>> where b.acode like substr(a.acode,1,4)concat'%'
>> and a.mainid='0'
>> group by a.acode,a.aname;
>> Open C1;
>> End P1;
>>
>> Any idea please ?
>>
>> Best Regards,
>>
>> Luqman
>>
>>
>>
>>
>> --
>> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing 
>> list
>> To post a message email: RPG400-L@xxxxxxxxxxxx
>> To subscribe, unsubscribe, or change list options,
>> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
>> or email: RPG400-L-request@xxxxxxxxxxxx
>> Before posting, please take a moment to review the archives
>> at http://archive.midrange.com/rpg400-l.
>>
>
> This e-mail has been sent by a company of Bertram Group Ltd, whose 
> registered office is 1 Broadland Business Park, Norwich, NR7 0WF.
> This message, and any attachments, are intended solely for the addressee 
> and may contain privileged or confidential information.  If you are not 
> the intended recipient, any disclosure, copying, distribution or any 
> action taken or omitted to be taken in reliance on it, is prohibited and 
> may be unlawful.  If you believe that you have received this email in 
> error, please contact the sender immediately. Opinions, conclusions and 
> statements of intent in this e-mail are those of the sender and will not 
> bind a Bertram Group Ltd company unless confirmed in writing by a director 
> independently of this message.
> Although we have taken steps to ensure that this email and any attachments 
> are free from any virus, we advise that in keeping with good computing 
> practice the recipient should ensure they are actually virus free.
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>