SubQuery returned more than 1 value error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SubQuery returned more than 1 value error

Author

Topic

axapta
Starting Member

11 Posts

Posted - 2006-06-30 : 07:39:01

Hi Group,
I'm getting the above error in my SP when the tblHolidayDates has more than one record. Here is my SP:
begin

SELECT su.SuspensionID, su.ConsentNo, si.SiteID,
si.NameNo + ' ' + si.Address1 AS SiteAddress, si.SiteDesc,
si.SuspensionStart, si.SuspensionEnd, si.Address1, si.BaysAttached,
rd.RefValue AS NoticeDays, si.MergeID
FROM Suspension su
INNER JOIN SiteData si ON su.SuspensionID = si.SuspensionID
INNER JOIN RefData rd ON si.NotReq = rd.RefCode
WHERE su.Status = 'ACT'
and si.Archived = 'N'
and datediff(day, (Select holidaydate from tblHolidayDates), si.SuspensionStart)= rd.RefValue)
end

Any ideas? ways to get around this?
What the above is saying is select all the records where the difference between the the holidaydate in the tblHolidayDate table and the suspensionstart = the notice period (rd.value). This will show all the records that will fall on the holidaydate.

TIA

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-30 : 07:45:56

It's (Select holidaydate from tblHolidayDates)
that's causing the proble.

You are rtying to get the difference between SuspensionStart and all the dates in tblHolidayDates which is pretty meaningless. You have to select one of the values from the table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-30 : 07:48:13

Rewrite as this instead. But the use of tblHolidayDates is unclear.

SELECT		su.SuspensionID,
		su.ConsentNo,
		si.SiteID,
		si.NameNo + ' ' + si.Address1 AS SiteAddress,
		si.SiteDesc,
		si.SuspensionStart,
		si.SuspensionEnd,
		si.Address1,
		si.BaysAttached,
		rd.RefValue AS NoticeDays,
		si.MergeID
FROM		Suspension su
INNER JOIN	SiteData si ON su.SuspensionID = si.SuspensionID
INNER JOIN	RefData rd ON si.NotReq = rd.RefCode
CROSS JOIN	tblHolidayDates
WHERE		su.Status = 'ACT'
		and si.Archived = 'N'
		and datediff(day, tblHolidayDates.holidaydate, si.SuspensionStart) = rd.RefValue

Peter Larsson
Helsingborg, Sweden

axapta
Starting Member

11 Posts

Posted - 2006-06-30 : 07:53:16

Yes, I know the Select holidaydate from tblHolidayDates statement is causing the error. With one record this is OK. With more than one, it gives the error. I need to rewrite the procedure but do not know SQL enough to.
Thanks

quote:
Originally posted by nr

It's (Select holidaydate from tblHolidayDates)
that's causing the proble.

You are rtying to get the difference between SuspensionStart and all the dates in tblHolidayDates which is pretty meaningless. You have to select one of the values from the table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-30 : 08:00:24

It's not sql that's the problem it's the definition of what you are trying to do.

and datediff(day, (Select holidaydate from tblHolidayDates), si.SuspensionStart)= rd.RefValue)

What is this statement trying to achieve. Once you are able to define that you will probably be able to write it.
maybe
and exists (select * from tblHolidayDates where datediff(day, holidaydate, si.SuspensionStart)= rd.RefValue)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

axapta
Starting Member

11 Posts

Posted - 2006-06-30 : 08:20:46

Hi Peter,
Thanks for your reply. Doing it this way gives the following error:
The column prefix 'tblHolidayDates' does not match with a table name or alias name used in the query.

quote:

Originally posted by Peso

Rewrite as this instead. But the use of tblHolidayDates is unclear.

SELECT		su.SuspensionID,
		su.ConsentNo,
		si.SiteID,
		si.NameNo + ' ' + si.Address1 AS SiteAddress,
		si.SiteDesc,
		si.SuspensionStart,
		si.SuspensionEnd,
		si.Address1,
		si.BaysAttached,
		rd.RefValue AS NoticeDays,
		si.MergeID
FROM		Suspension su
INNER JOIN	SiteData si ON su.SuspensionID = si.SuspensionID
INNER JOIN	RefData rd ON si.NotReq = rd.RefCode
CROSS JOIN	tblHolidayDates
WHERE		su.Status = 'ACT'
		and si.Archived = 'N'
		and datediff(day, tblHolidayDates.holidaydate, si.SuspensionStart) = rd.RefValue

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-30 : 08:29:31

quote:
Originally posted by axapta

Hi Peter,
Thanks for your reply. Doing it this way gives the following error:
The column prefix 'tblHolidayDates' does not match with a table name or alias name used in the query.

Replace my name suggestion tblHolidayDates for the holiday table, with the real name you use in your environment.

Peter Larsson
Helsingborg, Sweden

axapta
Starting Member

11 Posts

Posted - 2006-06-30 : 08:35:19

Thanks nr.
The statement is retreiving records where the difference between the suspension start (datetime) and the rd.refvalue (which is an int type field) gives a date in the tblHolidaydates.
The statement you gave, does not produce an error. I'll test it with some data and let you know how I get on.
Thanks.

quote:
Originally posted by nr

It's not sql that's the problem it's the definition of what you are trying to do.

and datediff(day, (Select holidaydate from tblHolidayDates), si.SuspensionStart)= rd.RefValue)

What is this statement trying to achieve. Once you are able to define that you will probably be able to write it.
maybe
and exists (select * from tblHolidayDates where datediff(day, holidaydate, si.SuspensionStart)= rd.RefValue)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

axapta
Starting Member

11 Posts

Posted - 2006-06-30 : 09:17:42

All the names are correct.

quote:
Originally posted by Peso

quote:
Originally posted by axapta

Hi Peter,
Thanks for your reply. Doing it this way gives the following error:
The column prefix 'tblHolidayDates' does not match with a table name or alias name used in the query.
Replace my name suggestion tblHolidayDates for the holiday table, with the real name you use in your environment.

Peter Larsson
Helsingborg, Sweden

axapta
Starting Member

11 Posts

Posted - 2006-06-30 : 09:19:50

A BIG thank you to you nr. This works great!!

NB (I was into 35 posts on another forum for anyone to understand what I was trying to do!!). I'll submit your suggestion!!
Onced again thanks mate!!

quote:
Originally posted by nr

It's not sql that's the problem it's the definition of what you are trying to do.

and datediff(day, (Select holidaydate from tblHolidayDates), si.SuspensionStart)= rd.RefValue)

What is this statement trying to achieve. Once you are able to define that you will probably be able to write it.
maybe
and exists (select * from tblHolidayDates where datediff(day, holidaydate, si.SuspensionStart)= rd.RefValue)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources