表1:
员工 项(xmid) 工资
1 1 2000
1 2 100
2 1 3000
3 1 1500
3 2 300
2 2 400
表2:项目表
xmID 项目名
1 基本工资
2 电费
3 应发工资生成如下表3:员工 基本工资 电费 应发工资
1 2000 100
2 3000 400
3 1500 300SQL要什么写呢下面是,xmid值是已知的简单的转换SQL语句
那未知的要怎么写呢select PU_ID as 姓名,
sum(decode(xmid,1, xms)) xmid_1,
sum(decode(xmid,2, xms)) xmid_2,
sum(decode(xmid,3, xms)) xmid_3
from gzjj
group by pu_id;
员工 项(xmid) 工资
1 1 2000
1 2 100
2 1 3000
3 1 1500
3 2 300
2 2 400
表2:项目表
xmID 项目名
1 基本工资
2 电费
3 应发工资生成如下表3:员工 基本工资 电费 应发工资
1 2000 100
2 3000 400
3 1500 300SQL要什么写呢下面是,xmid值是已知的简单的转换SQL语句
那未知的要怎么写呢select PU_ID as 姓名,
sum(decode(xmid,1, xms)) xmid_1,
sum(decode(xmid,2, xms)) xmid_2,
sum(decode(xmid,3, xms)) xmid_3
from gzjj
group by pu_id;
create or replace procedure Proc_aa
as
declear
strSql varchar2(100):='select PU_ID as 姓名';
cur_xm cursor is select xmid from xmb group by xmid;
begin
open cur_xm;
foreach xm in cur_xm loop
strSql:=strSql||', sum(decode(xmid,'||xm.xmid||',xms)) xmid_'||xm.xmid;
end loop;
strSql:=strSql+' from gzjj group by pu_id ';
execute immediacy strSql;
close cur_xm;
end Pro_aa
剛學,可能有誤!!
---------- ---------- ----------
1 1 2000
1 2 100
2 1 3000
3 1 1500
3 2 300
2 2 4006 rows selectedSQL> SELECT * FROM ITEM_TAB; ITEM_ID ITEM_NAME
---------- --------------------
1 基本工资
2 电费
3 应发工资SQL>
SQL> SELECT EMP_ID,
2 SUM(DECODE(ITEM_NAME,'基本工资',SALAY_NUM)) "基本工资",
3 SUM(DECODE(ITEM_NAME,'电费',SALAY_NUM)) "电费",
4 SUM(DECODE(ITEM_NAME,'基本工资',SALAY_NUM))+SUM(DECODE(ITEM_NAME,'电费',SALAY_NUM)) "应发工资"
5 FROM (
6 SELECT A.EMP_ID,B.ITEM_NAME,A.SALAY_NUM
7 FROM SALAY_TAB A,ITEM_TAB B
8 WHERE A.SALAY_ID=B.ITEM_ID
9 )
10 GROUP BY EMP_ID
11 / EMP_ID 基本工资 电费 应发工资
---------- ---------- ---------- ----------
1 2000 100 2100
2 3000 400 3400
3 1500 300 1800
as
xm gzjj.xmid%type;
strSql varchar2(100):='select PU_ID as 姓名';
cursor cur_xm is select xmid from xmb group by xmid;
begin
open cur_xm;
loop
fetch cur_xm into xm;
exit when cur_aaa%notfound;
strSql:=strSql||', sum(decode(xmid,'||xm.xmid||',xms)) xmid_'||xm.xmid;
end loop;
strSql:=strSql+' from gzjj group by pu_id ';
execute immediacy strSql;
close cur_xm;
end Pro_aa execute immediacy strSql;这句过不了
会出现Compilation errors for PROCEDURE GZXT.PROC_AAError: PLS-00103: 出现符号 "IMMEDIACY"在需要下列之一时:
:= . ( @ % ;
immediate
Line: 14
Text: execute immediacy strSql;Error: PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
;
符号 ";" 被替换为 "end-of-file" 后继续。
Line: 17
public DataSet Bill_Point_No(string CUSTOMER_ID)//ok
{
DataSet dataset = new DataSet();
Hashtable HT=new Hashtable();
HT.Add("P_CUSTOMER_ID",CUSTOMER_ID);
if(RunProcedure("Re_CURSOR",OracleType.Cursor,ref dataset,HT,bmsOracleUser+".YY_PKG_BILL_POINT_NO.BILL_POINT_NO",bmsOracleConnectionString))
{
;
}
else
{
dataset=null;
}
return dataset;
}
public bool RunProcedure(string ReturnParameter,OracleType ParamType,ref DataSet Dataset,Hashtable HT ,string ProcedureName,string OracleConnection)
{
System.Data.OracleClient.OracleConnection dsconnection = new System.Data.OracleClient.OracleConnection(OracleConnection);
System.Data.OracleClient.OracleCommand dacommand = new System.Data.OracleClient.OracleCommand(ProcedureName,dsconnection);
dsconnection.Open();
dacommand.CommandType=CommandType.StoredProcedure;
IDictionaryEnumerator Enumerator;
Enumerator = HT.GetEnumerator();
object Value=null;
OracleParameter OracleParam;
OracleParam = dacommand.Parameters.Add(new OracleParameter(ReturnParameter,ParamType));
OracleParam.Direction = ParameterDirection.Output;
while(Enumerator.MoveNext())
{
Value = Enumerator.Value;
OracleParam=dacommand.Parameters.Add(new OracleParameter(Enumerator.Key.ToString(), Value));
}
OracleDataAdapter ODAdapter=new OracleDataAdapter(dacommand);
try
{
ODAdapter.Fill(Dataset);
return true;
}
catch(System.Exception e)
{
e.ToString();
return false;
}
finally
{
HT.Clear();
dacommand.Parameters.Clear();
dsconnection.Close();
}
}
这个为什么偶试都没动静呢?是这样调用的吗
create or replace procedure Proc_aa(Re_CURSOR OUT pkg_cursor.MY_CURSOR)
is
Str_xm xmgl.xid%type;
strSql varchar2(2500):='select pu_id';
cursor cur_xm is select xid from xmgl group by xid;
V_CURSOR pkg_cursor.MY_CURSOR;
begin
open cur_xm;
loop
fetch cur_xm into Str_xm;
exit when cur_xm%notfound;
strSql:=strSql||', sum(decode(xmid,'||Str_xm||',xms)) as xmid_'||Str_xm;
end loop;
strSql:=strSql||' from gzjj group by pu_id';
OPEN V_CURSOR FOR strSql; Re_CURSOR := V_CURSOR;
close cur_xm;
end;.net调用可显示出来,可加到水晶报表就不行,为啥
from
(select distinct 员工 from 表1) A
,表1 B
,表1 C
,表1 D
where A.员工=B.员工(+)
and A.员工=C.员工(+)
and A.员工=D.员工(+)
and B.xmID=1
and C.xmID=2
and D.xmID=3
{
string connectionString ="User ID=GZXT;Password=654123;Data Source=GZXT";
string queryString = "Proc_aa"; //注意调用方法
OracleConnection cn = new OracleConnection(connectionString);
OracleCommand cmd = new OracleCommand(queryString,cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText="Proc_aa";
cmd.Parameters.Add("Re_CURSOR",OracleType.Cursor); //注意这里的类型
cmd.Parameters["Re_CURSOR"].Direction = ParameterDirection.Output;
try
{
cn.Open();
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds,"Proc_aa");
cn.Close();
this.DataGrid1.DataSource=ds.Tables["Proc_aa"];
this.DataGrid1.DataBind();
//报表 report.Report1 CRV_2=new GZXT.report.Report1();
string []field={"BILL_POINT_NO"};
TextObject[] txt=new TextObject[2];
//实例化报表中的自定义公式
FormulaFieldDefinition[] field_1=new FormulaFieldDefinition[3];
//实例化报表中的自定义公式(对详细资料中的字段进行汇总)
for(int i=1;i<=2;i++)
{
//为实例化的报表文本与报表中的文本对象相对应
txt[i-1]=(TextObject)CRV_2.ReportDefinition.ReportObjects["text"+Convert.ToString (i)];
//为实例化的自定义公式与报表中的自定义公式相对应
field_1[i-1]=CRV_2.DataDefinition.FormulaFields["field_1"+Convert.ToString (i)]; //初始设置所有报表中的所有对象为不显示
//CRV_2.ReportDefinition.ReportObjects["field_1"+Convert.ToString (i)].ObjectFormat .EnableSuppress = true; CRV_2.ReportDefinition.ReportObjects["field1"+Convert.ToString (i)+"1"].ObjectFormat .EnableSuppress = true;
CRV_2.ReportDefinition.ReportObjects["ln1"+Convert.ToString(i)].ObjectFormat .EnableSuppress =true;
}
for(int i=0;i<field.Length;i++)
{
//为要显示的自定义的文本对象赋值(即同时给报表中的文本对象赋值)
txt[i].Text="{BILL_POINT_NO1."+field[i] +"}"; //为要显示的自定义的公式赋值(即同时给报表中的自定义公式赋值)
field_1[i].Text="{BILL_POINT_NO1."+field[i] +"}";
//赋值过的对象(要在报表中显示的字段)让它们显示
CRV_2.ReportDefinition.ReportObjects["text"+Convert.ToString(i+1)].ObjectFormat.EnableSuppress=false; CRV_2.ReportDefinition.ReportObjects["field1"+Convert.ToString(i+1)+"1"].ObjectFormat.EnableSuppress=false;
CRV_2.ReportDefinition.ReportObjects["ln1"+Convert.ToString(i+1)].ObjectFormat.EnableSuppress =false;
} //对一些始终显示的文本的位置进行定位
// CRV_2.ReportDefinition.ReportObjects["tot1181"].Left=CRV_2.ReportDefinition.ReportObjects["tot1181"].ObjectFormat.EnableSuppress =false;
// CRV_2.ReportDefinition.ReportObjects["tot1181"].Left=CRV_2.ReportDefinition.ReportObjects["tot2181"].ObjectFormat.EnableSuppress =false;
//文本及字段的移动成功(下面是对一些线作出必要的调整)
//// lbltmp.Text="<script>var bl;var kd; var zs;var je;var t_kd; bl=parseInt(ln0.style.left);kd=1590*bl/1845;t_kd=240*bl/1845;zs=470*bl/1845; je=1120*bl/1845; ln_hj1.style.left=bl+zs+"+(field.Length-1)+"*kd;ln_hj2.style.left=bl+"+(field.Length)+"*kd;lntop1.style.width=t_kd+"+(field.Length)+"*kd;lntop2.style.width="+(field.Length)+"*kd;lntop3.style.width=t_kd+"+(field.Length)+"*kd;lntop5.style.width=t_kd+"+(field.Length)+"*kd;for(var i=0;i<"+sqldst.Tables[0].Rows.Count+";i++)lntop4[i].style.width=t_kd+"+(field.Length)+"*kd;</script>";
//
CRV_2.SetDataSource(ds.Tables["BILL_POINT_NO1"]);
this.CRV_1.ReportSource =CRV_2; }
//
catch( OracleException ex ) { Console.WriteLine("Exception occurred!"); Console.WriteLine("The exception message is:{0}",ex.Message.ToString()); } finally { Console.WriteLine("------------------End-------------------"); }
}
结果赋给datagrid1查显示,给水晶报表就不行,怎么处理呢