CODEWITHSUNDEEP
sqlsql-server
SearchForKnowledge
SearchForKnowledge

Reputation: 3751

Why does the SQL query throws a termination error

I have a SQL table which has some data. I have another table which takes those data and saves it to a new table:

TRUNCATE TABLE database.dbo.[table2]

INSERT INTO [database].[dbo].[table2]
SELECT CONVERT(VARCHAR(10), DATEADD(day, -(DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)), 101) AS [Date], -- ADD YESTERDAY'S DATE
    SUM([EMR ORDERS]) AS LastWeekEMROrders,
    SUM([ACCESSIONED LAB ORDERS]) AS LastWeekAccLabOrders,
    SUM([LAB_RESULT]) AS LastWeekLabResults,
    ((SELECT [EMR ORDERS] FROM [database].[dbo].[originaltable]) - (SELECT [ACCESSIONED LAB ORDERS] FROM [database].[dbo].[originaltable])) AS [LastWeekPendingProcessing]
FROM [database].[dbo].[originaltable]
WHERE
    [Date] >= DATEADD(day, -((DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7) - 6,
              DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) --PREVIOUS SUNDAY
    AND
    [Date] <= DATEADD(day, -(DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7,
              DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) --PREVIOUS SATURDAY

This is the error I get:

Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Basically the query it supposed to take the data from the original table and calculate based on week. It was working fine but I am not sure why happened...

Please help me resolve the issue.

Original table looks like this:

enter image description here

Upvotes: 0

Views: 72

Answers (1)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

Answer: because following queries returns more than one row:

(
(SELECT [EMR ORDERS] FROM [database].[dbo].[originaltable]) 
- 
(SELECT [ACCESSIONED LAB ORDERS] FROM [database].[dbo].[originaltable])
) AS [LastWeekPendingProcessing]

and this happens because [database].[dbo].[originaltable] has two or more rows.

Solutions:

[ 1 ] Use a predicate to filter rows:

(SELECT [EMR ORDERS] FROM [database].[dbo].[originaltable] WHERE <predicate>)

[ 2 ] Use an aggregate function:

(SELECT SUM/COUNT/...([EMR ORDERS] FROM [database].[dbo].[originaltable])

Note: you could use

(SELECT [EMR ORDERS] - [ACCESSIONED LAB ORDERS] FROM [database].[dbo].[originaltable] WHERE ...)

or

(SELECT SUM/...([EMR ORDERS] - [ACCESSIONED LAB ORDERS]) FROM [database].[dbo].[originaltable] WHERE ...)

Upvotes: 2

Related Questions