Error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

Question

0.00/5 (No votes)

See more:

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..

SQL

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

Posted 23-May-13 0:28am

Mithlesh Shaw

Add a Solution

Comments

_Amy 24-May-13 0:54am

Can you tell me about LotNos and TeaGrade? From where it is coming?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Arun Vasu · Answer 1 · 2013-05-23T00:33:00

Solution 1

SQL

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

Posted 23-May-13 0:33am

Arun Vasu

Comments

Mithlesh Shaw 23-May-13 6:38am

I did this...

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

--)
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
end
go

bt it also not working....

Mithlesh Shaw 23-May-13 6:46am

r u there. I am waiting 4 ur reply

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 23-May-13 8:05am

Did you see any exception or Error messages now ?
If you want to reply me, then click on Reply icon at right side in this box.

Arun Vasu 24-May-13 0:58am

sorry for delay... while running the query wat error is coming? today we will fix that.. don't worry.. tell me the error.

Mithlesh Shaw · Answer 2 · 2013-05-23T18:48:00

SQL

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