SQL Server Performance

Query

Discussion in 'General Developer Questions' started by Reddy, Oct 19, 2006.

  1. Reddy New Member

    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"

  2. Adriaan New Member

    Use EXISTS or NOT EXISTS:

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

    How can I select records skippping only the first record?

    Thanks!
    "He laughs best who laughs last"

  4. Reddy New Member

    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"

  5. Adriaan New Member

    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)?
  6. Reddy New Member

    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"

  7. Adriaan New Member

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

    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"

  9. Adriaan New Member

    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
  10. Reddy New Member

    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"

  11. Reddy New Member

    I am getting syntax error at

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

    Thanks!
    "He laughs best who laughs last"

  12. Reddy New Member

    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"

  13. DilliGrg Member

    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.
  14. Adriaan New Member

    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.
  15. DilliGrg Member

    <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=':)' />]
  16. Adriaan New Member

    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
  17. Chappy New Member

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

    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=':p' />
  19. Adriaan New Member

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

    quote:Originally posted by Reddy

    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"



    1 You never ask any questions with clear explanations
    2 You never reply to say "Thanks" if you get answer
    3 You always post "it is giving error" if you dont get what you want
    4 Sometime you ask question and doesnt follow
    5.

    Madhivanan

    Failing to plan is Planning to fail
  21. DilliGrg Member

    <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=':)' />]
  22. Madhivanan Moderator

    >>I couldn't agree more.

    Why?

    Madhivanan

    Failing to plan is Planning to fail
  23. DilliGrg Member

    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
  24. Madhivanan Moderator

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  25. DilliGrg Member

    <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
  26. Madhivanan Moderator

    >>But seems like Reddy doesn't really care about these points.

    Yes it is

    Madhivanan

    Failing to plan is Planning to fail

Share This Page