跪求啦：存储过程返回的列数不确定，数据窗口应该如何以这个存储过程为数据源建立
跪求啦：存储过程返回的列数不确定，数据窗口应该怎么以这个存储过程为数据源建立？
各位大侠：
拜托，帮忙了！！！
问题如下：
首先建立测试表：
Create table T (Class varchar(2),CallDate datetime, CallCount int)
insert into T select '1 ', '2005-8-8 ',40
union all select '1 ', '2005-8-7 ',6
union all select '2 ', '2005-8-8 ',77
union all select '3 ', '2005-8-9 ',33
union all select '3 ', '2005-8-8 ',9
union all select '3 ', '2005-8-7 ',21
下面是存储过程的内容
declare @s varchar(8000)
set @s= 'select CallDate '
select @s=@s+ ',[CallCount '+Class+ ']=sum(case when Class= ' ' '+Class+ ' ' ' then CallCount else 0 end) '
from T
group by Class
set @s=@s+ ' from T group by CallDate order by CallDate desc '
exec(@s)
请问怎样建立以此存储过程为数据源的数据窗口呢？
------解决方案--------------------
动态创建数据窗口.
两个函数可实现
SyntaxFromSQL()
及
dw_1.create(...)
------解决方案--------------------
就是选stored procedure啊……
------解决方案--------------------
你把sp的语法写到Pb里就可以了。
下面是我以前做的例子。
把语法换成你的就可以了。(dw_1不用设置数据窗口对象)
ls_new_sql = "declare @sql varchar(8000) "
ls_new_sql = ls_new_sql + " set @sql = 'select t_sz_710.firm,apply_date ' "
ls_new_sql = ls_new_sql + " select @sql = @sql + ',sum(case rate_name when ' ' '+rate_name+ ' ' ' then rate else 0 end) [ '+rate_name+ '] ' "
ls_new_sql = ls_new_sql + " from (select top 99999 rate_name,max(rate) as rate from t_sz_710 group by rate_name,min_qty order by min_qty) a "
ls_new_sql = ls_new_sql + " select @sql =@sql+ ', max(cret_day) as cret_day,max(x.emp_name) as cret_pern,max(mody_day) as mody_day,max(y.emp_name) as mody_pern from t_sz_710 ,t_sz_080 x,t_Sz_080 y where t_sz_710.cret_pern *= x.emp_code and t_sz_710.mody_pern *= y.emp_code group by t_sz_710.firm,t_sz_710.apply_date order by t_sz_710.apply_date ,t_sz_710.firm ' "
ls_new_sql = ls_new_sql + " exec(@sql) "
ls_new_syntax = SQLCA.SyntaxFromSQL(ls_new_sql, 'Style(Type=grid) column(border=5 ) text(border=6) datawindow(color=80269524) ', ls_error_syntaxfromSQL)
IF Len(ls_error_syntaxfromSQL) <= 0 THEN
// Generate new DataWindow
dw_1.Create(ls_new_syntax, ls_error_create)
END IF
dw_1.SetTransObject(SQLCA)
dw_1.Retrieve()
各位大侠：
拜托，帮忙了！！！
问题如下：
首先建立测试表：
Create table T (Class varchar(2),CallDate datetime, CallCount int)
insert into T select '1 ', '2005-8-8 ',40
union all select '1 ', '2005-8-7 ',6
union all select '2 ', '2005-8-8 ',77
union all select '3 ', '2005-8-9 ',33
union all select '3 ', '2005-8-8 ',9
union all select '3 ', '2005-8-7 ',21
下面是存储过程的内容
declare @s varchar(8000)
set @s= 'select CallDate '
select @s=@s+ ',[CallCount '+Class+ ']=sum(case when Class= ' ' '+Class+ ' ' ' then CallCount else 0 end) '
from T
group by Class
set @s=@s+ ' from T group by CallDate order by CallDate desc '
exec(@s)
请问怎样建立以此存储过程为数据源的数据窗口呢？
------解决方案--------------------
动态创建数据窗口.
两个函数可实现
SyntaxFromSQL()
及
dw_1.create(...)
------解决方案--------------------
就是选stored procedure啊……
------解决方案--------------------
你把sp的语法写到Pb里就可以了。
下面是我以前做的例子。
把语法换成你的就可以了。(dw_1不用设置数据窗口对象)
ls_new_sql = "declare @sql varchar(8000) "
ls_new_sql = ls_new_sql + " set @sql = 'select t_sz_710.firm,apply_date ' "
ls_new_sql = ls_new_sql + " select @sql = @sql + ',sum(case rate_name when ' ' '+rate_name+ ' ' ' then rate else 0 end) [ '+rate_name+ '] ' "
ls_new_sql = ls_new_sql + " from (select top 99999 rate_name,max(rate) as rate from t_sz_710 group by rate_name,min_qty order by min_qty) a "
ls_new_sql = ls_new_sql + " select @sql =@sql+ ', max(cret_day) as cret_day,max(x.emp_name) as cret_pern,max(mody_day) as mody_day,max(y.emp_name) as mody_pern from t_sz_710 ,t_sz_080 x,t_Sz_080 y where t_sz_710.cret_pern *= x.emp_code and t_sz_710.mody_pern *= y.emp_code group by t_sz_710.firm,t_sz_710.apply_date order by t_sz_710.apply_date ,t_sz_710.firm ' "
ls_new_sql = ls_new_sql + " exec(@sql) "
ls_new_syntax = SQLCA.SyntaxFromSQL(ls_new_sql, 'Style(Type=grid) column(border=5 ) text(border=6) datawindow(color=80269524) ', ls_error_syntaxfromSQL)
IF Len(ls_error_syntaxfromSQL) <= 0 THEN
// Generate new DataWindow
dw_1.Create(ls_new_syntax, ls_error_create)
END IF
dw_1.SetTransObject(SQLCA)
dw_1.Retrieve()