错误：当EXISTS未引入子查询时，只能在选择列表中指定一个表达式 [英] Error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

问题描述

我写了一个查询。它工作正常。 Bt现在我想使用该查询创建一个存储过程并获得类似的错误---当子查询没有用EXISTS引入时，只能在选择列表中指定一个表达式

我的代码是..

  create   procedure  [dbo]。[spCatalogPrinting] 
 < span class =code-keyword> AS  
 开始 
   -   从tempCataloguePrinting1中删除 
   -   插入tempCataloguePrinting（RefNo，InvoiceNo，Grade，Pkgs，Kg，DespDate，ArrivedAndLiftedOn，LotNo，Val，SoldAt） 
   -    insert into tempCataloguePrinting1（InvoiceNo） 
   -    insert into tempCataloguePrinting（引用号，InvoiceNo，等级，PKGS，KG，DespDate，ArrivedAndLif tedOn，LotNo，Val，SoldAt，SaleNo，Valuation，Remarks） 
 选择 
（
 选择 ls.LotNo，sm.InvoiceNo，i.ItemName，
 sm.PackingQuantityPcs，sm.GrossQuantityKgs，ih.BillDate，gi.ArrivalDate，
 cl.CatalogueLotNo ，sm.ApproxRate，sm.Rate，dbo.GetCatalogueNo（ls.LotNo），'  ss'， '  ss' 来自 StockMovement sm 
 内部  join  CatalogLine cl  on  cl .Oid = sm.Oid 
 内部  join 项目i  on  i.Oid = TeaGrade 
  inner   join  LocationStatusWiseStock ls  ls上的class =code-keyword>。 Oid = LotNos 
 内部  join  GardenInvoice gi  on  gi.Oid = ls.InvoiceNo 
  inner   join  InvoiceHeader ih  on  ih.Oid = gi.Oid 
  inner   join 目录c  on  cl.Catalog = c.Oid 
  inner   join  InvoiceHeader ihh  on  ihh.Oid = c.Oid 
）
  end  
  go  

解决方案

< blockquote>

 从中选择 * （
 
 选择 ls.LotNo，sm.InvoiceNo，i.ItemName，
 sm.PackingQuantityPc s，sm.GrossQuantityKgs，ih.BillDate，gi.ArrivalDate，
 cl.CatalogueLotNo，sm.ApproxRate，sm.Rate，dbo.GetCatalogueNo（ls.LotNo），'   ss'，'  ss' 来自 StockMovement sm 
 内部  join  CatalogLine cl  on  cl.Oid = sm.Oid 
  inner   join 项目i   i.Oid = TeaGrade 
  inner   join  LocationStatusWiseStock ls  on  ls.Oid = LotNos 
 内部 加入 GardenInvoice gi  on  gi.Oid = ls.InvoiceNo 
 内部 加入 InvoiceHeader ih   ih.Oid = gi .Oid 
 内部  join 目录c  on  cl.Catalog = c.Oid 
  inner   join  InvoiceHeader ihh   ihh.Oid = c.Oid 
）x 

尝试这样..如果它不起作用。请告诉我

  alter   procedure  [dbo]。[spCatalogPrinting] 
  AS  
 开始 
 删除 来自 tempCataloguePrinting 
 
 将 插入 tempCataloguePrinting 
 选择 ROW_NUMBER（） OVER （ ORDER   BY  i.ItemName） As  Row，ls.LotNo，sm.InvoiceNo，i.ItemName，
 sm.PackingQuantityPcs，sm.GrossQuantityKgs，ih.BillDate，gi.ArrivalDate，
 cl.CatalogueLotNo ，sm.ApproxRate，sm.Rate，dbo.GetCatalogueNo（ls.LotNo），dbo.Valuation（ls.LotNo），'  StockMovement获取> 
  sm 
  inner   join  CatalogLine cl  on  cl.Oid = sm.Oid 
 内部 加入项目i   i.Oid = TeaGrade 
 内部  join  LocationStatusWiseStock ls  on  ls.Oid = LotNos 
 内部 加入 GardenInvoice gi   gi.Oid = ls.InvoiceNo 
  inner   join  InvoiceHeader ih  on  ih.Oid = gi.Oid 
  inner   join 目录c   cl.Catalog = c.Oid 
  inner   join  InvoiceHeader ihh  on  ihh.Oid = c.Oid 
  end  
  go  

I have written a query. it works fine. Bt now I want to create a store procedure using that query and getting error like---Only one expression can be specified in the select list when the subquery is not introduced with EXISTS
my code is..

create procedure [dbo].[spCatalogPrinting] 
AS
Begin
	--Delete from tempCataloguePrinting1
	--insert into tempCataloguePrinting(RefNo,InvoiceNo,Grade,Pkgs,Kg,DespDate,ArrivedAndLiftedOn,LotNo,Val,SoldAt)
	--insert into tempCataloguePrinting1(InvoiceNo)
	--insert into tempCataloguePrinting(RefNo,InvoiceNo,Grade,Pkgs,Kg,DespDate,ArrivedAndLiftedOn,LotNo,Val,SoldAt,SaleNo,Valuation,Remarks)
	select 
	(
		select ls.LotNo,sm.InvoiceNo,i.ItemName,
		sm.PackingQuantityPcs,sm.GrossQuantityKgs,ih.BillDate,gi.ArrivalDate,
		cl.CatalogueLotNo,sm.ApproxRate,sm.Rate,dbo.GetCatalogueNo(ls.LotNo),'ss','ss' from StockMovement sm		
		inner join CatalogLine cl on cl.Oid= sm.Oid
		inner join Item i on i.Oid = TeaGrade
		inner join LocationStatusWiseStock ls on ls.Oid=LotNos
		inner join GardenInvoice gi on gi.Oid=ls.InvoiceNo
		inner join InvoiceHeader ih on ih.Oid=gi.Oid
		inner join Catalog c on cl.Catalog=c.Oid
		inner join InvoiceHeader ihh on ihh.Oid=c.Oid	
	)
end
go

解决方案

select * from(

select ls.LotNo,sm.InvoiceNo,i.ItemName,
        sm.PackingQuantityPcs,sm.GrossQuantityKgs,ih.BillDate,gi.ArrivalDate,
        cl.CatalogueLotNo,sm.ApproxRate,sm.Rate,dbo.GetCatalogueNo(ls.LotNo),'ss','ss' from StockMovement sm
        inner join CatalogLine cl on cl.Oid= sm.Oid
        inner join Item i on i.Oid = TeaGrade
        inner join LocationStatusWiseStock ls on ls.Oid=LotNos
        inner join GardenInvoice gi on gi.Oid=ls.InvoiceNo
        inner join InvoiceHeader ih on ih.Oid=gi.Oid
        inner join Catalog c on cl.Catalog=c.Oid
        inner join InvoiceHeader ihh on ihh.Oid=c.Oid
)x

try like this.. if its not work. plz let me know

alter procedure [dbo].[spCatalogPrinting]
AS
Begin
    Delete from tempCataloguePrinting

    Insert into tempCataloguePrinting
        select  ROW_NUMBER()OVER (ORDER BY i.ItemName) As  Row,ls.LotNo,sm.InvoiceNo,i.ItemName,
        sm.PackingQuantityPcs,sm.GrossQuantityKgs,ih.BillDate,gi.ArrivalDate,
        cl.CatalogueLotNo,sm.ApproxRate,sm.Rate,dbo.GetCatalogueNo(ls.LotNo),dbo.Valuation(ls.LotNo),''
        from StockMovement sm
        inner join CatalogLine cl on cl.Oid= sm.Oid
        inner join Item i on i.Oid = TeaGrade
        inner join LocationStatusWiseStock ls on ls.Oid=LotNos
        inner join GardenInvoice gi on gi.Oid=ls.InvoiceNo
        inner join InvoiceHeader ih on ih.Oid=gi.Oid
        inner join Catalog c on cl.Catalog=c.Oid
        inner join InvoiceHeader ihh on ihh.Oid=c.Oid
end
go

这篇关于错误：当EXISTS未引入子查询时，只能在选择列表中指定一个表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！