Transact SQL :: Aggregate In Subquery

Oct 22, 2015

(select SUM(sales.Total) from sales where StudentHist.Curdate = max(sales.curdate))  AS 'Balance'Iam trying to write a subquery to  calculate the total amount of sales until the Curdate  in studenthist equals the Curdate in sales table..how to write this query??

View 11 Replies


ADVERTISEMENT

Transact SQL :: Using Aggregate Function With Subquery?

Aug 6, 2015

SSMS does not like mine!  THis is the error that I receive:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

And this is my syntax:

Select
employeeID
,COUNT(case when rehirestatus IN (select rehirestatus from regionalemptable where rtrim(storename) = 'Location1') THEN userID ELSE 0 END) +
COUNT(case when rehirestatus IN (select rehirestatus from globalemptable where rtrim(storename) = 'Location1') Then userID ELSE 0 End)
FROM production
GROUP BY employeeID
ORDER BY employeeID

View 6 Replies View Related

Cannot Perform An Aggregate Function On An Expression Containing An Aggregate Or A Subquery.

Oct 19, 2007

Can any1 tell me why i am getting an error


SELECT DISTINCT

--p.voucher,

--p.amount,

p.siteID,

b.siteID,

SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS OutStandingBalance,

SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS CashCheque,

SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS Vouchers

FROM

BillingTotal b,

Payment p

--WHERE

-- s.sitename=@cmb1

--AND p.siteid = s.siteid

-- p.voucher = 0

-- p.voucher = 1

GROUP BY p.siteID,b.siteID



Msg 130, Level 15, State 1, Line 1

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

View 8 Replies View Related

Aggregate On Subquery

Apr 25, 2008

I want to return only the sum total of each of the following two columns generated by this query, but when I wrap them in SUM() I get an error stating that I can't use an aggregate function on an aggregate or subquery.

Is there another approach that I might take to sum these?

SELECT CASE soitem.fmultiple
WHEN 1 then
(SELECT funetprice FROM sorels
WHERE (sorels.fsono = shmast.fcsono)
AND sorels.frelease
= SUBSTRING(shitem.fsokey,10,3)
AND sorels.fenumber
= shitem.fenumber) * shitem.fshipqty
ELSE
(SELECT top 1 funetprice FROM sorels
WHERE (sorels.fsono = shmast.fcsono)
AND sorels.finumber
= soitem.finumber) * shitem.fshipqty
END as ExtPrice,


CASE CAST((shitem.fshipqty) as int)
% nullif(CAST(inmast.fnusrqty1 as int),0)
WHEN 0 then
(CAST((shitem.fshipqty) as int) /
nullif(CAST(inmast.fnusrqty1 as int),0))
ELSE
(CAST((shitem.fshipqty) as int) /
nullif(CAST(inmast.fnusrqty1 as int),0)) + 1
END as BoxCount

FROM shmast INNER JOIN shitem ON shmast.fshipno = shitem.fshipno
INNER JOIN soitem ON (soitem.fsono = shmast.fcsono)
AND (Convert(Int,soitem.finumber) = Convert(Int,SUBSTRING(shitem.fsokey,8,10)) / 1000)
LEFT JOIN somast ON (shmast.fcsono = somast.fsono)
LEFT JOIN inmast ON (soitem.fpartno = inmast.fpartno)
WHERE (shmast.fbl_lading='00000000000000003784')
AND (shitem.fshipqty > 0)

View 6 Replies View Related

Aggregate Function On SubQuery

Jan 20, 2006

I am working on a view in SQL Server 2005.
I am trying to get a list of the number of sessions each user had by user. I tried doing it this way, but


SELECT userid, MAX
((SELECT COUNT(DISTINCT sessionId) AS SESSIONCOUNT
FROM dbo.Sessions AS OD
HAVING (sessionId = O.sessionId))) AS MAXSESSION
FROM dbo.Sessions AS O
GROUP BY userid

but it throws an error 'Cannot perform an aggregate function on an expression containing an aggregate or subquery.'

Is there an elegant solution for this?

Thanks,

View 1 Replies View Related

Subquery In WHERE Clause And Aggregate

Mar 4, 2014

So I wrote this code but keep getting an error.

