Subquery is not introduced with EXISTS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Subquery is not introduced with EXISTS

Author

Topic

pmpjr
Starting Member

4 Posts

Posted - 2004-07-30 : 15:53:50

Can anyone help me on this?

When I try to save this stored procedure to SQL Server 2000 database, I keep getting the following error and have no idea how to correct it: "ADO error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS"

ALTER PROCEDURE dbo.sProcRevenueForTerminalByAcocuntManager
(
@fromDate datetime,
@toDate datetime
)

AS
SET NOCOUNT ON
RETURN
(
SELECT TOP 100 PERCENT
T2.TerminalCode AS GroupName1,
T3.TerminalName AS GroupName2,
T2.AccountManagerName AS DetailName,
SUM(T1.InvoiceDEDTotalAmount) AS DetailRevenue
FROM TBLINVOICEDED T1
INNER JOIN TBLCUSTOMERS T2 ON T1.CustomerId = T2.CustomerId
INNER JOIN TBLTERMINALDATA T3 ON T2.TerminalCode = T3.TerminalCode
WHERE (T1.InvoiceDEDDate >= @fromDate)
AND (T1.InvoiceDEDDate <= @toDate)
GROUP BY T2.TerminalCode, T3.TerminalName, T2.AccountManagerName
HAVING (NOT (SUM(T1.InvoiceDEDTotalAmount) IS NULL))
ORDER BY T2.TerminalCode, T2.AccountManagerName )

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-30 : 16:00:50

ADO error? Aren't you executing this through Query Analyzer?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-30 : 16:04:41

Never mind that...what's with the RETURN?

Brett

8-)

EDIT: And you don't need this either

HAVING (NOT (SUM(T1.InvoiceDEDTotalAmount) IS NULL))

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-30 : 16:08:45

Didn't see the RETURN. That's very odd. Is this SQL Server?

Tara

pmpjr
Starting Member

4 Posts

Posted - 2004-07-30 : 16:24:16

Okay, okay, so I am new at this.
In Visual Studio.NET there is a wizard for creating a New Stored Procedure for the connected SQL Server 2000. It gives one the following:
CREATE PROCEDURE dbo.StoredProcedure1
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
AS
/* SET NOCOUNT ON */
RETURN

And so that is where the RETURN comes from!
Below that one types in SELECT and right clicks inside the SELECT block to Insert SQL or use the Design SQL Block utility. When I eliminated the RETURN things were okay.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-30 : 16:26:15

CREATE PROCEDURE dbo.StoredProcedure1
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)

AS

SET NOCOUNT ON

queries go here

RETURN

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-30 : 16:32:41

How about



CREATE PROCEDURE dbo.sProcRevenueForTerminalByAcocuntManager
	  @fromDate datetime
	, @toDate datetime
AS

SET NOCOUNT ON

    SELECT T2.TerminalCode AS GroupName1, 
	 , T3.TerminalName AS GroupName2, 
	 , T2.AccountManagerName AS DetailName, 
	 , SUM(T1.InvoiceDEDTotalAmount) AS DetailRevenue
      FROM TBLINVOICEDED T1 
INNER JOIN TBLCUSTOMERS T2 
	ON T1.CustomerId = T2.CustomerId 
INNER JOIN TBLTERMINALDATA T3 
	ON T2.TerminalCode = T3.TerminalCode
     WHERE T1.InvoiceDEDDate >= @fromDate 
       AND T1.InvoiceDEDDate <= @toDate
  GROUP BY T2.TerminalCode, T3.TerminalName, T2.AccountManagerName
  ORDER BY T2.TerminalCode, T2.AccountManagerName

RETURN

Brett

8-)

pmpjr
Starting Member

4 Posts

Posted - 2004-07-30 : 16:44:46

That is the cleanest way. Thank you.

Consider that in a multiuser environment an invoice may be in process and saved in the invoice table with no $ amounts, thus the HAVING line keeps NULL computations from being returned.

pmpjr
Starting Member

4 Posts

Posted - 2004-07-30 : 16:47:04

The TOP 100 PERCENT was a redundancy on my part. Doesn't this make things clearer or am I being too verbose about this?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-07-30 : 18:56:00

I would leave out the TOP 100 PERCENT. I see no need for it.

And if you want to exclude rows with no dollar amounts, then why not put that in the WHERE clause instead of the HAVING? HAVING does not get processed until after the aggregation occurs, but the WHERE gets processed before.

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-30 : 18:57:57

TOP 100 PERCENT is used in views when you want to include an ORDER BY in the view definition. Views don't allow ORDER BYs without it. I see no other reason to use TOP 100 PERCENT in a query.

