Click here to Skip to main content
15,064,166 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear Friends,

I am running the below query in sql but it is giving the below error.

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


What I have tried:

SQL
SELECT b.Service_Name, c.Service_Type, Application_No, Reg_No, Student_Name,(select CONVERT(CHAR(10), dt+(select count(1) from Holiday_list 
where Date_Fmt between School_update and dt ),103) cnt from (select case when Service_TypeID='1' then (School_update + 30)  else (School_update + 5) end as dt from Application_Status)a) as Nxt_date, DATEDIFF(DAY, School_update, GETDATE()) as Day_Count, Created_Date, School_Code, 
case when Payment_Status='Y' then 'PAID'  when Payment_Status='N' then 'NOT PAID' END as Payment_Status 
FROM Application_Status a,MST_Service b,MST_ServiceType c,KSEEBMASTERS.dbo.MST_SCHOOL s ,MST_Division d 
where a.Service_ID=b.Service_ID and  s.SCM_SCHOOL_CODE collate Latin1_General_CI_AI=a.School_Code and s.DIST_CODE collate Latin1_General_CI_AI=d.DistrictCode 
and a.Service_TypeID=c.Type_ID and d.DivisionCode='ED' and Payment_Status='Y' and school_status='Y' and Div_Status='N'


Edit - from OP comments
My error is in the below query only.
SQL
select CONVERT(CHAR(10), dt+(select count(*) from Holiday_list where Date_Fmt between School_update and dt ),103) cnt from (select case when Service_TypeID='1' then (School_update + 30) else (School_update + 5) end as dt,School_update from Application_Status)a

Holiday_list

Date_Fmt Holiday_Name
2020-10-02 Gandhi Jayanthi
2020-10-04 Sunday
2020-10-10 Second Saturday
2020-10-11 Sunday
2020-10-18 Sunday
2020-10-24 Fourth Saturday
2020-10-25 Sunday
2020-10-26 Vijayadashami
2020-10-30 Ed Milad
2020-10-31 Valmiki Jayanthi
2020-11-01 Sunday
2020-11-08 Sunday
2020-11-14 Second Saturday
2020-11-15 Sunday
2020-11-16 Balipadyami
2020-11-22 Sunday
2020-11-28 Fourth Saturday
2020-11-29 Sunday
2020-12-03 Kanakadasa Jayanthi
2020-12-06 Sunday
2020-12-12 Second Saturday
2020-12-13 Sunday
2020-12-20 Sunday
2020-12-25 Christmas
2020-12-26 Fourth Saturday
2020-12-27 Sunday
this is the one table and another is below
Application_Status

Application_No School_update
202010090351001 2020-10-09 16:06:42.187
202010090552001 2020-10-09 17:49:54.037
Here i need to print the working day by skipping holidays and there is a service ID for 1 we have to calculate 30 days and for 2 calculate 5 days.
Application_No Service_TypeID School_update
202010090351001 1 2020-10-09 16:06:42.187
202010090552001 2 2020-10-09 17:49:54.037
Posted
Updated 9-Oct-20 7:00am
v4
Comments
F-ES Sitecore 9-Oct-20 11:05am
   
We can't really help without have access to your data but if you google that error message it will show you the scenarios that it occurs.
KriShna RaJendra N PraSad 9-Oct-20 11:08am
   
Actually I tried in different way but no luck. My error is in the below query only.

select CONVERT(CHAR(10), dt+(select count(*) from Holiday_list where Date_Fmt between School_update and dt ),103) cnt from (select case when Service_TypeID='1' then (School_update + 30) else (School_update + 5) end as dt,School_update from Application_Status)a

Here Application_Status is sone table with Column name School_Update and Holiday_List is another table with column name Date_Fmt.
CHill60 9-Oct-20 11:16am
   
Can you give us some sample data and the results you expect from that sample - use the "Improve question" link to add it to your question
KriShna RaJendra N PraSad 9-Oct-20 11:20am
   
sure
KriShna RaJendra N PraSad 9-Oct-20 11:23am
   
Holiday_list

