Home » Visual StudioRSS

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)

AS 

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

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter