Sql query problem ..................

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

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 9-Oct-20 5:01am

KriShna RaJendra N PraSad

Updated 9-Oct-20 7:00am

CHill60

v4

Add a Solution

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

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2020-10-09T05:34:00

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[^]

CHill60 · Answer 2 · 2020-10-09T06:29:00

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'

Gerry Schmitz · Answer 3 · 2020-10-09T07:00:00

Solution 3

I would just generate "service day" records from the "service" table, and add them to the "holiday table", and go from there.

Posted 9-Oct-20 7:00am

Gerry Schmitz

Sql query problem ..................

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0