Date_Fmt Holiday_Name
2020-10-02 Gandhi Jayanthi
2020-10-04 Sunday
2020-10-10 Second Saturday
2020-10-11 Sunday
2020-10-18 Sunday
2020-10-24 Fourth Saturday
2020-10-25 Sunday
2020-10-26 Vijayadashami
2020-10-30 Ed Milad
2020-10-31 Valmiki Jayanthi
2020-11-01 Sunday
2020-11-08 Sunday
2020-11-14 Second Saturday
2020-11-15 Sunday
2020-11-16 Balipadyami
2020-11-22 Sunday
2020-11-28 Fourth Saturday
2020-11-29 Sunday
2020-12-03 Kanakadasa Jayanthi
2020-12-06 Sunday
2020-12-12 Second Saturday
2020-12-13 Sunday
2020-12-20 Sunday
2020-12-25 Christmas
2020-12-26 Fourth Saturday
2020-12-27 Sunday

this is the one table and another is below

Application_Status

Application_No School_update
202010090351001 2020-10-09 16:06:42.187
202010090552001 2020-10-09 17:49:54.037
KriShna RaJendra N PraSad 9-Oct-20 11:24am
   
Here i need to print the working day by skipping holidays and there is a service ID for 1 we have to calculate 30 days and for 2 calculate 5 days.

Application_No Service_TypeID School_update
202010090351001 1 2020-10-09 16:06:42.187
202010090552001 2 2020-10-09 17:49:54.037

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

What it is saying is that you can't do this:
SQL
SELECT A, (SELECT B FROM BTable) FROM ATable
Unless the BTable SELECT query returns one single value at all times.
An that's for good reason: if the ATable query returns one value, and the BTable query returns two, SQL has no idea what to display inteh single row!

Almost certainly, instead of a sub query and a long list of tables to select from:
SQL
SELECT ... FROM Application_Status a,MST_Service b,MST_ServiceType c,KSEEBMASTERS.dbo.MST_SCHOOL s ,MST_Division d 
where ...
You need to think about your data relationships rather more carefully, and use JOINs to fetch related data: SQL Joins[^]
   
Further to @OriginalGriff's excellent answer consider the following query
SQL
select Application_No, Date_fmt
from Application_Status 
cross join holiday_list
It will give you each of the holidays for each of the applications (be careful with larger data sets, you are going to want to filter the join).

Plug in some criteria to get just the holidays you are interested in for each application e.g.
SQL
select Application_No, COUNT(*) as cnt 
from @Application_Status 
cross join @holiday_list
WHERE Date_Fmt BETWEEN School_update AND case when Service_TypeID='1' then (School_update + 30)  else (School_update + 5) end 
group by Application_No
This will now give you one row per application with the count of the number of holidays between the relevant dates determined by the Service Type.

You can use that query in a Common Table Expression or a Temporary table or a sub-query to JOIN it o your other data e.g. here it is being used as a sub-query (Note I've shown the JOINs to the other tables but they are commented out because you didn't supply any information about them)
SQL
SELECT a.Application_No, 
		School_update + cnt as Nxt_date, 
		DATEDIFF(DAY, School_update, GETDATE()) as Day_Count
FROM 
	@Application_Status a
	--INNER JOIN MST_Service b ON a.Service_ID=b.Service_ID
	--INNER JOIN MST_ServiceType c ON a.Service_TypeID=c.Type_ID
	--INNER JOIN KSEEBMASTERS.dbo.MST_SCHOOL s ON s.SCM_SCHOOL_CODE collate Latin1_General_CI_AI=a.School_Code 
	--INNER JOIN MST_Division d ON s.DIST_CODE collate Latin1_General_CI_AI=d.DistrictCode
	INNER JOIN (select Application_No, COUNT(*) as cnt
			from @Application_Status cross join @holiday_list
			WHERE Date_Fmt BETWEEN School_update AND case when Service_TypeID='1' then (School_update + 30)  else (School_update + 5) end 
			group by Application_No ) aa on a.Application_No = aa.Application_No
--where d.DivisionCode='ED' and Payment_Status='Y' and school_status='Y' and Div_Status='N'
   
I would just generate "service day" records from the "service" table, and add them to the "holiday table", and go from there.
   

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