Tara

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-30 : 19:42:12

quote:
Originally posted by tduggan

TOP 100 PERCENT is used in views when you want to include an ORDER BY in the view definition. Views don't allow ORDER BYs without it. I see no other reason to use TOP 100 PERCENT in a query.

Tara

Interesting. good to know. I love SQLTeam.com!

- RoLY roLLs

giri prakash
Starting Member

5 Posts

Posted - 2009-08-10 : 04:54:07

hello sql experts while executing this query iam getting an error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS" pls rectify the error its very urgent

SELECT Distinct(T0.DocEntry),
(SELECT SlpName FROM OSLP WHERE SlpCode=T0.SlpCode) As[Sales Emp / Refere],
(SELECT (IsNull(firstName,'')+IsNull(middleName,'')+IsNull(lastName,''))
FROM OHEM WHERE empID=T0.OwnerCode) As[CSE],
T0.[CardCode] As[CustomerCode],
T0.[CardName] As[CustomerName],
T0.[DocNum] As[BookingNum],
T0.[DocDate] As[BookingDate],
T1.[ItemCode] As[UnitCode],
T1.[Dscription] As[UnitName],
(SELECT NAME FROM [@OPCK]WHERE Code=T0.U_Pricel) As[Packages],
T3.U_InsNum As[Installment NO],
T3.U_DueDt As[Installment Due Date],T1.Gtotal As[Total Amount],
sum(T3.U_InsAmt) As[Total Instl amount],
sum(T3.U_PadAmt) as[Total Received amount],
(SELECT case When T0.U_CancelDt is not null Then T0.U_CancelDt else
(SELECT Distinct(ODLN.U_CancelDt ) FROM ODLN,DLN1 WHERE DLN1.ITEMCODE=T1.ItemCode) End As[CancelDate],
case When T0.U_CancelDt is not null Then T0.Comments else
(SELECT Distinct(ODLN.Comments ) FROM ODLN,DLN1 WHERE DLN1.ITEMCODE=T1.ItemCode) End As[Remarks],
case When T0.U_CancelDt is not null Then T0.U_Cancel else
(SELECT Distinct(ODLN.U_Cancel ) FROM ODLN,DLN1 WHERE DLN1.ITEMCODE=T1.ItemCode) End As[Cancellation Reason]
from ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
where T0.Canceled='y')
FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2
ON T1.ItemCode = T2.ItemCode, [dbo].[@INPS] T3
WHERE T2.[ItmsGrpCod] ='100' And
T0.[DocNum] = T3.[U_SoNum] And T0.Canceled='y' and
T3.[U_Stat] ='UnPaid' And T3.[U_DueDt] Between '20090731'and '20090831'
GROUP BY T0.[CardCode], T0.[CardName], T0.[DocNum], T0.[DocDate],T0.DocEntry,
T0.OwnerCode,T0.SlpCode,T1.[ItemCode], T1.[Dscription],T0.U_Pricel,T0.U_CancelDt ,
T1.Gtotal,T3.U_InsNum,T3.U_DueDt,T3.U_PadAmt, T0.U_Cancel,T0.Comments

thanks in advance
regards
giriprakash

giriprakash

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-08-12 : 15:59:39

A couple of things - you should've started a new post, not ping off one that's 5 years old. Second, "pls rectify the error its very urgent" usually turns people off and will get you little, if any, help. Lastly, you've provided no DDL, no sample input data or desired output data. We're not here to "guess" what it is you need........

Terry

-- Procrastinate now!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-08-12 : 17:32:11

quote:
Originally posted by giri prakash

hello sql experts while executing this query iam getting an error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS" pls rectify the error its very urgent
<snip>

That means that one or more of your sub-queries is returning more than one result. I assume it is one of the following subqueries, can you determine which one(s) return more than one row?

(SELECT SlpName FROM OSLP WHERE SlpCode=T0.SlpCode) As[Sales Emp / Refere]

(SELECT (IsNull(firstName,'')+IsNull(middleName,'')+IsNull(lastName,''))

(SELECT NAME FROM [@OPCK]WHERE Code=T0.U_Pricel) As[Packages],

(SELECT Distinct(ODLN.U_CancelDt ) FROM ODLN,DLN1 WHERE DLN1.ITEMCODE=T1.ItemCode) End As[CancelDate],

(SELECT Distinct(ODLN.Comments ) FROM ODLN,DLN1 WHERE DLN1.ITEMCODE=T1.ItemCode) End As[Remarks],

(SELECT Distinct(ODLN.U_Cancel ) FROM ODLN,DLN1 WHERE DLN1.ITEMCODE=T1.ItemCode) End As[Cancellation Reason]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources