Visual Studio Answers

Home » Visual Studio	RSS
Only one expression can be specified in the select list when the subquery is not introduced with EXI

Table

CREATETABLE [dbo].[Consignment](

[ConsignmentID] [numeric](13, 0)IDENTITY(1,1)NOTNULL,

[BookingPlace] [numeric](13, 0)NULL,

[GcNoteNo] [varchar](15),

[BookingDate] [datetime] NULL,

[VehicleNo] [varchar](20),

[NoOfPackages] [numeric](13, 0)NULL,

[ChargeWT] [numeric](13, 2)NULL,

[FreightAmt] [numeric](13, 2)NULL,

[ServiceCharge] [numeric](13, 2)NULL,

[GrandTotal] [numeric](13, 2)NULL,

)

Insert Statements

insertinto Consignment values(1,12,'2010-07-01 00:00:00.000','AP111',5090,1050,2310,333,4856)

insertinto Consignment values(1,12,'2010-07-15 00:00:00.000','AP258',1500,1007,230,3733,456)

insertinto Consignment values(1,12,'2010-07-30 00:00:00.000','AP137',5000,100,1230,333,456)

insertinto Consignment values(2,12,'2010-07-01 00:00:00.000','AP154',5008,1000,230,333,456)

insertinto Consignment values(2,12,'2010-07-15 00:00:00.000','AP453',5000,100,2310,333,4856)

insertinto Consignment values(2,12,'2010-07-30 00:00:00.000','AP837',5090,1050,230,3733,456)

insertinto Consignment values(3,12,'2010-07-01 00:00:00.000','AP344',5008,1007,1230,333,456)

insertinto Consignment values(3,12,'2010-07-02 00:00:00.000','AP783',1500,1000,230,333,456)

insertinto Consignment values(3,12,'2010-07-11 00:00:00.000','AP435',5000,100,230,333,4856)

insertinto Consignment values(4,12,'2010-07-01 00:00:00.000','AP134',5090,1050,2310,3733,456)

insertinto Consignment values(4,12,'2010-07-20 00:00:00.000','AP178',5008,1007,1230,333,456)

insertinto Consignment values(4,12,'2010-07-06 00:00:00.000','AP101',1500,1000,230,333,456)

insertinto Consignment values(5,12,'2010-07-01 00:00:00.000','AP186',5000,1050,2310,333,4856)

PROCEDURE

CREATEPROC [dbo].[usp_BookingStatementCode_Search]

@FromDate DATETIME

,@ToDate DATETIME

,@FromPlace VARCHAR(60)

,@ToPalce VARCHAR(60)

BEGIN

SETNOCOUNTON

SELECT

GCNOTENO

,Convert(VARCHAR(10), BookingDate,103)AS BookingDate

,VehicleNo

,NoOfPackages

,ChargeWT

,FreightAmt

,ServiceCharge as other

,GrandTotal

,(SELECT BookingPlace,SUM(NoOfPackages)from Consignment WHERE BookingDate BETWEEN @FromDate AND @ToDate GroupBy BookingPlace)As GroupNoOfPackages

,(SELECT BookingPlace,SUM(ChargeWT)from Consignment WHERE BookingDate BETWEEN @FromDate AND @ToDate GroupBy BookingPlace)As GroupChargeWT

,(SELECT BookingPlace,SUM(FreightAmt)from Consignment WHERE BookingDate BETWEEN @FromDate AND @ToDate GroupBy BookingPlace)as GroupFreightAmt

,(SELECT BookingPlace,SUM(ServiceCharge)from Consignment WHERE BookingDate BETWEEN @FromDate AND @ToDate GroupBy BookingPlace)as GroupOther

,(SELECT BookingPlace,SUM(GrandTotal)from Consignment WHERE BookingDate BETWEEN @FromDate AND @ToDate GroupBy BookingPlace)as GroupGrandTotal

FROM Consignment C

WHERE C.BookingDate BETWEEN @FromDate AND @ToDate

ORDER BY BookingPlace

SET NOCOUNTOFF