List all resellers whose annual sales exceed the average annual sales for resellers whose Business Type is "Specialty Bike Shop". Show Business type, Reseller Name, and annual sales. Use appropriate subqueries.

-- 396 Rows

SELECT R.ResellerName
FROM dbo.DimReseller AS R
WHERE
R.ResellerKey IN (
SELECT F.ResellerKey
FROM dbo.FactResellerSales AS F
WHERE R.ResellerKey = F.ResellerKey AND R.BusinessType = 'Specialty Bike Shop'
GROUP BY F.ResellerKey
HAVING R.AnnualSales > AVG(R.AnnualSales)
)

View 15 Replies View Related

Pb To Retreive Max Value With Aggregate Subquery

Jul 20, 2005

I would like to know which products are my best sells by sellers, but iwould like to retreive this info by product id, seller id and the totalamount of sells for this product.My Sells table is :Seller_idProduct_idTotaldate_s1 2 1020/05/042 4 1512/05/043 5 2206/06/041 5 1807/06/044 8 1213/05/047 2 1119/05/043 4 1421/05/042 4 1418/05/041 5 1817/06/042 5 5008/05/04etc....I know how to retreive the total sells by product id and seller idSELECT Seller_id, Product_id, SUM(Total) AS totalFROM SellsWHERE date_s > '01/05/04'GROUP BY Seller_id,Product_id order by Seller_idSeller_idProduct_idTotal1 5 361 2 102 5 502 4 293 5 223 4 14I would like retreive only the max of total, and the Seller id andproduct id, like this :Seller_idProduct_idTotal1 5 362 5 503 5 22How can i do without using a temp table ?Thanks for your help.

View 3 Replies View Related

Transact SQL :: Aggregate Last 12 Months

Sep 2, 2015

I have a table with sample data as shown in the table below. for each month end date, I need to aggregate (sum) all the quantities for each product and customer and include the current month plus the past 12 months (including current month). 

For example, for customer C123, P123 and for 7/31/2015, the query should add 7/31/2015 quantity and the quantity for 1/31/2015. 

DECLARE @TEMP TABLE (Customer VARCHAR (10), Product VARCHAR(10), Month_end_date DATE, Quantity INT)
INSERT INTO @TEMP VALUES ('C123','P123','1/31/2015',10)
INSERT INTO @TEMP VALUES ('C124','P124','2/28/2015',20)
INSERT INTO @TEMP VALUES ('C125','P125','3/31/2015',30)
INSERT INTO @TEMP VALUES ('C126','P126','4/30/2015',40)

[Code] ....

the output for the above example should be
C123 P123 7/31/2015
 70+10=80

is this possible?

View 10 Replies View Related

Transact SQL :: Get Max Value Using Subquery?

Nov 23, 2015

I have the below data and want to only show records where Response Column has Max date for the commencement date and expiry date under questions column.. For example for tenantcode 52090 the highest Tab value is 2 but this is not correct as there is no associated value in response.

We need to compare all the commencement and lease expiry dates and show only the commencement and lease expiry with the recent dates. Null should be ignored Instead of using Max Tab or Max Modified date would the below logic work? Not sure how to script this.

1. Filter to find Questions containing "Expiry Date"

2. summarise to find the max expiry date per Tenant Code

3. find which records have that maximum expiry

4. join again to find the commencement date

5. union the records from (3) and (4)

6. cross tab

Declare @rent Table
(
TenantCode CHAR(6)
,HHMID CHAR(5)
,Tab CHAR(4)
,FieldNo CHAR(4)
,FieldRowCHAR(10)

[code]....

View 29 Replies View Related

Transact SQL :: Subquery In WHERE

Aug 18, 2015

I have the following stored proc that I need to pull InsuranceID specific rows when @InsuranceID > 0 is sent.  Below is the code and I think the issue is in the 2nd WHERE condition.

ALTER PROCEDURE [dbo].[ms_selJobSetupSelections]
(@ActiveOnlybit = 1,
@InsuranceIDint = 0)
AS
BEGIN
SELECT J.JobSetupID
,J.JobSetupText

[code]....

View 21 Replies View Related

Transact SQL :: Group By With Non-aggregate Columns

Aug 5, 2015

How can I aggregate this result into 1 row? (I got it from a UNION ALL)

Article         Assort1    Assort2
50095811    K1             NULL
50095811    NULL          K3

I would like to have

Article         Assort1    Assort2
50095811    K1             K3

View 3 Replies View Related

Transact SQL :: How To Reuse Subquery

Jul 29, 2015

with c1 As (
select 1 As '1' , 2  As '2' , 3 As '3'

, c2 As (
select 4  As '4', 5  As '5',  6 As '6'
union all
select 1 , 2 ,3 from c1  -- >>>>>  select from c1 here 
) select * from c2 
  union all
  select * from c1 -- >>>>>> and select from c1 here 

According to the query above , I try to reuse the subquery by put  the subquery into 'with cte'  name (c1)  then i select this  2 times .

if I do this way , how many time this subquery (c1) execute ?
if 1 time then this is the right way to reuse this subquery .
if 2 times  , it is not then what should i do to reuse this subquery ?

View 3 Replies View Related

Transact SQL :: Subquery Another Table?

Apr 27, 2015

I have a table which is called PneumoniaCareBundleDiagnosisCodes with the fields DiagnosisCode, DiagnosisDesc

I have a another table called dbo_OP_APPOINTMENT_PROCEDURE_PIVOT

With fields Procedure01, Procedure02, Procedure03, Procedure04, Procedure05, Procedure06, Procedure07, Procedure08, Procedure09, Procedure10, Procedure11, Procedure12.

(for info purposes the relationship here is DiagnosisCode = Procedure01, 02, 03 etc)

Is it possible to right a query that shows all records in dbo_OP_APPOINTMENT_PROCEDURE_PIVOT where the DiagnosisCode in table PneumoniaCareBundleDiagnosisCodes appear in any of the Procedure01, 02, 03 fields etc.

If I was using an IF statement the logic i would right it as follows:
IF dbo_OP_APPOINTMENT_PROCEDURE_PIVOT.Procedure01
IS IN PneumoniaCareBundleDiagnosisCodes.DiagnosisCode OR
IF dbo_OP_APPOINTMENT_PROCEDURE_PIVOT.Procedure02

[Code] ......

View 5 Replies View Related

Transact SQL :: Use Like Command With Subquery

Jun 10, 2015

I am writing below query to pull from customer table.

select * from Customer where SrNo in (Select distinct SrNo from Orders)

This is doing an exact match of SrNo in Orders table SrNo is part of string and can occur at any place in that string. I want to use the like command.

Please see below example.

select
*
from Customer
where SrNo
like('%89898989%','%928hhst%')

View 2 Replies View Related

Transact SQL :: Working Of Aggregate In Sub-query Using Exists?

Aug 4, 2015

When i am running  below snippet execution plan is showing constant scan instead of referring subquery table.

I want to know  how this query working. and why in execution plan there is no scan /seek which will basically indicate that particular table is getting referred.

select count(*) from A  where exists (select count(1) from B where A.a=B.a)

execution plan has to show  scan or seek for subquery. Surprisingly, output is coming as expected.

View 8 Replies View Related

Transact SQL :: Create Sequential Aggregate Of A Column Using Sum?

Aug 17, 2015

I need to create a sequential aggregate of a column using sum in tsql.

I want a column that will sum up all hours work by transaction date.

for example

Monday my hours work will be 8 and therefore my total transaction hours will be 8.

Tuesday my hours work will be 8 but I want the total hours of my total transaction hours to  16.

Wed my total hours work will be 5 and I want the total hours of my total transaction hours to  21. etc.

Select myname, weekday, hours worked, Totalhours_ToDate =Sum(hours worked)from table

View 3 Replies View Related

Transact SQL :: How To GROUP BY And Get Aggregate Where A Certain Condition Is True

Jul 16, 2015

I am using SQL 2005.  I have some data from an old application that did not follow the rules for normalization.  The table is for Invoices, and the table allows for 13 purchase items per record.  So in each row of my table I have a non-unique integer field itemID, itemID1, itemID2 ... itemID12.  For each itemID I also have "lbs_total" and "line_total" (which is price * lbs_total) - so itemID, lbs_total, line_total ... itemID1, lbs_total1, line_total1 ... etc.  It's a mess, I know.Each row has a unique Customer Number ("cno") and an Invoice Date ("inv_date").  My proc needs to allow for params for the item number, and a start date and end date for BETWEEN on the inv_date.I also need to get the aggregate for the lbs_total and the line_total.

View 15 Replies View Related

Transact SQL :: Aggregate 2 Dates And Concatenate Into New String

May 21, 2015

Have this table

ACCOU  NAME      NAME TODATE                            ID     EDUCAT    EXPIRYDATE
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 2016-06-24 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-VOL  2018-02-11 00:00:00.000

Need to get it like

ACCOU  NAME      NAME TODATE                            ID     EDUCAT    EXPIRYDATEstring
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000 2016-06-24 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-VOL  2018-02-11 00:00:00.000

In other words I need to Aggregate the 2 dates and concatenated into a new string col string so basically a sum with a group by but instead of a sum I need to concatenate the string. I know this should be possible using stuff and for xml path but I can't seem to get my head around it everything I try concatenates all the strings, not just the appropriate ones.

View 11 Replies View Related

Transact SQL :: Invalid Due To Not Being In Group By Or Aggregate Function

Aug 7, 2015

Well adding it to a group by or function skews the result set. How to write this query so it displays as I need it to?  This is what I have thus far, and it works as it should UNTIL I add in the line of 

cast(cte.[C] As float)/cast(sum(cte.[C]) over() as float)*100 As [Rate1],

Presents the error of:
Msg 8120, Level 16, State 1, Line 35
Column 'cte.[C]' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

This is my full on query -- with 3 CTE's involved to get me the actual result set I am after.  

;with cte as
(
select
[state],
case when exists (select 1 from table2 R where R.centername = d.centername) then 1 else 0 end as [L],
case when exists (select 1 from table3 C where C.centername = d.centername) then 1 else 0 end as [C]
FROM maintable d
),

[Code] .....

View 4 Replies View Related

Transact SQL :: Left Join With Subquery

Aug 24, 2015

SELECT A.EmpId,A.IncidentDate
FROM EmployeePoints1 as A
WHERE IncidentDate=
(SELECT MAX(IncidentDate)
FROM EmployeePoints1
WHERE EmpId = A.EmpId) AND (DATEADD(day,28,DATEADD(WEEK, DATEDIFF(WEEK, 0,A.IncidentDate), 0)) < DATEADD(WEEK, DATEDIFF(WEEK, 0,GetDate()), 0)) AND (A.IncidentCode = 'I' OR A.IncidentCode = 'A')
LEFT JOIN EmployeeTotalPoints1 ON EmployeeTotalPoints1.EmpId = A.EmpId

I am trying to left join another table but  I got

Incorrect syntax near the keyword 'LEFT'.

View 9 Replies View Related

Transact SQL :: Adding Subquery As A Column

May 19, 2015

I have a subquery i wanted to add a as a fourth column to my  Original Query(is the one below the subquery). How to combine the two queries to one statement?? I tried but was getting an error "Subquery returned more than 1 value "

select  
COUNT(*)FreeReduced
 from students s join Buildings b on
 s.Building_ID = b.Building_ID
 where s.Activeness =1 and (Eligibility = 3 or Eligibility =2) 
 group by building_number,Building_Name

[Code] ....

View 3 Replies View Related

Transact SQL :: Get Subquery To Eliminate Malformed Date?

Jun 30, 2015

I have the following query.

SELECT a.Line_Number as Line_Number,
Cast(a.election_effective_date as DATE) as election_effective_date,
Cast(a.Plan_Year_Effective as DATE) as Plan_year_Effective
FROM (SELECT aa.* FROM TEMP_Validation aa WHERE IsDate(aa.Plan_Year_Effective) = 1 AND IsDate(aa.election_effective_Date) = 1) a
Where Cast(a.election_effective_date as Date) Not Between Cast(a.plan_year_effective as Date)
and DATEADD(DD,-1,DATEADD(YY,1,Cast(a.plan_year_effective as Date)))

There is a malformed date in the Temp data, at line 4932.  This is why I created a subquery that checks if the plan year and election effective dates are dates using the "IsDate" function.  However, when I execute this I get the error "Conversion failed when converting date and/or time from character string".  If I eliminate line 4932 the error no longer occurs.  Somehow line 4932 is not being eliminated in the subquery, except if I run JUST the subquery it will not include line 4932.  What am I doing wrong on these queries and how can I correctly get the subquery to eliminate the malformed date?

View 5 Replies View Related

Transact SQL :: Aggregate Function Causing Error In Complex Join

May 13, 2015

I have a few tables I am trying to join to create a report. Everything was working fine until I tried to add an aggregate Sum function to a column (MaxCap) in table ctfBarn. 

select
x.*, y.division, y.department, y.location
,(right(z.SvcMgrName,len(z.SvcMgrName)-len(left(z.SvcMgrName,CHARINDEX(', ',z.SvcMgrName)-1))-2)+' '+
left(z.SvcMgrName,CHARINDEX(', ',z.SvcMgrName)-1))AS SvcMgrName
,(right(z.SrSvcName,len(z.SrSvcName)-len(left(z.SrSvcName,CHARINDEX(', ',z.SrSvcName)-1))-2)+' '+

[Code] .....

I think I probable need to include a group by but can't figure out the correct syntax.

View 12 Replies View Related

Transact SQL :: How To Turn Select Aggregate Function Statement Into A Variable

May 26, 2015

I tend to learn from example and am used to powershell. If for instance in powershell I wanted to get-something and store it in a variable I could, then use it again in the same code. In this example of a table order items where there are order_num, quantity and item_prices how could I declare ordertotal as a variable then instead of repeating it again at "having sum", instead use the variable in its place?

Any example of such a use of a variable that still lets me select the order_num, ordertotal and group them etc? I hope to simply replace in the "having section" the agg function with "ordertotal" which bombs out.

select order_num, sum(quantity*item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity*item_price) >=50
order by ordertotal;

View 11 Replies View Related

Transact SQL :: Query Works Even If Column Not Exists In Subquery

Jul 23, 2015

When I execute the below queries it works perfectly where as my expectation is, it should break.

Select * from ChildDepartment C where C.ParentId IN (Select Id from TestDepartment where DeptId = 1)
In TestDepartment table, I do not have ID column. However the select in sub query works as ID column exists in ChildDepartment.  If I do change the query to something below then definately it will break -
Select * from ChildDepartment C where C.ParentId IN (Select D.Id from TestDepartment D where D.DeptId = 1)

Shouldn't the default behavior be otherwise? It should throw error if column doesnt exists in sub query table and force me to define the correct source table or alias name.

create table TestDepartment
(
DeptId int identity(1,1) primary key,
name varchar(50)
)
create table ChildDepartment
(
Id int identity(1,1) primary key,

[Code] ....

View 3 Replies View Related

Transact SQL :: Update Unique Records Count For Subquery?

Sep 2, 2015

updating the # of Payer from below query to match with the # of rows for each payer record. See the Current and desired results below. The query is currently counting the # of rows for all payers together and updating 3 as # of payers. I need it to count # of rows for each payer like shown inDesired result below. It should be showing 1 for first payer and 2 for 2nd & 3rd based on # of times each payer is repeated..

SELECT b.FILING_IND, b.PYR_CD, b. PAYER_ID, b. PAYER_NAME,a.CLAIM_ICN,
(Select Count(*) From MMITCGTD.MMIT_CLAIM a, MMITCGTD.MMIT_TPL b , MMITCGTD.MMIT_ATTACHMENT_LINK c where a.CLAIM_ICN_NU =
c.CLAIM_ICN and b.TPL_TS = c.TPL_TS and a.CLAIM_TYPE_CD = 'X' 

[Code] ....

Current Result

FILING_IND
PYR_CD
PAYER_ID
PAYER_NAME
CLAIM_ICN
#_OF_PAYER

[code]....

View 4 Replies View Related

Transact SQL :: Pull Records From 3 Tables Using Joins Or Subquery?

Sep 14, 2015

I have 3 tables.

Table 1:
ID  Name  Description
1  ABc      xyz
2  ABC      XYZ

Table 2:
RoleID   Role
1         Admin
2         QA

Table 3:
ID   RoleID  Time
1     1         09:14
2     1         09:15
1     2         09:16

Now I want all the records which belongs to RoleID 1 but if same ID is belongs to RoleID 2 than i don't want that ID.From above tables ID 1 belongs to RoleID 1 and 2 so i don't want ID 1.

View 4 Replies View Related

Transact SQL :: Retrieve Another Column (exchange Rate) From Particular Date For Subquery?

May 25, 2015

Below is the query in which i want to retrieve another column (exchange rate) from a particular date for the sub query. 

Actually PurchaseOrderDet table get records related to purchaseorder but for each row in purchaseorderdet need info from the same table for all rows on a particular date.

how i can achieve this query without using RANK() and other functions.

"SELECT  Supplier.Uniid AS SupplierID, Supplier.Name, PurchaseOrder.Uniid AS PurchaseID, PurchaseOrder.OrderNo, PurchaseOrder.FormDate, StockItem.Uniid AS StockID, StockItem.StockCode + N' - ' + StockItem.Description1 AS StockItem,
PurchaseOrderDet.ExchangeRate, PurchaseOrderDet.UnitPrice, PurchaseOrderDet.Discount, SUM(PurchaseOrderDet.OrderQty) AS SumOfOrderQty,

[Code] ....

View 7 Replies View Related

Transact SQL :: Adding Case When Statement With Group By Query Doesn't Aggregate Records

Aug 28, 2015

I have a a Group By query which is working fine aggregating records by city.  Now I have a requirement to focus on one city and then group the other cities to 'Other'.  Here is the query which works:

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' 
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]

Here is the result:

St. Louis 1000
Kansas City 800
Columbia 700
Jefferson City 650
Joplin 300

When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' 
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]

Here is the result:

St. Louis 1000
Other Missouri City 800
Other Missouri City 700
Other Missouri City 650
Other Missouri City 300

What I would like to see is a result like:

St. Louis 1000
Other Missouri City 2450

View 5 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.

Apr 26, 2008

hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues]    @Uid intASBEGIN    SET NOCOUNT ON;        select                                  DATEPART(year, c.fy)as fy,                                                (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1%      JRF' ) as survivorship,                (select contribeamount from wh_contribute where and contribename like  'Gross Earnings' and ) as ytdgross,                (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5%      JRP') as totalcontrib,                                                       from    wh_contribute c                       where    c.uid=@Uid                 Order by fy Asc .....what is the wrong here??  " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap... 

View 1 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.

Jul 20, 2005

I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO

View 3 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &&<, &&<= , &&>, &&>= Or When The Subquery I

Mar 6, 2008

I am getting an error as

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

while running the following query.





SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,

LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,

LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,

LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,

ManagerName=(SELECT E.FirstName+' '+E.LastName

FROM EmployeeDetails E

INNER JOIN LUP_EmpProject

ON E.Empid=LUP_EmpProject.Empid

INNER JOIN LUP_FIX_ProjectDetails

ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid

WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)



FROM EmployeeDetails

INNER JOIN LUP_EmpDepartment

ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid

INNER JOIN LUP_FIX_DeptDetails

ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid

AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date

FROM LUP_EmpDepartment

WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid

ORDER BY LUP_EmpDepartment.Date DESC)

INNER JOIN LUP_EmpDesignation

ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid

INNER JOIN LUP_FIX_DesigDetails

ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid

AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date

FROM LUP_EmpDesignation

WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid

ORDER BY LUP_EmpDesignation.Date DESC)

INNER JOIN LUP_EmpProject

ON EmployeeDetails.Empid=LUP_EmpProject.Empid

AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate

FROM LUP_EmpProject

WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid

ORDER BY LUP_EmpProject.StartDate DESC)

INNER JOIN LUP_FIX_ProjectDetails

ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid



WHERE EmployeeDetails.Empid=1

PLEASE HELP.................

View 1 Replies View Related

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &&<, &&<= , &&>, &&>= Or When The Subquery I

May 14, 2008

Hi,

I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Any ideas as to why??

SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar,
RDR1.U_Variety,
(SELECT OITM.U_Variety
FROM OWOR INNER JOIN
WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN
OITM INNER JOIN
OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode
WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity,
OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet,
OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode
FROM OWOR AS t0 INNER JOIN
ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN
RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN
OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode
WHERE (t0.Status <> 'L')

Thanks

Jacquues

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved