SQL Server Performance

A type to SQL

Discussion in 'General DBA Questions' started by Leite33, Feb 18, 2007.

  1. Leite33 New Member

    Hi i have 2 tables A and B.......With same fields!!!<br />For Example: A.Number1,A.Number.2 and B.Number1,B.Number2<br /><br />Now<br />i want to make the follow type:<br /><br />Result=sum(A*B)/min(A*A,B*B)<br /><br />with queries and at last compare with if and else wich is the min <br />and make the type work.....<br />My problems is how i will keep the results in variables<img src='/community/emoticons/emotion-7.gif' alt=':s' />um(A*B),(A*A),(B*B)<br />and second how i will syntax if and else and set the final result...<br />The calculations will be each field with each field of every table....<br /><br />
  2. MohammedU New Member

    You can't use sum(A*B) because A and B are tables...
    YOu can use the columns in place of tables... sum(A.Number1*B.Number1)...



    MohammedU.
    Moderator
    SQL-Server-Performance.com
  3. Leite33 New Member

    Oh god i know its tables it was an example to make you understand!!! I know how i will make the calculations between the 2 tables... My Problem is how i will keep the 3 results into variables and how i will make if else work
  4. FrankKalis Moderator

    Please explain more detailed what you are trying to achieve. It is hard to guess that from your description.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  5. dineshasanka Moderator

    can you paste your table stru and more explaination

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  6. Leite33 New Member

    sorry i cant be more specifiec!!!! I want to ask something else!!! I used if((....) >= (.....))else .... and it said to me in error i cant use if else right because it brings me 3 results...... but everything ok in tables
  7. FrankKalis Moderator

    Sorry, but how do you think we can help you, when you don't provide clear and precise specs? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />What is the error you are getting now?<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  8. Roji. P. Thomas New Member

  9. Leite33 New Member

    My error is the above:

    Server: Msg 512, Level 16, State 1, Line 1
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    (3 row(s) affected)
    but i save the results ok i just have this error
  10. FrankKalis Moderator

    The error message is pretty clear. Obviously you're using a subquery in a comparison. You need to constraint the subquery in that way, that it only return 1 single scalar value. Probably the easiest way wold be to use a TOP 1, if that is applicable.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  11. Leite33 New Member

    No i have to return 3 valuess but i will try with top and i will see
  12. madhuottapalam New Member

    use IN instead of =

    select *from sometable where somecolumn IN(select somecolumn from sometable)

    Madhu
  13. Leite33 New Member

    I didnt get that what you mean!!! I use insert into but i have to put 3 results
  14. cmdr_skywalker New Member

    It will be better for everyone if we can see the code, this helps us understand if we are talking the same context of the "code". We can keep guessing on what you might need or not. But then again, they are all guess. Help us help you.



    May the Almighty God bless us all!
    www.empoweredinformation.com
  15. Leite33 New Member

    Ok it worked with top!!! Sorry i wasnt very specifiec!!!!!One more question!!!! When i use if else and i have may rows after if and many rows after else what i use???? () or {} or something else?????
    if
    {
    ..
    ...
    }
    else
    {
    ...
    ...
    }
  16. Leite33 New Member

    Again me one problem with if and else and one if i use top 1 it work but i get wrong results if i use top 3 its correct but then i get the same message
  17. Kewin New Member

    I guess what you're looking for is:

    IF sometest > 1
    BEGIN
    code
    more code
    even more code
    END

    You use BEGIN - END blocks instead of ( ) or { }

    /Kenneth
  18. Kewin New Member

    quote:Originally posted by Leite33

    Again me one problem with if and else and one if i use top 1 it work but i get wrong results if i use top 3 its correct but then i get the same message

    If you want us to be able to provide something useful as help, you need to give as an example...
    A few rows of data, and the desired output from that data.
    If there are more than one table involved, also a description of the relevant columns in those tables, what the keys are, and how the tables relate to eachother, along with any other 'business rules' that may come into play.

    Post this as script statements that may be pasted into a query window, then we'll be able to run it for you.

    /Kenneth
  19. Adriaan New Member

    I think some basic training in T-SQL would benefit OP more than any suggestions we can make. Don't waste your own time groping in the dark.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]

Share This Page