Link to home
Start Free TrialLog in
Avatar of basilhs_s
basilhs_s

asked on

powerbuilder stored procedure datawindow error

i am trying to create a stored procedure datawindow. i am receiving the following error in design time.(code section contains the body of the stored procedure - under sql server 2000 it is executed correctly)

===========================================
can not create datawindow
Request result set number 1 not found
===========================================

i am using pb 10.0 and sql server 2000
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_update_logist_01]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_update_logist_01]
GO
 
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
 
CREATE procedure sp_update_logist_01
/*------------ parameters ---------------*/
 @scr_code_ph		nvarchar(08)
,@scr_date		datetime
,@scr_xwros		nvarchar(2)
,@scr_n_seira		nvarchar(2)
,@scr_parast		nvarchar(2)
,@scr_parast_no		decimal(8,0)
 
as
/*---------------------------------------*/
declare @w_katig_log	nvarchar(2)
declare @w_kwdkin	nvarchar(2)
declare @w_k_pel	nvarchar(2)
declare @w_k_eidos	nvarchar(3)
declare @w_t_fpa	nvarchar(1)
declare @w_pliromi	nvarchar(2)
declare @w_synt		decimal(18,5)
declare @w_axia_xr	decimal(18,2)
declare @w_axia_pis	decimal(18,2)
declare @w_parast_akir  nvarchar(2)
/*--------------------------------------*/
 
 
create table #tbl_syndesh_01
   (
         tmp_ma1_typos		int
        ,tmp_account_code	nvarchar(12)
        ,tmp_xp			int
        ,tmp_axia_xr		decimal(18,2)
        ,tmp_axia_pis		decimal(18,2)
        ,tmp_aa			int
        ,tmp_fpa		nvarchar(02)
	,tmp_kwdkin		nvarchar(02)
   )
 
 
/* eyresh an to parastatiko einai akyrwtiko */
set @w_parast_akir = 
    (
      select kp1_parast_akir from fkpel1 
        where 
	  kp1_code_ph 	= @scr_code_ph 		and
	  kp1_date_wrk	= @scr_date		and
	  kp1_xwros	= @scr_xwros		and
	  kp1_n_seira	= @scr_n_seira		and
	  kp1_parast	= @scr_parast		and
	  kp1_parast_no	= @scr_parast_no)
 
if @w_parast_akir = ''
   begin
    set @w_katig_log = (select pt1_k_e_log from fparast_timol where pt1_key = @scr_parast)
   end
else
   begin
    set @w_katig_log = (select pt1_k_e_log from fparast_timol where pt1_key = @w_parast_akir)
   end
 
if @w_katig_log = ''
   begin
     return
   end
 
set @w_kwdkin = (select kel_plg_code from fkatig_en_log where kel_key = @w_katig_log)
if not exists (select klg_key from fkin_logist where klg_key=@w_kwdkin)
   begin
     return
   end
 
set @w_k_pel = (select mp1_kategory from fmpel1 where mp1_code_ph = @scr_code_ph)
set @w_t_fpa = 
    (
      select kp1_t_fpa from fkpel1 
        where 
	  kp1_code_ph 	= @scr_code_ph 		and
	  kp1_date_wrk	= @scr_date		and
	  kp1_xwros	= @scr_xwros		and
	  kp1_n_seira	= @scr_n_seira		and
	  kp1_parast	= @scr_parast		and
	  kp1_parast_no	= @scr_parast_no
     )
 
set @w_pliromi = 
    (
      select kp1_pliromi from fkpel1 
        where 
	  kp1_code_ph 	= @scr_code_ph 		and
	  kp1_date_wrk	= @scr_date		and
	  kp1_xwros	= @scr_xwros		and
	  kp1_n_seira	= @scr_n_seira		and
	  kp1_parast	= @scr_parast		and
	  kp1_parast_no	= @scr_parast_no
     )
 
set @w_axia_xr = 
    (
      select kp1_axia_xr from fkpel1 
        where 
	  kp1_code_ph 	= @scr_code_ph 		and
	  kp1_date_wrk	= @scr_date		and
	  kp1_xwros	= @scr_xwros		and
	  kp1_n_seira	= @scr_n_seira		and
	  kp1_parast	= @scr_parast		and
	  kp1_parast_no	= @scr_parast_no
     )
 
set @w_axia_pis = 
    (
      select kp1_axia_pis from fkpel1 
        where 
	  kp1_code_ph 	= @scr_code_ph 		and
	  kp1_date_wrk	= @scr_date		and
	  kp1_xwros	= @scr_xwros		and
	  kp1_n_seira	= @scr_n_seira		and
	  kp1_parast	= @scr_parast		and
	  kp1_parast_no	= @scr_parast_no
     )
 
if @w_axia_xr = 0 and @w_axia_pis = 0 
   begin
     return
   end
 
set @w_synt = 
       (abs(@w_axia_xr) + abs(@w_axia_pis)) / (@w_axia_xr+@w_axia_pis)
 
insert into #tbl_syndesh_01		
select
	 1		as tmp_typos	
	,ac2_k_log	as tmp_account_code
	,ac2_xr_pi	as tmp_xp
        ,(select case
	  when ac2_xr_pi in (1,3)  then ka1_axia * @w_synt 
          else 0
	  end)		as tmp_axia_xr
        ,(select case
	  when ac2_xr_pi in (2,4)  then ka1_axia * @w_synt 
          else 0
	  end)		as tmp_axia_pis
 
	,ka1_aa 	as tmp_aa
	,ac2_fpa	as tmp_fpa
	,@w_kwdkin	as tmp_kwdkin
  from fkapo1, facc02, fmapo1 
       where 
	  ka1_code_ph 	= @scr_code_ph 				and
	  ka1_date_wrk	= @scr_date				and 
	  ka1_xwros	= @scr_xwros				and
	  ka1_n_seira	= @scr_n_seira				and
	  ka1_parast	= @scr_parast				and
	  ka1_parast_no	= @scr_parast_no			and
	  ac2_seira	= @scr_n_seira				and
	  ac2_l_parast	= @w_katig_log				and			
	  ac2_fpa	= ka1_ac2_fpa				and  	
	  ka1_code_apo	= ma1_code_apo				and
	  ac2_k_eidos	= ma1_kathg 				and
	  ac2_k_pel	= @w_k_pel				and
	  ac2_axia	= 1
union
select
	 3		as tmp_typos
	,ac2_k_log	as tmp_account_code
	,ac2_xr_pi	as tmp_xp
        ,(select case
	  when ac2_xr_pi in (1,3)  then ka1_ax_fpa * @w_synt 
          else 0
	  end)		as tmp_axia_xr
        ,(select case
	  when ac2_xr_pi in (2, 4) then ka1_ax_fpa * @w_synt 
          else 0
	  end)		as tmp_axia_pis
	,ka1_aa 	as tmp_aa
	,ac2_fpa	as tmp_fpa
	,@w_kwdkin	as tmp_kwdkin
 
  from fkapo1, facc02, fmapo1 
       where 
	  ka1_code_ph 	= @scr_code_ph 				and
	  ka1_date_wrk	= @scr_date				and 
	  ka1_xwros	= @scr_xwros				and
	  ka1_n_seira	= @scr_n_seira				and
	  ka1_parast	= @scr_parast				and
	  ka1_parast_no	= @scr_parast_no			and
	  ac2_seira	= @scr_n_seira				and
	  ac2_l_parast	= @w_katig_log				and			
	  ac2_fpa	= ka1_ac2_fpa				and  	
	  ka1_code_apo	= ma1_code_apo				and
	  ac2_k_eidos	= ma1_kathg 				and
	  ac2_k_pel	= @w_k_pel				and
	  ac2_axia	= 3
 
union
select
	 4		as tmp_typos
	,ac2_k_log	as tmp_account_code
	,ac2_xr_pi	as tmp_xp
        ,(select case
	  when ac2_xr_pi in (1 ,3)  then ka1_axia * @w_synt 
          else 0
	  end)		as tmp_axia_xr
        ,(select case
	  when ac2_xr_pi in (2 ,4)  then ka1_axia * @w_synt 
          else 0
	  end)		as tmp_axia_pis
	,ka1_aa 	as tmp_aa
	,ac2_fpa	as tmp_fpa
	,@w_kwdkin	as tmp_kwdkin
  from fkapo1, facc02, fmapo1, fpway
       where 
	  ka1_code_ph 	= @scr_code_ph 				and
	  ka1_date_wrk	= @scr_date				and 
	  ka1_xwros	= @scr_xwros				and
	  ka1_n_seira	= @scr_n_seira				and
	  ka1_parast	= @scr_parast				and
	  ka1_parast_no	= @scr_parast_no			and
	  ac2_seira	= @scr_n_seira				and
	  ac2_l_parast	= @w_katig_log				and			
	  ac2_fpa	= ka1_ac2_fpa				and  	
	  ka1_code_apo	= ma1_code_apo				and
	  ac2_k_eidos	= ma1_kathg 				and
	  ac2_k_pel	= @w_k_pel				and
	  ac2_axia	= 4					and
	  pway_key	= @w_pliromi				and
	  pway_typ_char='*'
union
select
	 4		as tmp_typos
	,ac2_k_log	as tmp_account_code
	,ac2_xr_pi	as tmp_xp
        ,(select case
	  when ac2_xr_pi in (1, 3)  then ka1_ax_fpa * @w_synt 
          else 0
	  end)		as tmp_axia_xr
        ,(select case
	  when ac2_xr_pi in (2,4)  then ka1_ax_fpa * @w_synt 
          else 0
	  end)		as tmp_axia_pis
	,ka1_aa 	as tmp_aa
	,ac2_fpa	as tmp_fpa
	,@w_kwdkin	as tmp_kwdkin
 
  from fkapo1, facc02, fmapo1 , fpway
       where 
	  ka1_code_ph 	= @scr_code_ph 				and
	  ka1_date_wrk	= @scr_date				and 
	  ka1_xwros	= @scr_xwros				and
	  ka1_n_seira	= @scr_n_seira				and
	  ka1_parast	= @scr_parast				and
	  ka1_parast_no	= @scr_parast_no			and
	  ac2_seira	= @scr_n_seira				and
	  ac2_l_parast	= @w_katig_log				and			
	  ac2_fpa	= ka1_ac2_fpa				and  	
	  ka1_code_apo	= ma1_code_apo				and
	  ac2_k_eidos	= ma1_kathg 				and
	  ac2_k_pel	= @w_k_pel				and
	  ac2_axia	= 4					and
	  pway_key	= @w_pliromi				and
	  pway_typ_char='*'
 
/*-------- results *---------*/
select distinct
	tmp_kwdkin	  as lk1_kwdkin,
	tmp_account_code  as lk1_account_code,
	tmp_xp		  as lk1_xp,
	sum(tmp_axia_xr)  as lk1_axia_xr,
	sum(tmp_axia_pis) as lk1_axia_pis
  from #tbl_syndesh_01
  group by tmp_kwdkin, tmp_account_code, tmp_xp
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Open in new window

Avatar of sandeep_patel
sandeep_patel
Flag of United States of America image

you need to have resultset and out parameter from stored procedure
Avatar of basilhs_s
basilhs_s

ASKER

how can i do it to the specific stored procedure? results are displayed under the /*-------- results -----*/ line?  i am a novice to sql language also
ASKER CERTIFIED SOLUTION
Avatar of sandeep_patel
sandeep_patel
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks very much