END

4 Answers Found

Answer 1

The problem is that your subqueries are returning more than 1 row of data.

Try this query

SELECT  

     GCNOTENO  

     ,Convert(VARCHAR(10), BookingDate,103) AS BookingDate  

     ,VehicleNo  

     ,NoOfPackages  

     ,ChargeWT  

     ,FreightAmt  

     ,ServiceCharge as other  

     ,GrandTotal
     , SUM(NoOfPackages)
     ,SUM(ChargeWT) 
     ,SUM(FreightAmt)
     ,SUM(ServiceCharge)
     ,SUM(GrandTotal)
     ,bookingplace

     

    FROM Consignment C  

    WHERE C.BookingDate BETWEEN'2010-07-01 00:00:00.000'AND'2010-07-30 00:00:00.000'groupby bookingdate,VehicleNo  

     ,NoOfPackages  

     ,ChargeWT  

     ,FreightAmt  

     ,ServiceCharge, GCNOTENO ,grandtotal,bookingplace

    ORDERBY BookingPlace

Answer 2

Hi,

Axel8s are right, this is the part of the SQL statement that gives you an error:

 ,(SELECT BookingPlace,SUM(ChargeWT) from Consignment WHERE BookingDate BETWEEN @FromDate AND @ToDate GroupBy BookingPlace) As GroupChargeWT

In a sub select, you can only specify one column, like this:

 ,(SELECTSUM(ChargeWT) from Consignment WHERE BookingDate BETWEEN @FromDate AND @ToDate) As GroupChargeWT

I have removed the GROUP BY, since this will not give a correct result now, Axel8s statement are correct. But for your information later on, I changed the SQL sub select to show you how it can look in another scenario.

Good luck!

Answer 3

Hi,

Thanks for your help. Even this solved my problem

CREATEPROC [dbo].[usp_BookingStatementCode_Search]  
	 @FromDate DATETIME  
	 ,@ToDate DATETIME  
	 ,@FromPlace VARCHAR(60)  
	 ,@ToPalce VARCHAR(60)
ASBEGINSETNOCOUNTONSELECT  
	  GCNOTENO  
	  ,Convert(VARCHAR(10), BookingDate,103) AS BookingDate  
	  ,NoOfPackages  
	  ,ChargeWT  
	  ,FreightAmt  
	  ,ServiceCharge  
	  ,GrandTotal
    ,BookingPlace
	  ,(SELECTSUM(NoOfPackages) from Consignment WHERE BookingDate BETWEEN @FromDate AND @ToDate AND BookingPlace = C.BOOKINGPLACE)As GroupNoOfPackages
	  ,(SELECTSUM(ChargeWT) from Consignment WHERE BookingDate BETWEEN @FromDate AND @ToDate AND BookingPlace = C.BOOKINGPLACE) As GroupChargeWT
	  ,(SELECTSUM(FreightAmt) from Consignment WHERE BookingDate BETWEEN @FromDate AND @ToDate AND BookingPlace = C.BOOKINGPLACE)as GroupFreightAmt
	  ,(SELECTSUM(ServiceCharge) from Consignment WHERE BookingDate BETWEEN @FromDate AND @ToDate AND BookingPlace = C.BOOKINGPLACE) as GroupOther
	  ,(SELECTSUM(GrandTotal) from Consignment WHERE BookingDate BETWEEN @FromDate AND @ToDate AND BookingPlace = C.BOOKINGPLACE) as GroupGrandTotal
	 FROM Consignment C  
	 WHERE C.BookingDate BETWEEN @FromDate AND @ToDate 
	 ORDERBY BookingPlace
	 SETNOCOUNTOFFEND

Answer 4

And I don´t see the need of the complicated subqueries where all could be done within one query only (as already mentioned). You should compare the execution plans with each other to see which performance benefit you get.

-Jens

Search for Only one expression can be specified in the select list when the subquery is not introduced with EXI

Related Answers:

<< Previous Next >>

Microsoft | Windows | Visual Studio | Follow us on Twitter