Mateen
asked on
How to call a sql server stored procedure from powerbuilder
I have created the following procedure in sql server 2000
-- procedure starts
declare @year smallint
declare @month smallint
declare @company_code varchar(2)
set @year=2003
set @month=10
set @company_code='02'
declare @budget_n_exp table
(ser_no int identity(1,1),
dept_name varchar(70),
expense numeric(10,0),
budget numeric(10,0),
mth smallint,
yr smallint,
per smallint)
while @month between 10 and 12
begin
insert @budget_n_exp
select x.dept_name,
expense=round(sum(x.iss_qt y * x.rate),0),
budget = isnull(sum(distinct budget_det.budget_qty),0),
mth=@month,
yr=@year,
per=@month-9
from
(
SELECT dept_code=issm.iss_to,
dept_name=dept.dept_name,
item_code=issd.item_code,
iss_qty = sum(issd.issue_qty),
rate =
(SELECT sum(recd.rate * recd.qty) / sum(recd.qty)
FROM str_receive_det recd,
str_receive_mst recm
WHERE ( recm.trans_no = recd.trans_no )
and ( recm.company_code = recd.company_code )
and ( recd.item_code = issd.item_code)
and ( recm.company_code = @company_code)
and ( year(recm.trans_date) <= @year)
and ( month(recm.trans_date) <=
case when year(recm.trans_date) = @year then @month else 12 end))
FROM str_issue_det issd,
str_issue_mst issm,
str_item item,
department dept
WHERE ( item.item_code = issd.item_code )
and ( issm.trans_no = issd.trans_no )
and ( issm.company_code = issd.company_code )
and ( dept.dept_code = issm.iss_to)
and ( year(issm.issue_date) = @year )
and ( month(issm.issue_date) = @month )
and ( issm.company_code = @company_code)
group by issm.iss_to,
dept.dept_name,
issd.item_code ) x,
budget_det
where x.dept_code *= budget_det.dept_code
and budget_det.year_no = @year
and budget_det.month_no = @month
group by x.dept_name
set @month=@month+1
end
set @month=1
while @month between 1 and 9
begin
insert @budget_n_exp
select x.dept_name,
expense=round(sum(x.iss_qt y * x.rate),0),
budget = isnull(sum(distinct budget_det.budget_qty),0),
mth=@month,
yr=@year+1,
per=@month
from
(
SELECT dept_code=issm.iss_to,
dept_name=dept.dept_name,
item_code=issd.item_code,
iss_qty = sum(issd.issue_qty),
rate =
(SELECT sum(recd.rate * recd.qty) / sum(recd.qty)
FROM str_receive_det recd,
str_receive_mst recm
WHERE ( recm.trans_no = recd.trans_no )
and ( recm.company_code = recd.company_code )
and ( recd.item_code = issd.item_code)
and ( recm.company_code = @company_code)
and ( year(recm.trans_date) <= @year+1)
and ( month(recm.trans_date) <=
case when year(recm.trans_date) = @year+1 then @month else 12 end))
FROM str_issue_det issd,
str_issue_mst issm,
str_item item,
department dept
WHERE ( item.item_code = issd.item_code )
and ( issm.trans_no = issd.trans_no )
and ( issm.company_code = issd.company_code )
and ( dept.dept_code = issm.iss_to)
and ( year(issm.issue_date) = @year+1 )
and ( month(issm.issue_date) = @month )
and ( issm.company_code = @company_code)
group by issm.iss_to,
dept.dept_name,
issd.item_code ) x,
budget_det
where x.dept_code *= budget_det.dept_code
and budget_det.year_no = @year+1
and budget_det.month_no = @month
group by x.dept_name
set @month=@month+1
end
-- procedure end
now in my actual database
select * from @budget_n_exp
give following result
ser dept_name expense budget mth yr per
1 ACCOUNT 24716 2000 10 2003 1
2 AIR JET 44179 143750 10 2003 1
3 AIR JET 1218 5000 10 2003 1
4 AIR JET 78119 63250 10 2003 1
5 BOILER 36238 23000 10 2003 1
6 CHILLER 57696 92000 10 2003 1
7 ELETRICA 2989 5750 10 2003 1
8 FOLDING 25860 5750 10 2003 1
9 GENERAL 34668 23000 10 2003 1
10 INDIGO C 4015953 1667500 10 2003 1
11 MASTER I 106250 115000 10 2003 1
12 PACKING 50533 5750 10 2003 1
13 PACKING 275939 299000 10 2003 1
14 SANFORIZ 5242 57500 10 2003 1
15 WASHING 55076 17250 10 2003 1
16 WASHING 147188 460000 10 2003 1
17 WORK SHO 4568 11500 10 2003 1
18 ACCOUNT 17570 2000 11 2003 2
19 AIR JET 43646 143750 11 2003 2
20 AIR JET 189931 63250 11 2003 2
21 CHILLER 271275 92000 11 2003 2
22 ELETRICA 2402 5750 11 2003 2
23 FOLDING 29653 5750 11 2003 2
24 GENERAL 4498 23000 11 2003 2
25 INDIGO C 4451377 1667500 11 2003 2
26 MASTER I 54458 115000 11 2003 2
27 PACKING 26598 5750 11 2003 2
28 PACKING 194833 299000 11 2003 2
29 SANFORIZ 12160 57500 11 2003 2
30 WASHING 34499 17250 11 2003 2
31 WASHING 189627 460000 11 2003 2
32 WORK SHO 1513 11500 11 2003 2
33 ACCOUNT 15182 2000 12 2003 3
34 AIR JET 76705 143750 12 2003 3
35 AIR JET 6133 5000 12 2003 3
36 AIR JET 198427 63250 12 2003 3
37 BOILER 365486 23000 12 2003 3
38 CHILLER 120099 92000 12 2003 3
39 ELETRICA 2232 5750 12 2003 3
40 FOLDING 19962 5750 12 2003 3
41 GENERAL 14241 23000 12 2003 3
42 INDIGO C 4477673 1667500 12 2003 3
43 MASTER I 154352 115000 12 2003 3
44 PACKING 28170 5750 12 2003 3
45 PACKING 201776 299000 12 2003 3
46 WASHING 144747 17250 12 2003 3
47 WASHING 186800 460000 12 2003 3
48 WORK SHO 1677 11500 12 2003 3
49 ACCOUNT 14720 0 1 2004 1
50 AIR JET 87593 0 1 2004 1
51 AIR JET 8711 0 1 2004 1
52 AIR JET 242401 0 1 2004 1
53 BOILER 22829 0 1 2004 1
54 CHILLER 44866 0 1 2004 1
55 ELETRICA 635 0 1 2004 1
56 FOLDING 67726 0 1 2004 1
57 GENERAL 78033 0 1 2004 1
58 INDIGO C 4465681 0 1 2004 1
59 MASTER I 274688 0 1 2004 1
60 PACKING 35658 0 1 2004 1
61 PACKING 229903 0 1 2004 1
62 SANFORIZ 9360 0 1 2004 1
63 WASHING 136247 0 1 2004 1
64 WASHING 109311 0 1 2004 1
65 WORK SHO 8955 0 1 2004 1
66 AIR JET 71439 0 2 2004 2
67 CHILLER 4840 0 2 2004 2
68 MASTER I 10112 0 2 2004 2
69 SANFORIZ 4016 0 2 2004 2
Now in powerbuilder i want to create a datawindow with this sql
select * from (the above data)
where yr = :retrieval_argument_year
and mth=:retrieval_argument_mo nth
That is i want my procedure to return a whole table
and how to call/access this table from within powerbuilder.
-- procedure starts
declare @year smallint
declare @month smallint
declare @company_code varchar(2)
set @year=2003
set @month=10
set @company_code='02'
declare @budget_n_exp table
(ser_no int identity(1,1),
dept_name varchar(70),
expense numeric(10,0),
budget numeric(10,0),
mth smallint,
yr smallint,
per smallint)
while @month between 10 and 12
begin
insert @budget_n_exp
select x.dept_name,
expense=round(sum(x.iss_qt
budget = isnull(sum(distinct budget_det.budget_qty),0),
mth=@month,
yr=@year,
per=@month-9
from
(
SELECT dept_code=issm.iss_to,
dept_name=dept.dept_name,
item_code=issd.item_code,
iss_qty = sum(issd.issue_qty),
rate =
(SELECT sum(recd.rate * recd.qty) / sum(recd.qty)
FROM str_receive_det recd,
str_receive_mst recm
WHERE ( recm.trans_no = recd.trans_no )
and ( recm.company_code = recd.company_code )
and ( recd.item_code = issd.item_code)
and ( recm.company_code = @company_code)
and ( year(recm.trans_date) <= @year)
and ( month(recm.trans_date) <=
case when year(recm.trans_date) = @year then @month else 12 end))
FROM str_issue_det issd,
str_issue_mst issm,
str_item item,
department dept
WHERE ( item.item_code = issd.item_code )
and ( issm.trans_no = issd.trans_no )
and ( issm.company_code = issd.company_code )
and ( dept.dept_code = issm.iss_to)
and ( year(issm.issue_date) = @year )
and ( month(issm.issue_date) = @month )
and ( issm.company_code = @company_code)
group by issm.iss_to,
dept.dept_name,
issd.item_code ) x,
budget_det
where x.dept_code *= budget_det.dept_code
and budget_det.year_no = @year
and budget_det.month_no = @month
group by x.dept_name
set @month=@month+1
end
set @month=1
while @month between 1 and 9
begin
insert @budget_n_exp
select x.dept_name,
expense=round(sum(x.iss_qt
budget = isnull(sum(distinct budget_det.budget_qty),0),
mth=@month,
yr=@year+1,
per=@month
from
(
SELECT dept_code=issm.iss_to,
dept_name=dept.dept_name,
item_code=issd.item_code,
iss_qty = sum(issd.issue_qty),
rate =
(SELECT sum(recd.rate * recd.qty) / sum(recd.qty)
FROM str_receive_det recd,
str_receive_mst recm
WHERE ( recm.trans_no = recd.trans_no )
and ( recm.company_code = recd.company_code )
and ( recd.item_code = issd.item_code)
and ( recm.company_code = @company_code)
and ( year(recm.trans_date) <= @year+1)
and ( month(recm.trans_date) <=
case when year(recm.trans_date) = @year+1 then @month else 12 end))
FROM str_issue_det issd,
str_issue_mst issm,
str_item item,
department dept
WHERE ( item.item_code = issd.item_code )
and ( issm.trans_no = issd.trans_no )
and ( issm.company_code = issd.company_code )
and ( dept.dept_code = issm.iss_to)
and ( year(issm.issue_date) = @year+1 )
and ( month(issm.issue_date) = @month )
and ( issm.company_code = @company_code)
group by issm.iss_to,
dept.dept_name,
issd.item_code ) x,
budget_det
where x.dept_code *= budget_det.dept_code
and budget_det.year_no = @year+1
and budget_det.month_no = @month
group by x.dept_name
set @month=@month+1
end
-- procedure end
now in my actual database
select * from @budget_n_exp
give following result
ser dept_name expense budget mth yr per
1 ACCOUNT 24716 2000 10 2003 1
2 AIR JET 44179 143750 10 2003 1
3 AIR JET 1218 5000 10 2003 1
4 AIR JET 78119 63250 10 2003 1
5 BOILER 36238 23000 10 2003 1
6 CHILLER 57696 92000 10 2003 1
7 ELETRICA 2989 5750 10 2003 1
8 FOLDING 25860 5750 10 2003 1
9 GENERAL 34668 23000 10 2003 1
10 INDIGO C 4015953 1667500 10 2003 1
11 MASTER I 106250 115000 10 2003 1
12 PACKING 50533 5750 10 2003 1
13 PACKING 275939 299000 10 2003 1
14 SANFORIZ 5242 57500 10 2003 1
15 WASHING 55076 17250 10 2003 1
16 WASHING 147188 460000 10 2003 1
17 WORK SHO 4568 11500 10 2003 1
18 ACCOUNT 17570 2000 11 2003 2
19 AIR JET 43646 143750 11 2003 2
20 AIR JET 189931 63250 11 2003 2
21 CHILLER 271275 92000 11 2003 2
22 ELETRICA 2402 5750 11 2003 2
23 FOLDING 29653 5750 11 2003 2
24 GENERAL 4498 23000 11 2003 2
25 INDIGO C 4451377 1667500 11 2003 2
26 MASTER I 54458 115000 11 2003 2
27 PACKING 26598 5750 11 2003 2
28 PACKING 194833 299000 11 2003 2
29 SANFORIZ 12160 57500 11 2003 2
30 WASHING 34499 17250 11 2003 2
31 WASHING 189627 460000 11 2003 2
32 WORK SHO 1513 11500 11 2003 2
33 ACCOUNT 15182 2000 12 2003 3
34 AIR JET 76705 143750 12 2003 3
35 AIR JET 6133 5000 12 2003 3
36 AIR JET 198427 63250 12 2003 3
37 BOILER 365486 23000 12 2003 3
38 CHILLER 120099 92000 12 2003 3
39 ELETRICA 2232 5750 12 2003 3
40 FOLDING 19962 5750 12 2003 3
41 GENERAL 14241 23000 12 2003 3
42 INDIGO C 4477673 1667500 12 2003 3
43 MASTER I 154352 115000 12 2003 3
44 PACKING 28170 5750 12 2003 3
45 PACKING 201776 299000 12 2003 3
46 WASHING 144747 17250 12 2003 3
47 WASHING 186800 460000 12 2003 3
48 WORK SHO 1677 11500 12 2003 3
49 ACCOUNT 14720 0 1 2004 1
50 AIR JET 87593 0 1 2004 1
51 AIR JET 8711 0 1 2004 1
52 AIR JET 242401 0 1 2004 1
53 BOILER 22829 0 1 2004 1
54 CHILLER 44866 0 1 2004 1
55 ELETRICA 635 0 1 2004 1
56 FOLDING 67726 0 1 2004 1
57 GENERAL 78033 0 1 2004 1
58 INDIGO C 4465681 0 1 2004 1
59 MASTER I 274688 0 1 2004 1
60 PACKING 35658 0 1 2004 1
61 PACKING 229903 0 1 2004 1
62 SANFORIZ 9360 0 1 2004 1
63 WASHING 136247 0 1 2004 1
64 WASHING 109311 0 1 2004 1
65 WORK SHO 8955 0 1 2004 1
66 AIR JET 71439 0 2 2004 2
67 CHILLER 4840 0 2 2004 2
68 MASTER I 10112 0 2 2004 2
69 SANFORIZ 4016 0 2 2004 2
Now in powerbuilder i want to create a datawindow with this sql
select * from (the above data)
where yr = :retrieval_argument_year
and mth=:retrieval_argument_mo
That is i want my procedure to return a whole table
and how to call/access this table from within powerbuilder.
One more point,
insert @budget_n_exp <-- syntax like that was introduced in sql 2000 only. If PB does not like to for some reason, try this at the end of procedure.
select *
into #temp
from @budget_n_exp
where yr = @aYear and mth = @aMonth <-- also correct the syntax in my previous post
select * from #temp
Or even from the beggining insert into #temp instead of @budget_n_exp
In that case you will create #temp as follows:
Create table #temp (ser_no int identity(1,1),
dept_name varchar(70),
expense numeric(10,0),
budget numeric(10,0),
mth smallint,
yr smallint,
per smallint)
Then :
insert #temp
select x.dept_name,
expense=round(sum(x.iss_qt y * x.rate),0),
budget = isnull(sum(distinct budget_det.budget_qty),0),
mth=@month,
yr=@year+1,
per=@month
....other code
HTH
insert @budget_n_exp <-- syntax like that was introduced in sql 2000 only. If PB does not like to for some reason, try this at the end of procedure.
select *
into #temp
from @budget_n_exp
where yr = @aYear and mth = @aMonth <-- also correct the syntax in my previous post
select * from #temp
Or even from the beggining insert into #temp instead of @budget_n_exp
In that case you will create #temp as follows:
Create table #temp (ser_no int identity(1,1),
dept_name varchar(70),
expense numeric(10,0),
budget numeric(10,0),
mth smallint,
yr smallint,
per smallint)
Then :
insert #temp
select x.dept_name,
expense=round(sum(x.iss_qt
budget = isnull(sum(distinct budget_det.budget_qty),0),
mth=@month,
yr=@year+1,
per=@month
....other code
HTH
ls_statement = "select * from @budget_n_exp"
dw_recpt.Modify (" DataWindow.Table.Procedure
dw_recpt.settransobject(as
as_transaction.AutoCommit = true
ll_rows = dw_recpt.retrieve()
as_transaction.AutoCommit = false
Correctionn:
This
select * from @budget_n_exp
where yr = @aYear and @mth = @aMonth
should be
select * from @budget_n_exp
where yr = @aYear and mth = @aMonth
or
select * from #temp
where yr = @aYear and mth = @aMonth
HTH
This
select * from @budget_n_exp
where yr = @aYear and @mth = @aMonth
should be
select * from @budget_n_exp
where yr = @aYear and mth = @aMonth
or
select * from #temp
where yr = @aYear and mth = @aMonth
HTH
ASKER
When I posted this question I have in mind that
a procedure will be declared in such a way that it will return a table
and then i could write
select *
from
dbo.MyDefinedProcedure(yea r_value,mo nth_value)
that is i never imagined that i will have to go so long.
will have to use datastore.
Isn't it possible that a datawindow be created with sql select
instead of stored procedure as the store_procedure is returning table.
I am unable to explain as english is not my native lanugage.
a procedure will be declared in such a way that it will return a table
and then i could write
select *
from
dbo.MyDefinedProcedure(yea
that is i never imagined that i will have to go so long.
will have to use datastore.
Isn't it possible that a datawindow be created with sql select
instead of stored procedure as the store_procedure is returning table.
I am unable to explain as english is not my native lanugage.
have you tried
ls_statement = "select * from @budget_n_exp"
dw_recpt.Modify (" DataWindow.Table.Procedure = '"+ls_statement+"'")
dw_recpt.settransobject(as _transacti on)
ll_rows = dw_recpt.retrieve()
ls_statement = "select * from @budget_n_exp"
dw_recpt.Modify (" DataWindow.Table.Procedure
dw_recpt.settransobject(as
ll_rows = dw_recpt.retrieve()
ASKER
Please consider this
--This example returns an inline table-valued function.
USE pubs
GO
CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
FROM sales s, titles t
WHERE s.stor_id = @storeid and
t.title_id = s.title_id)
now i can write
select *
from
SalesByStore() -- i don't know the exact syntax.
Should I change my procedure to function. if so then how.
--This example returns an inline table-valued function.
USE pubs
GO
CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
FROM sales s, titles t
WHERE s.stor_id = @storeid and
t.title_id = s.title_id)
now i can write
select *
from
SalesByStore() -- i don't know the exact syntax.
Should I change my procedure to function. if so then how.
No. You do not need to convert SP to Function. Absolutely not. But all you need to do is return a resultset using "select" at the end. It seems that all you are doing is insert into table @budget_n_exp. All you need to do is add a select statement at the end of stored proc to retuen a result set using "select". Then you can use that stored procedure as the source for you datawindow. I have hundreds of datawindow like that.
have you tried what I suggested at the end of proc.
select *
into #temp
from @budget_n_exp
where yr = @aYear and mth = @aMonth <-- also correct the syntax in my previous post
select * from #temp
If you post your proc I will modify it for you.
HTH
Namasi.
have you tried what I suggested at the end of proc.
select *
into #temp
from @budget_n_exp
where yr = @aYear and mth = @aMonth <-- also correct the syntax in my previous post
select * from #temp
If you post your proc I will modify it for you.
HTH
Namasi.
Create a datawindow out of this proc and everything will be just fine.
Create procedure sp_GetMyResults
(
@aYear int,
@aMonth int
)
AS
BEGIN
declare @year smallint
declare @month smallint
declare @company_code varchar(2)
set @year=2003
set @month=10
set @company_code='02'
CREATE TABLE #budget_n_exp table
(ser_no int identity(1,1),
dept_name varchar(70),
expense numeric(10,0),
budget numeric(10,0),
mth smallint,
yr smallint,
per smallint)
while @month between 10 and 12
begin
insert #budget_n_exp table
select x.dept_name,
expense=round(sum(x.iss_qt y * x.rate),0),
budget = isnull(sum(distinct budget_det.budget_qty),0),
mth=@month,
yr=@year,
per=@month-9
from
(
SELECT dept_code=issm.iss_to,
dept_name=dept.dept_name,
item_code=issd.item_code,
iss_qty = sum(issd.issue_qty),
rate =
(SELECT sum(recd.rate * recd.qty) / sum(recd.qty)
FROM str_receive_det recd,
str_receive_mst recm
WHERE ( recm.trans_no = recd.trans_no )
and ( recm.company_code = recd.company_code )
and ( recd.item_code = issd.item_code)
and ( recm.company_code = @company_code)
and ( year(recm.trans_date) <= @year)
and ( month(recm.trans_date) <=
case when year(recm.trans_date) = @year then @month else 12 end))
FROM str_issue_det issd,
str_issue_mst issm,
str_item item,
department dept
WHERE ( item.item_code = issd.item_code )
and ( issm.trans_no = issd.trans_no )
and ( issm.company_code = issd.company_code )
and ( dept.dept_code = issm.iss_to)
and ( year(issm.issue_date) = @year )
and ( month(issm.issue_date) = @month )
and ( issm.company_code = @company_code)
group by issm.iss_to,
dept.dept_name,
issd.item_code ) x,
budget_det
where x.dept_code *= budget_det.dept_code
and budget_det.year_no = @year
and budget_det.month_no = @month
group by x.dept_name
set @month=@month+1
end
set @month=1
while @month between 1 and 9
begin
insert #budget_n_exp table
select x.dept_name,
expense=round(sum(x.iss_qt y * x.rate),0),
budget = isnull(sum(distinct budget_det.budget_qty),0),
mth=@month,
yr=@year+1,
per=@month
from
(
SELECT dept_code=issm.iss_to,
dept_name=dept.dept_name,
item_code=issd.item_code,
iss_qty = sum(issd.issue_qty),
rate =
(SELECT sum(recd.rate * recd.qty) / sum(recd.qty)
FROM str_receive_det recd,
str_receive_mst recm
WHERE ( recm.trans_no = recd.trans_no )
and ( recm.company_code = recd.company_code )
and ( recd.item_code = issd.item_code)
and ( recm.company_code = @company_code)
and ( year(recm.trans_date) <= @year+1)
and ( month(recm.trans_date) <=
case when year(recm.trans_date) = @year+1 then @month else 12 end))
FROM str_issue_det issd,
str_issue_mst issm,
str_item item,
department dept
WHERE ( item.item_code = issd.item_code )
and ( issm.trans_no = issd.trans_no )
and ( issm.company_code = issd.company_code )
and ( dept.dept_code = issm.iss_to)
and ( year(issm.issue_date) = @year+1 )
and ( month(issm.issue_date) = @month )
and ( issm.company_code = @company_code)
group by issm.iss_to,
dept.dept_name,
issd.item_code ) x,
budget_det
where x.dept_code *= budget_det.dept_code
and budget_det.year_no = @year+1
and budget_det.month_no = @month
group by x.dept_name
set @month=@month+1
end
select *
from #budget_n_exp table
where mth = @aMonth and
yr = @aYear
DROP TABLE #budget_n_exp table
END
2) To retrieve try:
long ll_year = 2004, ll_month = 2, ll_rc
dw_1.Retrieve(ll_year, ll_month) // dw_1 is associated with a dw created using stored proc sp_GetMyResults
OR
datastore lds_result
long ll_year = 2004, ll_month = 2, ll_rc
lds_result = create datastore
lds_result.dataobject = 'd_get_budget_info'
lds_result.SetTransObject( SQLCA)
ll_rc = lds_result.Retrieve(ll_yea r, ll_month)
MessageBox("Debug", string(ll_rc))
If IsValid(lds_host) Then Destroy lds_host
Regards.
Create procedure sp_GetMyResults
(
@aYear int,
@aMonth int
)
AS
BEGIN
declare @year smallint
declare @month smallint
declare @company_code varchar(2)
set @year=2003
set @month=10
set @company_code='02'
CREATE TABLE #budget_n_exp table
(ser_no int identity(1,1),
dept_name varchar(70),
expense numeric(10,0),
budget numeric(10,0),
mth smallint,
yr smallint,
per smallint)
while @month between 10 and 12
begin
insert #budget_n_exp table
select x.dept_name,
expense=round(sum(x.iss_qt
budget = isnull(sum(distinct budget_det.budget_qty),0),
mth=@month,
yr=@year,
per=@month-9
from
(
SELECT dept_code=issm.iss_to,
dept_name=dept.dept_name,
item_code=issd.item_code,
iss_qty = sum(issd.issue_qty),
rate =
(SELECT sum(recd.rate * recd.qty) / sum(recd.qty)
FROM str_receive_det recd,
str_receive_mst recm
WHERE ( recm.trans_no = recd.trans_no )
and ( recm.company_code = recd.company_code )
and ( recd.item_code = issd.item_code)
and ( recm.company_code = @company_code)
and ( year(recm.trans_date) <= @year)
and ( month(recm.trans_date) <=
case when year(recm.trans_date) = @year then @month else 12 end))
FROM str_issue_det issd,
str_issue_mst issm,
str_item item,
department dept
WHERE ( item.item_code = issd.item_code )
and ( issm.trans_no = issd.trans_no )
and ( issm.company_code = issd.company_code )
and ( dept.dept_code = issm.iss_to)
and ( year(issm.issue_date) = @year )
and ( month(issm.issue_date) = @month )
and ( issm.company_code = @company_code)
group by issm.iss_to,
dept.dept_name,
issd.item_code ) x,
budget_det
where x.dept_code *= budget_det.dept_code
and budget_det.year_no = @year
and budget_det.month_no = @month
group by x.dept_name
set @month=@month+1
end
set @month=1
while @month between 1 and 9
begin
insert #budget_n_exp table
select x.dept_name,
expense=round(sum(x.iss_qt
budget = isnull(sum(distinct budget_det.budget_qty),0),
mth=@month,
yr=@year+1,
per=@month
from
(
SELECT dept_code=issm.iss_to,
dept_name=dept.dept_name,
item_code=issd.item_code,
iss_qty = sum(issd.issue_qty),
rate =
(SELECT sum(recd.rate * recd.qty) / sum(recd.qty)
FROM str_receive_det recd,
str_receive_mst recm
WHERE ( recm.trans_no = recd.trans_no )
and ( recm.company_code = recd.company_code )
and ( recd.item_code = issd.item_code)
and ( recm.company_code = @company_code)
and ( year(recm.trans_date) <= @year+1)
and ( month(recm.trans_date) <=
case when year(recm.trans_date) = @year+1 then @month else 12 end))
FROM str_issue_det issd,
str_issue_mst issm,
str_item item,
department dept
WHERE ( item.item_code = issd.item_code )
and ( issm.trans_no = issd.trans_no )
and ( issm.company_code = issd.company_code )
and ( dept.dept_code = issm.iss_to)
and ( year(issm.issue_date) = @year+1 )
and ( month(issm.issue_date) = @month )
and ( issm.company_code = @company_code)
group by issm.iss_to,
dept.dept_name,
issd.item_code ) x,
budget_det
where x.dept_code *= budget_det.dept_code
and budget_det.year_no = @year+1
and budget_det.month_no = @month
group by x.dept_name
set @month=@month+1
end
select *
from #budget_n_exp table
where mth = @aMonth and
yr = @aYear
DROP TABLE #budget_n_exp table
END
2) To retrieve try:
long ll_year = 2004, ll_month = 2, ll_rc
dw_1.Retrieve(ll_year, ll_month) // dw_1 is associated with a dw created using stored proc sp_GetMyResults
OR
datastore lds_result
long ll_year = 2004, ll_month = 2, ll_rc
lds_result = create datastore
lds_result.dataobject = 'd_get_budget_info'
lds_result.SetTransObject(
ll_rc = lds_result.Retrieve(ll_yea
MessageBox("Debug", string(ll_rc))
If IsValid(lds_host) Then Destroy lds_host
Regards.
ASKER
Hi namasi:
I created the procedure spgetmyresults in sql server 2000
as u stated.
then i selected datawindow->tabular->store d_procedur e->system proedure->and selected sp_getmyresults;1
The database does not currently support retrieval of procedure text.
next->next->finish
cannot create datawindow 'arguments for select are invalid or incomplete.
I am not understanding your this part.
2) To retrieve try:
long ll_year = 2004, ll_month = 2, ll_rc
dw_1.Retrieve(ll_year, ll_month) // dw_1 is associated with a dw created using stored proc sp_GetMyResults
OR
datastore lds_result
long ll_year = 2004, ll_month = 2, ll_rc
lds_result = create datastore
lds_result.dataobject = 'd_get_budget_info'
lds_result.SetTransObject( SQLCA)
ll_rc = lds_result.Retrieve(ll_yea r, ll_month)
MessageBox("Debug", string(ll_rc))
If IsValid(lds_host) Then Destroy lds_host
why i have to say
long ll_year = 2004, ll_month = 2, ll_rc
Consider i am in preview mode and when i click rows->retrieve
the retrieval argument popup window should come where i
will give the value of year and month just as i do with other reports.
Please see my next comment also
I created the procedure spgetmyresults in sql server 2000
as u stated.
then i selected datawindow->tabular->store
The database does not currently support retrieval of procedure text.
next->next->finish
cannot create datawindow 'arguments for select are invalid or incomplete.
I am not understanding your this part.
2) To retrieve try:
long ll_year = 2004, ll_month = 2, ll_rc
dw_1.Retrieve(ll_year, ll_month) // dw_1 is associated with a dw created using stored proc sp_GetMyResults
OR
datastore lds_result
long ll_year = 2004, ll_month = 2, ll_rc
lds_result = create datastore
lds_result.dataobject = 'd_get_budget_info'
lds_result.SetTransObject(
ll_rc = lds_result.Retrieve(ll_yea
MessageBox("Debug", string(ll_rc))
If IsValid(lds_host) Then Destroy lds_host
why i have to say
long ll_year = 2004, ll_month = 2, ll_rc
Consider i am in preview mode and when i click rows->retrieve
the retrieval argument popup window should come where i
will give the value of year and month just as i do with other reports.
Please see my next comment also
ASKER
Hi namasi:
I created a sample table colour with column 'colour_code' and inserted values '01','02','03','04','05'. then in query analyzer i wrote
create FUNCTION udf_delit ()
RETURNS TABLE
AS
RETURN (SELECT * from colour )
then in powerbuilder i did
datawindow->tabular->sql select->next->cancel->desi gn->conver t to syntax->select * from udf_delit() where colour_code=:ra_code->desi gn->retrie val arguments->:ra_code (string)->next->next->fini sh
then rows->retrieve
the argument taking window popped up and i supplied '04' and ok.
and the required row is retrieved.
I want to do this way.
I created a sample table colour with column 'colour_code' and inserted values '01','02','03','04','05'. then in query analyzer i wrote
create FUNCTION udf_delit ()
RETURNS TABLE
AS
RETURN (SELECT * from colour )
then in powerbuilder i did
datawindow->tabular->sql select->next->cancel->desi
then rows->retrieve
the argument taking window popped up and i supplied '04' and ok.
and the required row is retrieved.
I want to do this way.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Mateen,
Also, make sure "manual result set" option is NOT CHECKED when associating your stored proc with the procedure.
Also, make sure "manual result set" option is NOT CHECKED when associating your stored proc with the procedure.
Mateen
This will work. But there is no need to convert SPs to functions like that. Believe me!!! :) Functions have they are own issue when you write a complicated one.
create FUNCTION udf_delit ()
RETURNS TABLE
AS
RETURN (SELECT * from colour )
go
I am sure this will work as a stored procedure, too.
CREATE Procedure sp_delit
AS
BEGIN
SELECT * from colour
END
I will run into many issues when using stored functions than stored procedures with complicated logics...
Regards.
Namasi.
This will work. But there is no need to convert SPs to functions like that. Believe me!!! :) Functions have they are own issue when you write a complicated one.
create FUNCTION udf_delit ()
RETURNS TABLE
AS
RETURN (SELECT * from colour )
go
I am sure this will work as a stored procedure, too.
CREATE Procedure sp_delit
AS
BEGIN
SELECT * from colour
END
I will run into many issues when using stored functions than stored procedures with complicated logics...
Regards.
Namasi.
This is what you can do:
1) Add thia line at the end of your stored procedure
select * from @budget_n_exp
2) Creare a datawindow with stored procedure data source. When you run "create datawindow wizard" you will see an option to select store procedure as data source for the datawindow. Name the datawindow as "d_get_budget_info"
Add two input parameter to your stored proc as follows:
Create Procedure sp_GetMyResults
(
@aYear int,
@aMonth int
)
as
Begin
-- Your Sp Code here
--End of proc
select * from @budget_n_exp
where yr = @aYear and @mth = @aMonth
end
3) Now you can use datastore to retrieve data
datastore lds_result
long ll_year = 2004, ll_month = 2, ll_rc
lds_result = create datastore
lds_result.dataobject = 'd_get_budget_info'
lds_result.SetTransObject(
ll_rc = lds_result.Retrieve(ll_yea
MessageBox("Debug", string(ll_rc))
If IsValid(lds_host) Then Destroy lds_host
To make it more efficient you can even modify the stored proc to insert data only for a given month and year.
Regards,
Namasi