Click here to Skip to main content
15,662,484 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi ,
I ran Below query but not executed. Please help me to solve this issue:

SQL
select COUNT(distinct RepID) 'MissingInventory'
from
( select CAST(datediff(day,0,datesent) as datetime) datesent
,r.repid,TerritoryHierarchy = (SELECT DISTINCT TOP 1 HR.TerritoryNum TerritoryID,HR.Dateto,HR.DateFrom
             FROM HistoryRepTerritory HR
             where e.Datesent between HR.DateFrom and HR.DateTo
             and HR.RepID = r.RepID
            -- order by HR.Dateto DESC,HR.DateFrom DESC
             )
from dbo.reps r inner join dbo.Email_EmailsToSend e on r.RepId = e.Reference
where e.AlertsituationId = '13'
and cast(Datediff(DAY,0,cast(isnull(e.Datesent,'1/1/1900') as datetime)) as datetime)
between '1/1/2014' and '12/31/2014') tf
WHERE EXISTS( SELECT 1 FROM TerritoryHierarchy TH inner join HistoryRepTerritory HT
on HT.TerritoryNum = TH.ID )


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

1 solution

Problem is here-
SQL
SELECT DISTINCT TOP 1 HR.TerritoryNum TerritoryID,HR.Dateto,HR.DateFrom
             FROM HistoryRepTerritory HR
             where e.Datesent between HR.DateFrom and HR.DateTo
             and HR.RepID = r.RepID

You can mention only one column for the subquery.
Try this-
SQL
select COUNT(distinct RepID) 'MissingInventory'
from
( select CAST(datediff(day,0,datesent) as datetime) datesent
,r.repid,TerritoryHierarchy = (SELECT DISTINCT TOP 1 HR.TerritoryNum
             FROM HistoryRepTerritory HR
             where e.Datesent between HR.DateFrom and HR.DateTo
             and HR.RepID = r.RepID
            -- order by HR.Dateto DESC,HR.DateFrom DESC
             )
from dbo.reps r inner join dbo.Email_EmailsToSend e on r.RepId = e.Reference
where e.AlertsituationId = '13'
and cast(Datediff(DAY,0,cast(isnull(e.Datesent,'1/1/1900') as datetime)) as datetime)
between '1/1/2014' and '12/31/2014') tf
WHERE EXISTS( SELECT 1 FROM TerritoryHierarchy TH inner join HistoryRepTerritory HT
on HT.TerritoryNum = TH.ID )


Hope, it helps :)
 
Share this answer
 
v2

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900