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
==========================
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
you need to have resultset and out parameter from stored procedure
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks very much