CODEWITHSUNDEEP
sql-server-2008
user1004299
user1004299

Reputation: 135

Error creating a SP with temporal table inside and subquery

I've created this temporal table in my store procedure, as you can see I have more than 1 records for the same ID:

@tmpTableResults
TmpInstallerID      TmpConfirmDate  TmpConfirmLocalTime 
==============      ==============  ===================
173                 2011-11-08      11:45:50
278                 2011-11-04      09:06:26
321                 2011-11-08      13:21:35
321                 2011-11-08      11:44:54
483                 2011-11-08      11:32:00
483                 2011-11-08      11:31:59
645                 2011-11-04      10:03:15
645                 2011-11-04      07:03:15

That is the result of the query to create @tmpTableResults 

DECLARE @tmpTableResults TABLE
(
    TmpInstallerID int,
    TmpConfirmDate date,
    TmpConfirmLocalTime time

)

DECLARE @tmpTableQuery VarChar(800)

SET @tmpTableQuery = 'select FxWorkorder.INSTALLERSYSID, FxWorkorder.CONFIRMDATE, FxWorkorder.CONFIRMLOCALTIME from FxWorkorder   
   join install on FxWorkorder.INSTALLERSYSID = install.sysid 
   join RouteGroupWorkarea on FxWorkorder.WORKAREAGROUPSYSID = RouteGroupWorkarea.IWORKAREA_ID
   join RoutingGroup on RouteGroupWorkarea.IRG_ID = RoutingGroup.IRG_IDENTITY 

   where FxWorkorder.SCHEDULEDDATE > = @StartDate and FxWorkorder.SCHEDULEDDATE <= @EndDate 
    and FxWorkorder.Jobstatus <> "Unassign"
    and FxWorkorder.Jobstatus <> "Route"  
    and install.FOXTELCODE <> ""
    and FxWorkorder.CONFIRMLOCALTIME is not null
    and FxWorkorder.CONFIRMDATE <> ""
    group by FxWorkorder.INSTALLERSYSID, FxWorkorder.CONFIRMDATE, FxWorkorder.CONFIRMLOCALTIME
    order by FxWorkorder.INSTALLERSYSID, FxWorkorder.CONFIRMDATE, FxWorkorder.CONFIRMLOCALTIME desc '

INSERT INTO @tmpTableResults EXEC(@tmpTableQuery)

I'm creating another query to get data from another table and only the first record from the temporal table for the same INSTALLERSYSID

SELECT RoutingGroup.SDESCRIPTION, FxWorkorder.INSTALLERSYSID, FxWorkOrder.JOBSTATUS, Install.FOXTELCODE, 
    install.NAME, FxWorkOrder.ScheduledDate,
    count(*) as TotalJobs, COUNT(CONFIRMDATE) as ConfirmedJobs,
    (select TmpInstallerID, TmpConfirmDate, TmpConfirmLocalTime from @tmpTableResults where TmpInstallerID = FxWorkorder.INSTALLERSYSID)

from FxWorkorder 
   join install on fxworkorder.INSTALLERSYSID = install.sysid 
   join RouteGroupWorkarea on FxWorkOrder.WORKAREAGROUPSYSID = RouteGroupWorkarea.IWORKAREA_ID
   join RoutingGroup on  RouteGroupWorkarea.IRG_ID = RoutingGroup.IRG_IDENTITY 

where FxWorkorder.SCHEDULEDDATE  > = @StartDate and  FxWorkorder.SCHEDULEDDATE <= @EndDate  
    and FxWorkOrder.Jobstatus <> 'Unassign' 
    and FxWorkOrder.Jobstatus <> 'Route'  
    and Install.FOXTELCODE <> ''
group by RoutingGroup.SDESCRIPTION,FxWorkOrder.INSTALLERSYSID, FxWorkOrder.JOBSTATUS, Install.FOXTELCODE,install.NAME, FxWorkOrder.ScheduledDate,FxWorkOrder.WORKAREAGROUPSYSID

When I tried to save the sp I got the error

"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." I can't see why I got this error. But if I run the query in sql that works. Can someone see the error?

Upvotes: 1

Views: 91

Answers (1)

Andriy M
Andriy M

Reputation: 77707

I don't know how your second query works for you ‘in sql’ (where is that supposed to be? do you mean SSMS = SQL Server Management Studio?), but I'm sure it cannot possibly work in any version of SQL Server that exists at the moment. It's because of this subquery in the SELECT list:

 (select TmpInstallerID, TmpConfirmDate, TmpConfirmLocalTime from @tmpTableResults where TmpInstallerID = FxWorkorder.INSTALLERSYSID)

The thing is, every expression in the SELECT clause should be scalar, but this subquery returns a row of more than one value. Even if it's only one row, it is illegal there, because it returns several columns. The subquery in that context should return no more than one value, i.e. it should be one column and the result produced should contain either no rows or just one.

You could try this query instead (although I'm not entirely sure without knowing more details about your schema):

SELECT
    RoutingGroup.SDESCRIPTION,
    FxWorkorder.INSTALLERSYSID,
    FxWorkOrder.JOBSTATUS,
    Install.FOXTELCODE, 
    install.NAME, FxWorkOrder.ScheduledDate,
    count(*) as TotalJobs, COUNT(CONFIRMDATE) as ConfirmedJobs,
    tmp.TmpInstallerID,
    tmp.TmpConfirmDate,
    tmp.TmpConfirmLocalTime

from FxWorkorder 
   join install on fxworkorder.INSTALLERSYSID = install.sysid 
   join RouteGroupWorkarea on FxWorkOrder.WORKAREAGROUPSYSID = RouteGroupWorkarea.IWORKAREA_ID
   join RoutingGroup on  RouteGroupWorkarea.IRG_ID = RoutingGroup.IRG_IDENTITY 
   join @tmpTableResults tmp ON tmp.TmpInstallerID = FxWorkorder.INSTALLERSYSID

where FxWorkorder.SCHEDULEDDATE  > = @StartDate
    and FxWorkorder.SCHEDULEDDATE <= @EndDate  
    and FxWorkOrder.Jobstatus <> 'Unassign' 
    and FxWorkOrder.Jobstatus <> 'Route'  
    and Install.FOXTELCODE <> ''
group by
  RoutingGroup.SDESCRIPTION,
  FxWorkOrder.INSTALLERSYSID,
  FxWorkOrder.JOBSTATUS,
  Install.FOXTELCODE,install.NAME,
  FxWorkOrder.ScheduledDate,
  FxWorkOrder.WORKAREAGROUPSYSID
  tmp.TmpInstallerID,
  tmp.TmpConfirmDate,
  tmp.TmpConfirmLocalTime

That is, I added one more join, the one to @tmpTableResults, as well as added the columns you were trying to pull to the SELECT clause and to the GROUP BY clause.

Also, if I were you I would consider using short aliases for tables, like this:

SELECT
    …
    wo.INSTALLERSYSID,
    wo.JOBSTATUS,
    …
from FxWorkorder wo
   join …

That might make your queries more readable.

Upvotes: 2

Related Questions