Query

I am using the follwing query in If condition in my SP for some validations..

If (select substring(admissioncode,12,3) from school)<>STU
Error: subquery returned more than 1 value.

If(select top 1 substring(admissioncode,12,3) from school)<>STU
result: am able to check only the first record.

How can I check all the records where ever it is not equal to STU.



Thanks!
"He laughs best who laughs last"

Use EXISTS or NOT EXISTS:

IF EXISTS
(select * from school WHERE substring(admissioncode,12,3) <> 'STU')
BEGIN
-- do whatever you have to do
END

How can I select records skippping only the first record?

Thanks!
"He laughs best who laughs last"

SET @pos =(select substring(admissioncode,12,3) from school)

IF EXISTS(@pos<>'STU' or @pos<>STC or @pos<>STP)
BEGIN
Error Mesg.
END


I am trying to work out in this way..like if @pos has any thing other than STU,STC,STP then kick an error, but unable to get it, pls help.

Thanks!
"He laughs best who laughs last"

Your description is not getting any clearer.<br /><br />Q1: How can I select records skippping only the first record?<br /><br />A1: You use a subquery in the WHERE clause, like this:<br /><br />SELECT * FROM schools s1<br />WHERE s1.SchoolId<br />NOT IN (SELECT TOP 1 s2.SchoolId FROM Schools s2<br />ORDER BY &lt;column_that_decides_which_is_the_first_row&gt<img src='/community/emoticons/emotion-5.gif' alt='' /><br /><br /><br /><br />Q2: I am trying to work out in this way..like if @pos has any thing other than STU,STC,STP then kick an error, but unable to get it, pls help.<br /><br />A2: Do you want to filter your resultset, or do you only want to check if there are rows like that (which was your original question)?

I dont neeed to get any result set but need to find out if htere are any records meeting the criteria and kick an eror mesg for that.

Thanks!
"He laughs best who laughs last"

IF EXISTS
(select * from school WHERE substring(admissioncode,12,3) IN ('STU', 'STC', 'STP'))
BEGIN
-- do whatever you have to do
END

No..

what I need is... If my @pos(resultset) do not have any of the(STU,STC,STP) then kick an error.
It mean my result set which is SET for @pos should not contain any other records except STU,STP,STC


Thanks!
"He laughs best who laughs last"

Let me explain what that last version does:

It looks in the School table, checking the admissioncode column on each row. From the admissioncode, it takes characters 12-14, and checks if it says "STU" or "STC" or "STP".

As soon as the query finds one row where characters 12-14 are "STU" or "STC" or "STP", then the EXISTS criteria is satisfied and the IF statement evaluates as true.

OK - now if you want to raise an error in case there is NO row where characters 12-14 are "STU" or "STC" or "STP", then change EXISTS to NOT EXISTS.

If you want to raise the error only if there are no rows with "STU", and no rows with "STC", and no rows with "STP" - then you need three NOT EXISTS subqueries, combined with AND:

IF NOT EXISTS
(select * from school WHERE substring(admissioncode,12,3) = 'STU')
AND NOT EXISTS
<fill in the blanks>
BEGIN
-- do whatever you have to do
END

some times there may be rows where instead of having STU it may have STD( which is not required), I have to find it.then I have to kick an error mesg saying that there is some thing wrong at that position.

when i kick that error mesg to user, then the user will correct that to the requirement.

Thanks!
"He laughs best who laughs last"

I am getting syntax error at

If EXISTS (@pos NOT IN (STU,STC,STP))

Thanks!
"He laughs best who laughs last"

If EXISTS (select @pos where @pos NOT IN (STU,STC,STP))


I am getting the following error.

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

i understand the error, but how can I resolve my issue?


Thanks!
"He laughs best who laughs last"

quote:Originally posted by Reddy

If EXISTS (select @pos where @pos NOT IN (STU,STC,STP))


I am getting the following error.

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

i understand the error, but how can I resolve my issue?


Thanks!
"He laughs best who laughs last"

Does this (your code) return right result sets?
SET @pos =(select substring(admissioncode,12,3) from school)
AND
make sure you are evaluating one row for the assigned variable based on your subquery condition.

Reddy,

You've asked lots of questions here in the past, so you should already know most of the basics. Your requirement is very poorly formulated, and you're using poor T-SQL syntax - which seems just TOO complicated, so it is extremely difficult to answer.

Please answer these points:

Give us some sample data!

Tell us where this code is supposed to exist: in a trigger, in a stored procedure, in a UDF ...

Tell us what you want to do ...
(1) find out if there are any rows with one of the listed values (or no rows)
(2) find out if there is at least one row for each of the listed values
(3) return a resultset which is filtered: only the listed values, or all values except those listed
(4) update or delete rows using the same logic
(5) just throw an error message

Also tell us what results you expect to see, based on the sample data.

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Reddy</i><br /><br />I am getting syntax error at<br /><br />If EXISTS (@pos NOT IN (STU,STC,STP))<br /><br />Thanks!<br />"He laughs best who laughs last"<br /><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br />Syntax errors should not be the topic of discussion on this forum. You can try your end to correct the syntax errors instead of posting here just to increase post numbers. [<img src='/community/emoticons/emotion-1.gif' alt='' />]

Okay - this post suggests your not querying a table, just checking a value entered by a user:

"some times there may be rows where instead of having STU it may have STD( which is not required), I have to find it.then I have to kick an error mesg saying that there is some thing wrong at that position.

when i kick that error mesg to user, then the user will correct that to the requirement."

CREATE PROCEDURE dbo.TestAdmissionCode12To14
(@AdmissionCode NVARCHAR(50))
AS

IF SUBSTRING(@AdmissionCode,12,3) NOT IN ('STU', 'STC', 'STP')
BEGIN
RAISERROR 'Admission Code must contain STU, STC or STP', 16, 1
RETURN
END
ELSE
BEGIN
-- The admission code is acceptable, so you could insert it as a new row on a table - whatever you want to do, really
END

GO

Or if I can be a bit more blunt <br /><br />Garbage In -&gt; garbage out <img src='/community/emoticons/emotion-1.gif' alt='' />

Well, maybe I should say.. my reply is to the original poster and not to Adriaans reply <img src='/community/emoticons/emotion-4.gif' alt='' />

Not necessarily garbage - just lack of normalization. The admission code should be broken up in three parts, characters 1-11, 12-14, and 15-...

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Reddy</i><br /><br />I am using the follwing query in If condition in my SP for some validations..<br /><br />If (select substring(admissioncode,12,3) from school)&lt;&gt;STU<br />Error: subquery returned more than 1 value.<br /><br />If(select top 1 substring(admissioncode,12,3) from school)&lt;&gt;STU<br />result: am able to check only the first record.<br /><br />How can I check all the records where ever it is not equal to STU.<br /><br /><br /><br />Thanks!<br />"He laughs best who laughs last"<br /><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />1 You never ask any questions with clear explanations<br />2 You never reply to say "Thanks" if you get answer<br />3 You always post "it is giving error" if you dont get what you want<br />4 Sometime you ask question and doesnt follow<br />5.<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Madhivanan<br />I couldn't agree more.[<img src='/community/emoticons/emotion-1.gif' alt='' />]

quote:Originally posted by Madhivanan

>>I couldn't agree more.

Why?

Madhivanan

Failing to plan is Planning to fail

'I couldn't have agreed more'
You know what this means, right? In other words, I agree.

Thanks,
DilliGrg

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />[<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br />But seems like Reddy doesn't really care about these points. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Thanks,<br />DilliGrg