一个UNION的SQL语句,帮忙

Question

如果把两行isnull(ops_indent.indenttitle,'') as 'indenttitle',isnull(ops_finance.title,'') as 'financetitle' ,
去掉就OK了

Answer 1

select ops_finance.id as objectid,ops_finance.dependon,ops_finance.companyaid,ops_finance.companybid,
isnull(ops_indent.indenttitle) as indenttitle,isnull(ops_finance.title) as financetitle ,
case sequence when 1 then amount else 0 end as before,0 as receivables,
case sequence when 2 then amount when 4 then -amount else 0 end as cash,
case sequence when 3 then -amount when 5 then amount else 0 end as back
from ops_finance 
inner join Ops_FinanceType on ops_finance.FinanceTypeID=Ops_FinanceType.id
left join ops_indent on ops_indent.id=ops_finance.id
where ops_finance.active=0unionselect Ops_Indent.id as objectid,Ops_Indent.dependon,Ops_Indent.companyaid,Ops_Indent.companybid,0 as before,amount1 as receivables,
isnull(ops_indent.indenttitle) as indenttitle,isnull(ops_finance.title) as financetitle ,
case IsCredit when 0 then amount1 when 1 then 0 end as cash,0 as back from Ops_Indent 
inner join (select indentid,sum(amount)as amount1 
from Ops_Indent_Product group by indentid) t1
on t1.indentid=Ops_Indent.id 
left join ops_finance on ops_indent.id=ops_finance.id
where (Ops_Indent.IsCredit=1 or Ops_Indent.PayTypeID is not null) and IsCredit=0

Answer 2

before,amount1 as receivables,
isnull(ops_indent.indenttitle) as indenttitle,isnull(ops_finance.title) as financetitle ,
case IsCredit when 0 then amount1 when 1 then 0 end as cash,0 as back from Ops_Indent 
inner join (select indentid,sum(amount)as amount1 
from Ops_Indent_Product group by indentid) t1
on t1.indentid=Ops_Indent.id 
left join ops_finance on ops_indent.id=ops_finance.id
where (Ops_Indent.IsCredit=1 or Ops_Indent.PayTypeID is not null) and IsCredit=0

Answer 3

试试：select ops_finance.id as objectid,ops_finance.dependon,ops_finance.companyaid,ops_finance.companybid,
isnull(ops_indent.indenttitle) as indenttitle,isnull(ops_finance.title) as financetitle ,
case sequence when 1 then amount else 0 end as before,0 as receivables,
case sequence when 2 then amount when 4 then -amount else 0 end as cash,
case sequence when 3 then -amount when 5 then amount else 0 end as back
from ops_finance 
inner join Ops_FinanceType on ops_finance.FinanceTypeID=Ops_FinanceType.id
left join ops_indent on ops_indent.id=ops_finance.id
where ops_finance.active=0unionselect Ops_Indent.id as objectid,Ops_Indent.dependon,Ops_Indent.companyaid,Ops_Indent.companybid,0 as before,amount1 as receivables,
isnull(ops_indent.indenttitle) as indenttitle,isnull(ops_finance.title) as financetitle ,
case IsCredit when 0 then amount1 when 1 then 0 end as cash,0 as back from Ops_Indent 
inner join (select indentid,sum(amount)as amount1 
from Ops_Indent_Product group by indentid) t1
on t1.indentid=Ops_Indent.id 
left join ops_finance on ops_indent.id=ops_finance.id
where (Ops_Indent.IsCredit=1 or Ops_Indent.PayTypeID is not null) and IsCredit=0

Answer 4

isnull(ops_indent.indenttitle) as indenttitle 这样不行的
isnull(ops_indent.indenttitle，＇＇) as indenttitle 得这样啊

Answer 5

select ops_finance.id as objectid,ops_finance.dependon,ops_finance.companyaid,ops_finance.companybid,
ops_indent.indenttitle,ops_finance.title,
case sequence when 1 then amount else 0 end as before,0 as receivables,
case sequence when 2 then amount when 4 then -amount else 0 end as cash,
case sequence when 3 then -amount when 5 then amount else 0 end as back
from ops_finance 
inner join Ops_FinanceType on ops_finance.FinanceTypeID=Ops_FinanceType.id
left join ops_indent on ops_indent.id=ops_finance.id
where ops_finance.active=0unionselect Ops_Indent.id as objectid,Ops_Indent.dependon,Ops_Indent.companyaid,Ops_Indent.companybid,0 as before,amount1 as receivables,
ops_indent.indenttitle,ops_finance.title,
case IsCredit when 0 then amount1 when 1 then 0 end as cash,0 as back from Ops_Indent 
inner join (select indentid,sum(amount)as amount1 
from Ops_Indent_Product group by indentid) t1
on t1.indentid=Ops_Indent.id 
left join ops_finance on ops_indent.id=ops_finance.id
where (Ops_Indent.IsCredit=1 or Ops_Indent.PayTypeID is not null) and IsCredit=0
就算这样
改成ops_indent.indenttitle,ops_finance.title,还是报同样的错误

Answer 6

１．给出测试数据２．写成这样：
　　select a.*,b.* from table1 a,table2 b where a.id=b.id

Answer 7

3 id uniqueidentifier 16 0
0 Title nvarchar 64 0
0 TitleEn varchar 64 1
0 FinanceCode varchar 32 0
0 DependOn nvarchar 10 1
0 FinanceTypeID uniqueidentifier 16 0
0 IndentID uniqueidentifier 16 1
0 PurchaseID uniqueidentifier 16 1
0 CompanyAID uniqueidentifier 16 0
0 CompanyBID uniqueidentifier 16 0
0 OperateID uniqueidentifier 16 0
0 EmployeeID uniqueidentifier 16 1
0 PayTypeID uniqueidentifier 16 0
0 Amount decimal 9 1
0 Discount int 4 1
0 FactAmount decimal 9 1
0 OwnerID uniqueidentifier 16 1
0 Note ntext 16 1
0 NoteEn text 16 1
0 BuildOn datetime 8 1
0 ModifyOn datetime 8 1
0 BuildEmployeeid uniqueidentifier 16 1
0 ModifyEmployeeid uniqueidentifier 16 1
0 Active bit 1 1

Answer 8

错了，上面是ops_finance 
下面是ops_indent3 id uniqueidentifier 16 0
0 IndentTitle nvarchar 32 0
0 IndentTitleEn varchar 32 1
0 IndentCode varchar 64 0
0 LeadID uniqueidentifier 16 1
0 FinanceID uniqueidentifier 16 1
0 FinanceAmount decimal 9 1
0 ShippingMethodID uniqueidentifier 16 1
0 StockID uniqueidentifier 16 1
0 DependOn varchar 10 0
0 BalanceOn varchar 10 1
0 IsCredit bit 1 1
0 PayTypeID uniqueidentifier 16 1
0 ReceiptNo nvarchar 64 1
0 CompanyAID uniqueidentifier 16 0
0 CompanyBID uniqueidentifier 16 1
0 StorageAID uniqueidentifier 16 1
0 StorageBID uniqueidentifier 16 1
0 OperateID uniqueidentifier 16 1
0 EmployeeID uniqueidentifier 16 1
0 OwnerID uniqueidentifier 16 1
0 IndentTypeID uniqueidentifier 16 1
0 IsFreeze bit 1 1
0 FreezeWhy ntext 16 1
0 FreezeWhyEn text 16 1
0 FreezeDate varchar 10 1
0 BuildOn datetime 8 1
0 ModifyOn datetime 8 1
0 BuildEmployeeid uniqueidentifier 16 1
0 ModifyEmployeeid uniqueidentifier 16 1
0 Active bit 1 1
0 PurchaseID uniqueidentifier 16 1
0 IsChannel bit 1 1
0 Note ntext 16 1
0 NoteEn text 16 1

Answer 9

try:isnull(ops_indent.indenttitle，0) as indenttitle

Answer 10

你的ops_indent.indenttitle和ops_finance.title这两个字段的数据类型一定不一样对吧，肯定一个是nvarchar型的，一个是int型的。
假设ops_indent.indenttitle是int型的，而ops_finance.title是nvarchar型的，如果ops_indent.indenttitle没有为空的值，就会出现错误提示：‘将数据类型 nvarchar 转换为 numeric 时出错。’

Answer 11

用convert函数转换一下就ok，统一类型，例如convert(nvarchar(30),ops_indent.indenttitle),将int型的数值转换成nvarchar型。