Loading ...
Sorry, an error occurred while loading the content.

How to Run MSSQL StoredProcedure Automatically ?

Expand Messages
  • Ax
    Hi All , How can i force a storedprocedure in MSSQL 2000 to run automatically at a specific time ? Thanks Ax
    Message 1 of 25 , Mar 4 4:34 AM
    • 0 Attachment
      Hi All ,
      How can i force a storedprocedure in MSSQL 2000 to run automatically at
      a specific time ?

      Thanks
      Ax
    • Arnie Rowland
      Use a SQL Agent Job, and in that Job specify the Job Step to be EXECUTE MyStoredProcedure Then schedule that Job to run when you desire. Refer to Books Online,
      Message 2 of 25 , Mar 4 8:07 AM
      • 0 Attachment
        Use a SQL Agent Job, and in that Job specify the Job Step to be

        EXECUTE MyStoredProcedure

        Then schedule that Job to run when you desire.

        Refer to Books Online, topics: "SQL Agent", Jobs

        - Arnie Rowland, MVP (SQL Server)

        "I am a great believer in luck, and I find that the harder I work, the
        more I have of it." - Thomas Jefferson (1743-1826)



        -----Original Message-----
        From: SQLQueriesNoCode@yahoogroups.com
        [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Ax
        Sent: Tuesday, March 04, 2008 7:44 AM
        To: Arnie
        Subject: [SQLQueriesNoCode] How to Run MSSQL StoredProcedure
        Automatically ?

        Hi All ,
        How can i force a storedprocedure in MSSQL 2000 to run automatically at
        a specific time ?

        Thanks
        Ax




        Yahoo! Groups Links







        Disclaimer - March 4, 2008
        This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
        This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
      • John Warner
        I have a field that contains data like: 00-4010-00 10-4010-00 20-4010-00 20-4010-01 00-5510-00 Etc The only entries I care about will be the pattern
        Message 3 of 25 , Mar 5 2:47 PM
        • 0 Attachment
          I have a field that contains data like:

          00-4010-00
          10-4010-00
          20-4010-00
          20-4010-01
          00-5510-00
          Etc

          The only entries I care about will be the pattern nn-4010-nn, in this case
          I don't care what the nn's are.

          In a WHERE clause which is faster in your opinion?

          WHERE SUBSTRING(field, 4,4) = '4010'

          OR

          WHERE LIKE %4010%

          Note again this is in the WHERE, not the SELECT. Also if this makes a
          difference, the field is VARCHAR not CHAR and it will be hard coded like
          this into the proc, it won't be arriving as a parameter from the calling
          code.

          Thanks for your performance opinions

          SQL Server 2K On Win2003

          John Warner
        • Michael Weiss
          I am thinking they will both produce a table scan but you would have to test them to see which would actually be faster. I am not sure you can avoid a table
          Message 4 of 25 , Mar 5 4:27 PM
          • 0 Attachment
            I am thinking they will both produce a table scan but you would have to
            test them to see which would actually be faster. I am not sure you can
            avoid a table scan but I could well be wrong.

            Best wishes,

            Michael



            ________________________________

            From: SQLQueriesNoCode@yahoogroups.com
            [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
            Sent: Wednesday, March 05, 2008 2:48 PM
            To: SQLQueriesNoCode@yahoogroups.com
            Subject: [SQLQueriesNoCode] WHERE clause question
            Importance: Low



            I have a field that contains data like:

            00-4010-00
            10-4010-00
            20-4010-00
            20-4010-01
            00-5510-00
            Etc

            The only entries I care about will be the pattern nn-4010-nn, in this
            case
            I don't care what the nn's are.

            In a WHERE clause which is faster in your opinion?

            WHERE SUBSTRING(field, 4,4) = '4010'

            OR

            WHERE LIKE %4010%

            Note again this is in the WHERE, not the SELECT. Also if this makes a
            difference, the field is VARCHAR not CHAR and it will be hard coded like
            this into the proc, it won't be arriving as a parameter from the calling
            code.

            Thanks for your performance opinions

            SQL Server 2K On Win2003

            John Warner





            [Non-text portions of this message have been removed]
          • Yano, Grant
            I ran a test on a string field in one of my larger tables (1.5mil rows). Here is what I found: The where like took 32 seconds to return using a clustered
            Message 5 of 25 , Mar 5 4:42 PM
            • 0 Attachment
              I ran a test on a string field in one of my larger tables (1.5mil rows).
              Here is what I found:
              The "where like" took 32 seconds to return using a clustered index.
              The "where substring" took 10 seconds to return using a non-clustered
              index.

              Hope this helps.

              Grant



              -----Original Message-----
              From: SQLQueriesNoCode@yahoogroups.com
              [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
              Sent: Wednesday, March 05, 2008 2:48 PM
              To: SQLQueriesNoCode@yahoogroups.com
              Subject: [SQLQueriesNoCode] WHERE clause question
              Importance: Low



              I have a field that contains data like:

              00-4010-00
              10-4010-00
              20-4010-00
              20-4010-01
              00-5510-00
              Etc

              The only entries I care about will be the pattern nn-4010-nn, in
              this case
              I don't care what the nn's are.

              In a WHERE clause which is faster in your opinion?

              WHERE SUBSTRING(field, 4,4) = '4010'

              OR

              WHERE LIKE %4010%

              Note again this is in the WHERE, not the SELECT. Also if this
              makes a
              difference, the field is VARCHAR not CHAR and it will be hard
              coded like
              this into the proc, it won't be arriving as a parameter from the
              calling
              code.

              Thanks for your performance opinions

              SQL Server 2K On Win2003

              John Warner







              [Non-text portions of this message have been removed]
            • Arnie Rowland
              If this is a query that is used often, you may wish to create a computed column that is equal to the substring() function (as you indicated), and then build an
              Message 6 of 25 , Mar 5 5:47 PM
              • 0 Attachment
                If this is a query that is used often, you may wish to create a computed column that is equal to the substring() function (as you indicated), and then build an index on that computed column.


                Regards,

                - Arnie Rowland, MVP (SQL Server)

                "I am a great believer in luck, and I find that the harder I work, the more I have of it." - Thomas Jefferson (1743-1826)


                -----Original Message-----
                From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                Sent: Wednesday, March 05, 2008 2:51 PM
                To: Arnie
                Subject: [SQLQueriesNoCode] WHERE clause question
                Importance: Low

                I have a field that contains data like:

                00-4010-00
                10-4010-00
                20-4010-00
                20-4010-01
                00-5510-00
                Etc

                The only entries I care about will be the pattern nn-4010-nn, in this case
                I don't care what the nn's are.

                In a WHERE clause which is faster in your opinion?

                WHERE SUBSTRING(field, 4,4) = '4010'

                OR

                WHERE LIKE %4010%

                Note again this is in the WHERE, not the SELECT. Also if this makes a
                difference, the field is VARCHAR not CHAR and it will be hard coded like
                this into the proc, it won't be arriving as a parameter from the calling
                code.

                Thanks for your performance opinions

                SQL Server 2K On Win2003

                John Warner





                Yahoo! Groups Links







                Disclaimer - March 5, 2008
                This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
                This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
              • Paul Livengood
                John; I ran a test that loaded a table with 5 million random values like one s you listed. I then ran a five basic select statements against the table using
                Message 7 of 25 , Mar 5 6:33 PM
                • 0 Attachment
                  John;



                  I ran a test that loaded a table with 5 million random values like one's you
                  listed. I then ran a five basic select statements against the table using
                  SUBSTRING and five using LIKE. Between each selection I cleared all caches
                  to make sure that I was not getting false results. Although the execution
                  time and page reads were about the same, using SUBSTING produced less CPU
                  hits. Based on this I would say that the SUBSTRING would produce slightly
                  better performance.



                  Note: Unless you have full text indexing turned on the query engine will
                  not be able to use an index with either scenario. Because of this I would
                  agree with Arnie Rowland's post; If this is a regular query I would look at
                  adding a computed column to allow for indexing.



                  HTH

                  Paul



                  From: SQLQueriesNoCode@yahoogroups.com
                  [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                  Sent: Wednesday, March 05, 2008 3:48 PM
                  To: SQLQueriesNoCode@yahoogroups.com
                  Subject: [SQLQueriesNoCode] WHERE clause question
                  Importance: Low



                  I have a field that contains data like:

                  00-4010-00
                  10-4010-00
                  20-4010-00
                  20-4010-01
                  00-5510-00
                  Etc

                  The only entries I care about will be the pattern nn-4010-nn, in this case
                  I don't care what the nn's are.

                  In a WHERE clause which is faster in your opinion?

                  WHERE SUBSTRING(field, 4,4) = '4010'

                  OR

                  WHERE LIKE %4010%

                  Note again this is in the WHERE, not the SELECT. Also if this makes a
                  difference, the field is VARCHAR not CHAR and it will be hard coded like
                  this into the proc, it won't be arriving as a parameter from the calling
                  code.

                  Thanks for your performance opinions

                  SQL Server 2K On Win2003

                  John Warner





                  [Non-text portions of this message have been removed]
                • Zeeshan Khan
                  Yeah Michael, thats true. It will perform table scan process and from performance point of view both the queries are not good. I think you should split this
                  Message 8 of 25 , Mar 5 6:44 PM
                  • 0 Attachment
                    Yeah Michael, thats true. It will perform table scan process
                    and from performance point of view both the queries are not good.
                    I think you should split this field in three columns and then create index.

                    Regards
                    Zeeshan Khan



                    ----- Original Message ----
                    From: Michael Weiss <mweiss@...>
                    To: SQLQueriesNoCode@yahoogroups.com
                    Sent: Thursday, 6 March, 2008 5:57:01 AM
                    Subject: RE: [SQLQueriesNoCode] WHERE clause question

                    I am thinking they will both produce a table scan but you would have to
                    test them to see which would actually be faster. I am not sure you can
                    avoid a table scan but I could well be wrong.

                    Best wishes,

                    Michael

                    ____________ _________ _________ __

                    From: SQLQueriesNoCode@ yahoogroups. com
                    [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of John Warner
                    Sent: Wednesday, March 05, 2008 2:48 PM
                    To: SQLQueriesNoCode@ yahoogroups. com
                    Subject: [SQLQueriesNoCode] WHERE clause question
                    Importance: Low

                    I have a field that contains data like:

                    00-4010-00
                    10-4010-00
                    20-4010-00
                    20-4010-01
                    00-5510-00
                    Etc

                    The only entries I care about will be the pattern nn-4010-nn, in this
                    case
                    I don't care what the nn's are.

                    In a WHERE clause which is faster in your opinion?

                    WHERE SUBSTRING(field, 4,4) = '4010'

                    OR

                    WHERE LIKE %4010%

                    Note again this is in the WHERE, not the SELECT. Also if this makes a
                    difference, the field is VARCHAR not CHAR and it will be hard coded like
                    this into the proc, it won't be arriving as a parameter from the calling
                    code.

                    Thanks for your performance opinions

                    SQL Server 2K On Win2003

                    John Warner

                    [Non-text portions of this message have been removed]





                    Why delete messages? Unlimited storage is just a click away. Go to http://help.yahoo.com/l/in/yahoo/mail/yahoomail/tools/tools-08.html

                    [Non-text portions of this message have been removed]
                  • John Warner
                    Just the sort of thing I was wondering. Thanks for running the test. John Warner
                    Message 9 of 25 , Mar 6 3:00 AM
                    • 0 Attachment
                      Just the sort of thing I was wondering. Thanks for running the test.

                      John Warner


                      > -----Original Message-----
                      > From: SQLQueriesNoCode@yahoogroups.com
                      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Yano, Grant
                      > Sent: Wednesday, March 05, 2008 7:43 PM
                      > To: SQLQueriesNoCode@yahoogroups.com
                      > Subject: RE: [SQLQueriesNoCode] WHERE clause question
                      >
                      > I ran a test on a string field in one of my larger tables (1.5mil rows).
                      > Here is what I found:
                      > The "where like" took 32 seconds to return using a clustered index.
                      > The "where substring" took 10 seconds to return using a non-clustered
                      > index.
                      >
                      > Hope this helps.
                      >
                      > Grant
                      >
                      >
                      >
                      > -----Original Message-----
                      > From: SQLQueriesNoCode@yahoogroups.com
                      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                      > Sent: Wednesday, March 05, 2008 2:48 PM
                      > To: SQLQueriesNoCode@yahoogroups.com
                      > Subject: [SQLQueriesNoCode] WHERE clause question
                      > Importance: Low
                      >
                      >
                      >
                      > I have a field that contains data like:
                      >
                      > 00-4010-00
                      > 10-4010-00
                      > 20-4010-00
                      > 20-4010-01
                      > 00-5510-00
                      > Etc
                      >
                      > The only entries I care about will be the pattern nn-4010-nn, in
                      > this case
                      > I don't care what the nn's are.
                      >
                      > In a WHERE clause which is faster in your opinion?
                      >
                      > WHERE SUBSTRING(field, 4,4) = '4010'
                      >
                      > OR
                      >
                      > WHERE LIKE %4010%
                      >
                      > Note again this is in the WHERE, not the SELECT. Also if this
                      > makes a
                      > difference, the field is VARCHAR not CHAR and it will be hard
                      > coded like
                      > this into the proc, it won't be arriving as a parameter from the
                      > calling
                      > code.
                      >
                      > Thanks for your performance opinions
                      >
                      > SQL Server 2K On Win2003
                      >
                      > John Warner
                      >
                      >
                      >
                      >
                      >
                      >
                      >
                      > [Non-text portions of this message have been removed]
                      >
                      >
                      >
                      >
                      > Yahoo! Groups Links
                      >
                      >
                      >
                    • John Warner
                      No choice on the redesign, the design is locked in with a serious front end application already in place, I m just doing some reporting. John Warner ... index.
                      Message 10 of 25 , Mar 6 3:03 AM
                      • 0 Attachment
                        No choice on the redesign, the design is locked in with a serious front
                        end application already in place, I'm just doing some reporting.

                        John Warner


                        > -----Original Message-----
                        > From: SQLQueriesNoCode@yahoogroups.com
                        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Zeeshan Khan
                        > Sent: Wednesday, March 05, 2008 9:44 PM
                        > To: SQLQueriesNoCode@yahoogroups.com
                        > Subject: Re: [SQLQueriesNoCode] WHERE clause question
                        >
                        > Yeah Michael, thats true. It will perform table scan process
                        > and from performance point of view both the queries are not good.
                        > I think you should split this field in three columns and then create
                        index.
                        >
                        > Regards
                        > Zeeshan Khan
                        >
                        >
                        >
                        > ----- Original Message ----
                        > From: Michael Weiss <mweiss@...>
                        > To: SQLQueriesNoCode@yahoogroups.com
                        > Sent: Thursday, 6 March, 2008 5:57:01 AM
                        > Subject: RE: [SQLQueriesNoCode] WHERE clause question
                        >
                        > I am thinking they will both produce a table scan but you would have to
                        > test them to see which would actually be faster. I am not sure you can
                        > avoid a table scan but I could well be wrong.
                        >
                        > Best wishes,
                        >
                        > Michael
                        >
                        > ____________ _________ _________ __
                        >
                        > From: SQLQueriesNoCode@ yahoogroups. com
                        > [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of John Warner
                        > Sent: Wednesday, March 05, 2008 2:48 PM
                        > To: SQLQueriesNoCode@ yahoogroups. com
                        > Subject: [SQLQueriesNoCode] WHERE clause question
                        > Importance: Low
                        >
                        > I have a field that contains data like:
                        >
                        > 00-4010-00
                        > 10-4010-00
                        > 20-4010-00
                        > 20-4010-01
                        > 00-5510-00
                        > Etc
                        >
                        > The only entries I care about will be the pattern nn-4010-nn, in this
                        > case
                        > I don't care what the nn's are.
                        >
                        > In a WHERE clause which is faster in your opinion?
                        >
                        > WHERE SUBSTRING(field, 4,4) = '4010'
                        >
                        > OR
                        >
                        > WHERE LIKE %4010%
                        >
                        > Note again this is in the WHERE, not the SELECT. Also if this makes a
                        > difference, the field is VARCHAR not CHAR and it will be hard coded like
                        > this into the proc, it won't be arriving as a parameter from the calling
                        > code.
                        >
                        > Thanks for your performance opinions
                        >
                        > SQL Server 2K On Win2003
                        >
                        > John Warner
                        >
                        > [Non-text portions of this message have been removed]
                        >
                        >
                        >
                        >
                        >
                        > Why delete messages? Unlimited storage is just a click away. Go to
                        > http://help.yahoo.com/l/in/yahoo/mail/yahoomail/tools/tools-08.html
                        >
                        > [Non-text portions of this message have been removed]
                        >
                        >
                        >
                        >
                        > Yahoo! Groups Links
                        >
                        >
                        >
                      • John Warner
                        Luckily it is just for reporting purposes and not on going transactions. I was just looking to know which would cause the system the least amount of pain . My
                        Message 11 of 25 , Mar 6 3:07 AM
                        • 0 Attachment
                          Luckily it is just for reporting purposes and not on going transactions. I
                          was just looking to know which would cause the system the least amount of
                          'pain'. My report is likely to be run while others are in the system
                          adding data so I want to minimize my impact where I can.

                          Thanks

                          John Warner


                          > -----Original Message-----
                          > From: SQLQueriesNoCode@yahoogroups.com
                          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                          > Sent: Wednesday, March 05, 2008 8:47 PM
                          > To: SQLQueriesNoCode@yahoogroups.com
                          > Subject: RE: [SQLQueriesNoCode] WHERE clause question
                          >
                          > If this is a query that is used often, you may wish to create a computed
                          column
                          > that is equal to the substring() function (as you indicated), and then
                          build an
                          > index on that computed column.
                          >
                          >
                          > Regards,
                          >
                          > - Arnie Rowland, MVP (SQL Server)
                          >
                          > "I am a great believer in luck, and I find that the harder I work, the
                          more I have
                          > of it." - Thomas Jefferson (1743-1826)
                          >
                          >
                          > -----Original Message-----
                          > From: SQLQueriesNoCode@yahoogroups.com
                          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                          > Sent: Wednesday, March 05, 2008 2:51 PM
                          > To: Arnie
                          > Subject: [SQLQueriesNoCode] WHERE clause question
                          > Importance: Low
                          >
                          > I have a field that contains data like:
                          >
                          > 00-4010-00
                          > 10-4010-00
                          > 20-4010-00
                          > 20-4010-01
                          > 00-5510-00
                          > Etc
                          >
                          > The only entries I care about will be the pattern nn-4010-nn, in this
                          case
                          > I don't care what the nn's are.
                          >
                          > In a WHERE clause which is faster in your opinion?
                          >
                          > WHERE SUBSTRING(field, 4,4) = '4010'
                          >
                          > OR
                          >
                          > WHERE LIKE %4010%
                          >
                          > Note again this is in the WHERE, not the SELECT. Also if this makes a
                          > difference, the field is VARCHAR not CHAR and it will be hard coded like
                          > this into the proc, it won't be arriving as a parameter from the calling
                          > code.
                          >
                          > Thanks for your performance opinions
                          >
                          > SQL Server 2K On Win2003
                          >
                          > John Warner
                          >
                          >
                          >
                          >
                          >
                          > Yahoo! Groups Links
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          > Disclaimer - March 5, 2008
                          > This email and any files transmitted with it are confidential and
                          intended solely
                          > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                          > addressee you should not disseminate, distribute, copy or alter this
                          email. Any
                          > views or opinions presented in this email are solely those of the author
                          and
                          > might not represent those of Westwood Consulting, Inc. Warning: Although
                          > Westwood Consulting, Inc has taken reasonable precautions to ensure no
                          > viruses are present in this email, the company cannot accept
                          responsibility for
                          > any loss or damage arising from the use of this email or attachments.
                          > This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
                          >
                          >
                          >
                          > Yahoo! Groups Links
                          >
                          >
                          >
                        • Arnie Rowland
                          John, Since it is only for reporting, you could create a VIEW, and with the VIEW, create a computed column and build an index on the computed column. Then
                          Message 12 of 25 , Mar 6 7:20 AM
                          • 0 Attachment
                            John,

                            Since it is only for reporting, you could create a VIEW, and with the
                            VIEW, create a computed column and build an index on the computed
                            column.

                            Then report from the view.

                            - Arnie Rowland, MVP (SQL Server)

                            "I am a great believer in luck, and I find that the harder I work, the
                            more I have of it." - Thomas Jefferson (1743-1826)



                            -----Original Message-----
                            From: SQLQueriesNoCode@yahoogroups.com
                            [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                            Sent: Thursday, March 06, 2008 3:10 AM
                            To: Arnie
                            Subject: RE: [SQLQueriesNoCode] WHERE clause question
                            Importance: Low

                            Luckily it is just for reporting purposes and not on going transactions.
                            I
                            was just looking to know which would cause the system the least amount
                            of
                            'pain'. My report is likely to be run while others are in the system
                            adding data so I want to minimize my impact where I can.

                            Thanks

                            John Warner


                            > -----Original Message-----
                            > From: SQLQueriesNoCode@yahoogroups.com
                            > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                            > Sent: Wednesday, March 05, 2008 8:47 PM
                            > To: SQLQueriesNoCode@yahoogroups.com
                            > Subject: RE: [SQLQueriesNoCode] WHERE clause question
                            >
                            > If this is a query that is used often, you may wish to create a
                            computed
                            column
                            > that is equal to the substring() function (as you indicated), and then
                            build an
                            > index on that computed column.
                            >
                            >
                            > Regards,
                            >
                            > - Arnie Rowland, MVP (SQL Server)
                            >
                            > "I am a great believer in luck, and I find that the harder I work,
                            the
                            more I have
                            > of it." - Thomas Jefferson (1743-1826)
                            >
                            >
                            > -----Original Message-----
                            > From: SQLQueriesNoCode@yahoogroups.com
                            > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                            > Sent: Wednesday, March 05, 2008 2:51 PM
                            > To: Arnie
                            > Subject: [SQLQueriesNoCode] WHERE clause question
                            > Importance: Low
                            >
                            > I have a field that contains data like:
                            >
                            > 00-4010-00
                            > 10-4010-00
                            > 20-4010-00
                            > 20-4010-01
                            > 00-5510-00
                            > Etc
                            >
                            > The only entries I care about will be the pattern nn-4010-nn, in this
                            case
                            > I don't care what the nn's are.
                            >
                            > In a WHERE clause which is faster in your opinion?
                            >
                            > WHERE SUBSTRING(field, 4,4) = '4010'
                            >
                            > OR
                            >
                            > WHERE LIKE %4010%
                            >
                            > Note again this is in the WHERE, not the SELECT. Also if this makes a
                            > difference, the field is VARCHAR not CHAR and it will be hard coded
                            like
                            > this into the proc, it won't be arriving as a parameter from the
                            calling
                            > code.
                            >
                            > Thanks for your performance opinions
                            >
                            > SQL Server 2K On Win2003
                            >
                            > John Warner
                            >
                            >
                            >
                            >
                            >
                            > Yahoo! Groups Links
                            >
                            >
                            >
                            >
                            >
                            >
                            >
                            > Disclaimer - March 5, 2008
                            > This email and any files transmitted with it are confidential and
                            intended solely
                            > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                            > addressee you should not disseminate, distribute, copy or alter this
                            email. Any
                            > views or opinions presented in this email are solely those of the
                            author
                            and
                            > might not represent those of Westwood Consulting, Inc. Warning:
                            Although
                            > Westwood Consulting, Inc has taken reasonable precautions to ensure no
                            > viruses are present in this email, the company cannot accept
                            responsibility for
                            > any loss or damage arising from the use of this email or attachments.
                            > This disclaimer was added by Policy Patrol:
                            http://www.policypatrol.com/
                            >
                            >
                            >
                            > Yahoo! Groups Links
                            >
                            >
                            >





                            Yahoo! Groups Links
                          • John Warner
                            That is indeed a thought, so far it has not been a big performance hit doing it with the substring. How was the trip to the SQL 2008 dog and pony show a couple
                            Message 13 of 25 , Mar 6 7:36 AM
                            • 0 Attachment
                              That is indeed a thought, so far it has not been a big performance hit
                              doing it with the substring. How was the trip to the SQL 2008 dog and pony
                              show a couple weeks ago?

                              John Warner




                              > -----Original Message-----
                              > From: SQLQueriesNoCode@yahoogroups.com
                              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                              > Sent: Thursday, March 06, 2008 10:21 AM
                              > To: SQLQueriesNoCode@yahoogroups.com
                              > Subject: RE: [SQLQueriesNoCode] WHERE clause question
                              >
                              >
                              > John,
                              >
                              > Since it is only for reporting, you could create a VIEW, and
                              > with the VIEW, create a computed column and build an index on
                              > the computed column.
                              >
                              > Then report from the view.
                              >
                              > - Arnie Rowland, MVP (SQL Server)
                              >
                              > "I am a great believer in luck, and I find that the harder I
                              > work, the more I have of it." - Thomas Jefferson (1743-1826)
                              >
                              >
                              >
                              > -----Original Message-----
                              > From: SQLQueriesNoCode@yahoogroups.com
                              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                              > Sent: Thursday, March 06, 2008 3:10 AM
                              > To: Arnie
                              > Subject: RE: [SQLQueriesNoCode] WHERE clause question
                              > Importance: Low
                              >
                              > Luckily it is just for reporting purposes and not on going
                              > transactions. I was just looking to know which would cause
                              > the system the least amount of 'pain'. My report is likely to
                              > be run while others are in the system adding data so I want
                              > to minimize my impact where I can.
                              >
                              > Thanks
                              >
                              > John Warner
                              >
                              >
                              > > -----Original Message-----
                              > > From: SQLQueriesNoCode@yahoogroups.com
                              > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                              > > Sent: Wednesday, March 05, 2008 8:47 PM
                              > > To: SQLQueriesNoCode@yahoogroups.com
                              > > Subject: RE: [SQLQueriesNoCode] WHERE clause question
                              > >
                              > > If this is a query that is used often, you may wish to create a
                              > computed
                              > column
                              > > that is equal to the substring() function (as you
                              > indicated), and then
                              > build an
                              > > index on that computed column.
                              > >
                              > >
                              > > Regards,
                              > >
                              > > - Arnie Rowland, MVP (SQL Server)
                              > >
                              > > "I am a great believer in luck, and I find that the harder I work,
                              > the
                              > more I have
                              > > of it." - Thomas Jefferson (1743-1826)
                              > >
                              > >
                              > > -----Original Message-----
                              > > From: SQLQueriesNoCode@yahoogroups.com
                              > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                              > > Sent: Wednesday, March 05, 2008 2:51 PM
                              > > To: Arnie
                              > > Subject: [SQLQueriesNoCode] WHERE clause question
                              > > Importance: Low
                              > >
                              > > I have a field that contains data like:
                              > >
                              > > 00-4010-00
                              > > 10-4010-00
                              > > 20-4010-00
                              > > 20-4010-01
                              > > 00-5510-00
                              > > Etc
                              > >
                              > > The only entries I care about will be the pattern
                              > nn-4010-nn, in this
                              > case
                              > > I don't care what the nn's are.
                              > >
                              > > In a WHERE clause which is faster in your opinion?
                              > >
                              > > WHERE SUBSTRING(field, 4,4) = '4010'
                              > >
                              > > OR
                              > >
                              > > WHERE LIKE %4010%
                              > >
                              > > Note again this is in the WHERE, not the SELECT. Also if
                              > this makes a
                              > > difference, the field is VARCHAR not CHAR and it will be hard coded
                              > like
                              > > this into the proc, it won't be arriving as a parameter from the
                              > calling
                              > > code.
                              > >
                              > > Thanks for your performance opinions
                              > >
                              > > SQL Server 2K On Win2003
                              > >
                              > > John Warner
                              > >
                              > >
                              > >
                              > >
                              > >
                              > > Yahoo! Groups Links
                              > >
                              > >
                              > >
                              > >
                              > >
                              > >
                              > >
                              > > Disclaimer - March 5, 2008
                              > > This email and any files transmitted with it are confidential and
                              > intended solely
                              > > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                              > > addressee you should not disseminate, distribute, copy or alter this
                              > email. Any
                              > > views or opinions presented in this email are solely those of the
                              > author
                              > and
                              > > might not represent those of Westwood Consulting, Inc. Warning:
                              > Although
                              > > Westwood Consulting, Inc has taken reasonable precautions
                              > to ensure no
                              > > viruses are present in this email, the company cannot accept
                              > responsibility for
                              > > any loss or damage arising from the use of this email or
                              > attachments.
                              > > This disclaimer was added by Policy Patrol:
                              > http://www.policypatrol.com/
                              > >
                              > >
                              > >
                              > > Yahoo! Groups Links
                              > >
                              > >
                              > >
                              >
                              >
                              >
                              >
                              >
                              > Yahoo! Groups Links
                              >
                              >
                              >
                              >
                              >
                              >
                              >
                              >
                              > Yahoo! Groups Links
                              >
                              >
                              >
                              >
                            • Arnie Rowland
                              Jumpstart was good. Got to meet up with several good people on the SQL DEV Team. I think that the videos of most of the sessions will be available online for
                              Message 14 of 25 , Mar 6 7:47 AM
                              • 0 Attachment
                                Jumpstart was good. Got to meet up with several good people on the SQL
                                DEV Team.

                                I think that the videos of most of the sessions will be available online
                                for public consumption in a few weeks.

                                - Arnie Rowland, MVP (SQL Server)

                                "I am a great believer in luck, and I find that the harder I work, the
                                more I have of it." - Thomas Jefferson (1743-1826)



                                -----Original Message-----
                                From: SQLQueriesNoCode@yahoogroups.com
                                [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                                Sent: Thursday, March 06, 2008 7:40 AM
                                To: Arnie
                                Subject: RE: [SQLQueriesNoCode] WHERE clause question
                                Importance: Low

                                That is indeed a thought, so far it has not been a big performance hit
                                doing it with the substring. How was the trip to the SQL 2008 dog and
                                pony
                                show a couple weeks ago?

                                John Warner




                                > -----Original Message-----
                                > From: SQLQueriesNoCode@yahoogroups.com
                                > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                                > Sent: Thursday, March 06, 2008 10:21 AM
                                > To: SQLQueriesNoCode@yahoogroups.com
                                > Subject: RE: [SQLQueriesNoCode] WHERE clause question
                                >
                                >
                                > John,
                                >
                                > Since it is only for reporting, you could create a VIEW, and
                                > with the VIEW, create a computed column and build an index on
                                > the computed column.
                                >
                                > Then report from the view.
                                >
                                > - Arnie Rowland, MVP (SQL Server)
                                >
                                > "I am a great believer in luck, and I find that the harder I
                                > work, the more I have of it." - Thomas Jefferson (1743-1826)
                                >
                                >
                                >
                                > -----Original Message-----
                                > From: SQLQueriesNoCode@yahoogroups.com
                                > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                                > Sent: Thursday, March 06, 2008 3:10 AM
                                > To: Arnie
                                > Subject: RE: [SQLQueriesNoCode] WHERE clause question
                                > Importance: Low
                                >
                                > Luckily it is just for reporting purposes and not on going
                                > transactions. I was just looking to know which would cause
                                > the system the least amount of 'pain'. My report is likely to
                                > be run while others are in the system adding data so I want
                                > to minimize my impact where I can.
                                >
                                > Thanks
                                >
                                > John Warner
                                >
                                >
                                > > -----Original Message-----
                                > > From: SQLQueriesNoCode@yahoogroups.com
                                > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                                > > Sent: Wednesday, March 05, 2008 8:47 PM
                                > > To: SQLQueriesNoCode@yahoogroups.com
                                > > Subject: RE: [SQLQueriesNoCode] WHERE clause question
                                > >
                                > > If this is a query that is used often, you may wish to create a
                                > computed
                                > column
                                > > that is equal to the substring() function (as you
                                > indicated), and then
                                > build an
                                > > index on that computed column.
                                > >
                                > >
                                > > Regards,
                                > >
                                > > - Arnie Rowland, MVP (SQL Server)
                                > >
                                > > "I am a great believer in luck, and I find that the harder I work,
                                > the
                                > more I have
                                > > of it." - Thomas Jefferson (1743-1826)
                                > >
                                > >
                                > > -----Original Message-----
                                > > From: SQLQueriesNoCode@yahoogroups.com
                                > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                                > > Sent: Wednesday, March 05, 2008 2:51 PM
                                > > To: Arnie
                                > > Subject: [SQLQueriesNoCode] WHERE clause question
                                > > Importance: Low
                                > >
                                > > I have a field that contains data like:
                                > >
                                > > 00-4010-00
                                > > 10-4010-00
                                > > 20-4010-00
                                > > 20-4010-01
                                > > 00-5510-00
                                > > Etc
                                > >
                                > > The only entries I care about will be the pattern
                                > nn-4010-nn, in this
                                > case
                                > > I don't care what the nn's are.
                                > >
                                > > In a WHERE clause which is faster in your opinion?
                                > >
                                > > WHERE SUBSTRING(field, 4,4) = '4010'
                                > >
                                > > OR
                                > >
                                > > WHERE LIKE %4010%
                                > >
                                > > Note again this is in the WHERE, not the SELECT. Also if
                                > this makes a
                                > > difference, the field is VARCHAR not CHAR and it will be hard coded
                                > like
                                > > this into the proc, it won't be arriving as a parameter from the
                                > calling
                                > > code.
                                > >
                                > > Thanks for your performance opinions
                                > >
                                > > SQL Server 2K On Win2003
                                > >
                                > > John Warner
                                > >
                                > >
                                > >
                                > >
                                > >
                                > > Yahoo! Groups Links
                                > >
                                > >
                                > >
                                > >
                                > >
                                > >
                                > >
                                > > Disclaimer - March 5, 2008
                                > > This email and any files transmitted with it are confidential and
                                > intended solely
                                > > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                                > > addressee you should not disseminate, distribute, copy or alter this
                                > email. Any
                                > > views or opinions presented in this email are solely those of the
                                > author
                                > and
                                > > might not represent those of Westwood Consulting, Inc. Warning:
                                > Although
                                > > Westwood Consulting, Inc has taken reasonable precautions
                                > to ensure no
                                > > viruses are present in this email, the company cannot accept
                                > responsibility for
                                > > any loss or damage arising from the use of this email or
                                > attachments.
                                > > This disclaimer was added by Policy Patrol:
                                > http://www.policypatrol.com/
                                > >
                                > >
                                > >
                                > > Yahoo! Groups Links
                                > >
                                > >
                                > >
                                >
                                >
                                >
                                >
                                >
                                > Yahoo! Groups Links
                                >
                                >
                                >
                                >
                                >
                                >
                                >
                                >
                                > Yahoo! Groups Links
                                >
                                >
                                >
                                >





                                Yahoo! Groups Links
                              • Noman Aftab
                                Dear All, I have a table SERVICE as: SERVICE_ID SERVICE_NAME SUNDRY_ACCOUNT SERVICE_CHARGES_ACCOUNT REVENUE_ACCOUNT If a row has a value for e.g.
                                Message 15 of 25 , Mar 7 4:40 AM
                                • 0 Attachment
                                  Dear All,
                                  I have a table SERVICE as:
                                  SERVICE_ID
                                  SERVICE_NAME
                                  SUNDRY_ACCOUNT
                                  SERVICE_CHARGES_ACCOUNT
                                  REVENUE_ACCOUNT

                                  If a row has a value for e.g. '1001202606010' as SUNDRY_ACCOUNT, then it should not have the same value for SERVICE_CHARGES_ACCOUNT and REVENUE_ACCOUNT; and no cell in the table (of last three columns) should contain this value.

                                  i.e. universally unique

                                  using constraints will only check on row basis. unique will also not work here as re-arranging column value will fail...

                                  how is it possible other than programatically checking it or using triggers.



                                  Noman Muhammad Aftab
                                  Software Engineer
                                  Avanza Solutions
                                  Tel: +92 21 567 5240 – 42 (ext: 762)
                                  Fax: +92 21 567 5244

                                  Cell: +92 333 231 0594
                                  URL: http://www.avanzasolutions.com

                                  Email: noman.aftab@...


                                  ---------------------------------
                                  Sent from Yahoo! Mail.
                                  The World 's Favourite Email.

                                  [Non-text portions of this message have been removed]
                                • John Warner
                                  Are you wanting to check for this condition or control inserts to prevent it happening to begin with? DBMS? John Warner
                                  Message 16 of 25 , Mar 7 5:47 AM
                                  • 0 Attachment
                                    Are you wanting to check for this condition or control inserts to prevent
                                    it happening to begin with?

                                    DBMS?

                                    John Warner




                                    > -----Original Message-----
                                    > From: SQLQueriesNoCode@yahoogroups.com
                                    > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab
                                    > Sent: Friday, March 07, 2008 7:41 AM
                                    > To: SQLQueriesNoCode@yahoogroups.com
                                    > Cc: Mustafa Parekh; Mujtaba Kori
                                    > Subject: [SQLQueriesNoCode] Unique Across Multiple Columns
                                    >
                                    >
                                    > Dear All,
                                    > I have a table SERVICE as:
                                    > SERVICE_ID
                                    > SERVICE_NAME
                                    > SUNDRY_ACCOUNT
                                    > SERVICE_CHARGES_ACCOUNT
                                    > REVENUE_ACCOUNT
                                    >
                                    > If a row has a value for e.g. '1001202606010' as
                                    > SUNDRY_ACCOUNT, then it should not have the same value for
                                    > SERVICE_CHARGES_ACCOUNT and REVENUE_ACCOUNT; and no cell in
                                    > the table (of last three columns) should contain this value.
                                    >
                                    > i.e. universally unique
                                    >
                                    > using constraints will only check on row basis. unique will
                                    > also not work here as re-arranging column value will fail...
                                    >
                                    > how is it possible other than programatically checking it
                                    > or using triggers.
                                    >
                                    >
                                    >
                                    > Noman Muhammad Aftab
                                    > Software Engineer
                                    > Avanza Solutions
                                    > Tel: +92 21 567 5240 - 42 (ext: 762)
                                    > Fax: +92 21 567 5244
                                    >
                                    > Cell: +92 333 231 0594
                                    > URL: http://www.avanzasolutions.com
                                    >
                                    > Email: noman.aftab@...
                                    >
                                    >
                                    > ---------------------------------
                                    > Sent from Yahoo! Mail.
                                    > The World 's Favourite Email.
                                    >
                                    > [Non-text portions of this message have been removed]
                                    >
                                    >
                                    >
                                    >
                                    > Yahoo! Groups Links
                                    >
                                    >
                                    >
                                    >
                                  • Noman Aftab
                                    I want to prevent inserts. SQL Server 2000 John Warner wrote: Are you wanting to check for this condition or
                                    Message 17 of 25 , Mar 7 7:25 AM
                                    • 0 Attachment
                                      I want to prevent inserts.

                                      SQL Server 2000

                                      John Warner <john@...> wrote: Are you wanting to check for this condition or control inserts to prevent
                                      it happening to begin with?

                                      DBMS?

                                      John Warner

                                      > -----Original Message-----
                                      > From: SQLQueriesNoCode@yahoogroups.com
                                      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Noman Aftab
                                      > Sent: Friday, March 07, 2008 7:41 AM
                                      > To: SQLQueriesNoCode@yahoogroups.com
                                      > Cc: Mustafa Parekh; Mujtaba Kori
                                      > Subject: [SQLQueriesNoCode] Unique Across Multiple Columns
                                      >
                                      >
                                      > Dear All,
                                      > I have a table SERVICE as:
                                      > SERVICE_ID
                                      > SERVICE_NAME
                                      > SUNDRY_ACCOUNT
                                      > SERVICE_CHARGES_ACCOUNT
                                      > REVENUE_ACCOUNT
                                      >
                                      > If a row has a value for e.g. '1001202606010' as
                                      > SUNDRY_ACCOUNT, then it should not have the same value for
                                      > SERVICE_CHARGES_ACCOUNT and REVENUE_ACCOUNT; and no cell in
                                      > the table (of last three columns) should contain this value.
                                      >
                                      > i.e. universally unique
                                      >
                                      > using constraints will only check on row basis. unique will
                                      > also not work here as re-arranging column value will fail...
                                      >
                                      > how is it possible other than programatically checking it
                                      > or using triggers.
                                      >
                                      >
                                      >
                                      > Noman Muhammad Aftab
                                      > Software Engineer
                                      > Avanza Solutions
                                      > Tel: +92 21 567 5240 - 42 (ext: 762)
                                      > Fax: +92 21 567 5244
                                      >
                                      > Cell: +92 333 231 0594
                                      > URL: http://www.avanzasolutions.com
                                      >
                                      > Email: noman.aftab@...
                                      >
                                      >
                                      > ---------------------------------
                                      > Sent from Yahoo! Mail.
                                      > The World 's Favourite Email.
                                      >
                                      > [Non-text portions of this message have been removed]
                                      >
                                      >
                                      >
                                      >
                                      > Yahoo! Groups Links
                                      >
                                      >
                                      >
                                      >







                                      Noman Muhammad Aftab
                                      Software Engineer
                                      Avanza Solutions
                                      Tel: +92 21 567 5240 – 42 (ext: 762)
                                      Fax: +92 21 567 5244

                                      Cell: +92 333 231 0594
                                      URL: http://www.avanzasolutions.com

                                      Email: noman.aftab@...


                                      ---------------------------------
                                      Rise to the challenge for Sport Relief with Yahoo! for Good

                                      [Non-text portions of this message have been removed]
                                    • John Warner
                                      I have a rather large query in a proc that I would like to be able to adjust how many fields (columns) are returned. Let me show you and I suspect it will be
                                      Message 18 of 25 , Mar 7 1:21 PM
                                      • 0 Attachment
                                        I have a rather large query in a proc that I would like to be able to
                                        adjust how many fields (columns) are returned. Let me show you and I
                                        suspect it will be clear what I would like to do:

                                        SELECT SUM(field1), field2, If condition = true then field3 else nothing
                                        FROM blah blah join etc
                                        GROUP BY
                                        Field2 if condition = true then field3

                                        Is there a syntax that will allow this or can I have an arrangement like
                                        If Condition SELECT 1,2,3
                                        ELSE
                                        SELECT 1,2

                                        FROM blah

                                        If condition GROUP BY 2,3
                                        Else
                                        GROUP BY 2

                                        Is this a pipe dream on my part or can this be done? I confess from what
                                        I've read I suspect no, but I'm hoping one of you (well almost anyone on
                                        this list will fit this bill) that is smarter and knows SQL better then me
                                        can show me how it can be done. Note I'm open to CASE if that will work I'm
                                        just using IF to demonstrate.

                                        SQL 2K on Win 2003

                                        Thank you

                                        John Warner
                                      • Michael Weiss
                                        I think you need to have two queries John - you can run them under an IF...THEN logic flow but you can t put a CASE or IF in the GROUP BY as far as I know. In
                                        Message 19 of 25 , Mar 7 1:49 PM
                                        • 0 Attachment
                                          I think you need to have two queries John - you can run them under an
                                          IF...THEN logic flow but you can't put a CASE or IF in the GROUP BY as
                                          far as I know.

                                          In your sproc:

                                          CASE WHEN x THEN

                                          SELECT blah blah...

                                          ELSE

                                          SELECT blah blah blah

                                          END



                                          Of course you can always have a CASE statement in your SELECT for
                                          returning a column such as SELECT field1, field2, CASE WHEN field3 = x
                                          THEN field3 ELSE NULL END AS field3 FROM...blah blah.

                                          Hope this helps,

                                          Michael



                                          ________________________________

                                          From: SQLQueriesNoCode@yahoogroups.com
                                          [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                                          Sent: Friday, March 07, 2008 1:22 PM
                                          To: SQLQueriesNoCode@yahoogroups.com
                                          Subject: [SQLQueriesNoCode] Conditional SELECT question
                                          Importance: Low



                                          I have a rather large query in a proc that I would like to be able to
                                          adjust how many fields (columns) are returned. Let me show you and I
                                          suspect it will be clear what I would like to do:

                                          SELECT SUM(field1), field2, If condition = true then field3 else nothing
                                          FROM blah blah join etc
                                          GROUP BY
                                          Field2 if condition = true then field3

                                          Is there a syntax that will allow this or can I have an arrangement like
                                          If Condition SELECT 1,2,3
                                          ELSE
                                          SELECT 1,2

                                          FROM blah

                                          If condition GROUP BY 2,3
                                          Else
                                          GROUP BY 2

                                          Is this a pipe dream on my part or can this be done? I confess from what
                                          I've read I suspect no, but I'm hoping one of you (well almost anyone on
                                          this list will fit this bill) that is smarter and knows SQL better then
                                          me
                                          can show me how it can be done. Note I'm open to CASE if that will work
                                          I'm
                                          just using IF to demonstrate.

                                          SQL 2K on Win 2003

                                          Thank you

                                          John Warner





                                          [Non-text portions of this message have been removed]
                                        • John Warner
                                          It helps, you confirmed what I didn t want to hear. Oh, and you can have a case in a GROUP BY, but in the same form as you show in your SELECT example. I want
                                          Message 20 of 25 , Mar 7 1:58 PM
                                          • 0 Attachment
                                            It helps, you confirmed what I didn't want to hear. Oh, and you can have a
                                            case in a GROUP BY, but in the same form as you show in your SELECT
                                            example. I want to do away with the null so that the column just isn't
                                            there at all based on the condition.

                                            Thanks, have a good weekend.

                                            John Warner




                                            > -----Original Message-----
                                            > From: SQLQueriesNoCode@yahoogroups.com
                                            > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Michael Weiss
                                            > Sent: Friday, March 07, 2008 4:50 PM
                                            > To: SQLQueriesNoCode@yahoogroups.com
                                            > Subject: RE: [SQLQueriesNoCode] Conditional SELECT question
                                            >
                                            >
                                            > I think you need to have two queries John - you can run them
                                            > under an IF...THEN logic flow but you can't put a CASE or IF
                                            > in the GROUP BY as far as I know.
                                            >
                                            > In your sproc:
                                            >
                                            > CASE WHEN x THEN
                                            >
                                            > SELECT blah blah...
                                            >
                                            > ELSE
                                            >
                                            > SELECT blah blah blah
                                            >
                                            > END
                                            >
                                            >
                                            >
                                            > Of course you can always have a CASE statement in your SELECT
                                            > for returning a column such as SELECT field1, field2, CASE
                                            > WHEN field3 = x THEN field3 ELSE NULL END AS field3 FROM...blah blah.
                                            >
                                            > Hope this helps,
                                            >
                                            > Michael
                                            >
                                            >
                                            >
                                            > ________________________________
                                            >
                                            > From: SQLQueriesNoCode@yahoogroups.com
                                            > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                                            > Sent: Friday, March 07, 2008 1:22 PM
                                            > To: SQLQueriesNoCode@yahoogroups.com
                                            > Subject: [SQLQueriesNoCode] Conditional SELECT question
                                            > Importance: Low
                                            >
                                            >
                                            >
                                            > I have a rather large query in a proc that I would like to be
                                            > able to adjust how many fields (columns) are returned. Let me
                                            > show you and I suspect it will be clear what I would like to do:
                                            >
                                            > SELECT SUM(field1), field2, If condition = true then field3
                                            > else nothing FROM blah blah join etc GROUP BY Field2 if
                                            > condition = true then field3
                                            >
                                            > Is there a syntax that will allow this or can I have an
                                            > arrangement like If Condition SELECT 1,2,3 ELSE SELECT 1,2
                                            >
                                            > FROM blah
                                            >
                                            > If condition GROUP BY 2,3
                                            > Else
                                            > GROUP BY 2
                                            >
                                            > Is this a pipe dream on my part or can this be done? I
                                            > confess from what I've read I suspect no, but I'm hoping one
                                            > of you (well almost anyone on this list will fit this bill)
                                            > that is smarter and knows SQL better then me can show me how
                                            > it can be done. Note I'm open to CASE if that will work I'm
                                            > just using IF to demonstrate.
                                            >
                                            > SQL 2K on Win 2003
                                            >
                                            > Thank you
                                            >
                                            > John Warner
                                            >
                                            >
                                            >
                                            >
                                            >
                                            > [Non-text portions of this message have been removed]
                                            >
                                            >
                                            >
                                            >
                                            > Yahoo! Groups Links
                                            >
                                            >
                                            >
                                            >
                                          • Michael Weiss
                                            You could always write it with dynamic sql, John. Not the prettiest solution but sometimes it has its place. Best Regards, Michael
                                            Message 21 of 25 , Mar 7 2:33 PM
                                            • 0 Attachment
                                              You could always write it with dynamic sql, John. Not the prettiest
                                              solution but sometimes it has its place.

                                              Best Regards,

                                              Michael



                                              ________________________________

                                              From: SQLQueriesNoCode@yahoogroups.com
                                              [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                                              Sent: Friday, March 07, 2008 1:59 PM
                                              To: SQLQueriesNoCode@yahoogroups.com
                                              Subject: RE: [SQLQueriesNoCode] Conditional SELECT question
                                              Importance: Low



                                              It helps, you confirmed what I didn't want to hear. Oh, and you can have
                                              a
                                              case in a GROUP BY, but in the same form as you show in your SELECT
                                              example. I want to do away with the null so that the column just isn't
                                              there at all based on the condition.

                                              Thanks, have a good weekend.

                                              John Warner

                                              > -----Original Message-----
                                              > From: SQLQueriesNoCode@yahoogroups.com
                                              <mailto:SQLQueriesNoCode%40yahoogroups.com>
                                              > [mailto:SQLQueriesNoCode@yahoogroups.com
                                              <mailto:SQLQueriesNoCode%40yahoogroups.com> ] On Behalf Of Michael Weiss
                                              > Sent: Friday, March 07, 2008 4:50 PM
                                              > To: SQLQueriesNoCode@yahoogroups.com
                                              <mailto:SQLQueriesNoCode%40yahoogroups.com>
                                              > Subject: RE: [SQLQueriesNoCode] Conditional SELECT question
                                              >
                                              >
                                              > I think you need to have two queries John - you can run them
                                              > under an IF...THEN logic flow but you can't put a CASE or IF
                                              > in the GROUP BY as far as I know.
                                              >
                                              > In your sproc:
                                              >
                                              > CASE WHEN x THEN
                                              >
                                              > SELECT blah blah...
                                              >
                                              > ELSE
                                              >
                                              > SELECT blah blah blah
                                              >
                                              > END
                                              >
                                              >
                                              >
                                              > Of course you can always have a CASE statement in your SELECT
                                              > for returning a column such as SELECT field1, field2, CASE
                                              > WHEN field3 = x THEN field3 ELSE NULL END AS field3 FROM...blah blah.
                                              >
                                              > Hope this helps,
                                              >
                                              > Michael
                                              >
                                              >
                                              >
                                              > ________________________________
                                              >
                                              > From: SQLQueriesNoCode@yahoogroups.com
                                              <mailto:SQLQueriesNoCode%40yahoogroups.com>
                                              > [mailto:SQLQueriesNoCode@yahoogroups.com
                                              <mailto:SQLQueriesNoCode%40yahoogroups.com> ] On Behalf Of John Warner
                                              > Sent: Friday, March 07, 2008 1:22 PM
                                              > To: SQLQueriesNoCode@yahoogroups.com
                                              <mailto:SQLQueriesNoCode%40yahoogroups.com>
                                              > Subject: [SQLQueriesNoCode] Conditional SELECT question
                                              > Importance: Low
                                              >
                                              >
                                              >
                                              > I have a rather large query in a proc that I would like to be
                                              > able to adjust how many fields (columns) are returned. Let me
                                              > show you and I suspect it will be clear what I would like to do:
                                              >
                                              > SELECT SUM(field1), field2, If condition = true then field3
                                              > else nothing FROM blah blah join etc GROUP BY Field2 if
                                              > condition = true then field3
                                              >
                                              > Is there a syntax that will allow this or can I have an
                                              > arrangement like If Condition SELECT 1,2,3 ELSE SELECT 1,2
                                              >
                                              > FROM blah
                                              >
                                              > If condition GROUP BY 2,3
                                              > Else
                                              > GROUP BY 2
                                              >
                                              > Is this a pipe dream on my part or can this be done? I
                                              > confess from what I've read I suspect no, but I'm hoping one
                                              > of you (well almost anyone on this list will fit this bill)
                                              > that is smarter and knows SQL better then me can show me how
                                              > it can be done. Note I'm open to CASE if that will work I'm
                                              > just using IF to demonstrate.
                                              >
                                              > SQL 2K on Win 2003
                                              >
                                              > Thank you
                                              >
                                              > John Warner
                                              >
                                              >
                                              >
                                              >
                                              >
                                              > [Non-text portions of this message have been removed]
                                              >
                                              >
                                              >
                                              >
                                              > Yahoo! Groups Links
                                              >
                                              >
                                              >
                                              >





                                              [Non-text portions of this message have been removed]
                                            • O S
                                              Hi I like to know how to do something like this table: orderid, orderitemid, stockid, priceOfOrder 1 1 1 200 1 2 2 200 3 4 4 300 then how can i do a
                                              Message 22 of 25 , Mar 10 10:46 AM
                                              • 0 Attachment
                                                Hi
                                                I like to know how to do something like this

                                                table:
                                                orderid, orderitemid, stockid, priceOfOrder

                                                1 1 1 200
                                                1 2 2 200
                                                3 4 4 300


                                                then how can i do a sum on priceOfOrder
                                                it gives me 400 rather then 200 for order "1".

                                                thanks.


                                                _________________________________________________________________
                                                Get Hotmail on your mobile, text MSN to 63463!
                                                http://mobile.uk.msn.com/pc/mail.aspx

                                                [Non-text portions of this message have been removed]
                                              • John Warner
                                                400 is correct based on what you have told us. SELECT SUM(priceOfOrder), orderid FROM table WHERE orderid = 1 GROUP BY orderid John Warner
                                                Message 23 of 25 , Mar 10 10:53 AM
                                                • 0 Attachment
                                                  400 is correct based on what you have told us.

                                                  SELECT SUM(priceOfOrder), orderid
                                                  FROM table
                                                  WHERE orderid = 1
                                                  GROUP BY orderid

                                                  John Warner




                                                  > -----Original Message-----
                                                  > From: SQLQueriesNoCode@yahoogroups.com
                                                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of O S
                                                  > Sent: Monday, March 10, 2008 1:46 PM
                                                  > To: sqlqueriesnocode@yahoogroups.com
                                                  > Subject: [SQLQueriesNoCode] sum() on key id
                                                  >
                                                  >
                                                  >
                                                  >
                                                  > Hi
                                                  > I like to know how to do something like this
                                                  >
                                                  > table:
                                                  > orderid, orderitemid, stockid, priceOfOrder
                                                  >
                                                  > 1 1 1 200
                                                  > 1 2 2 200
                                                  > 3 4 4 300
                                                  >
                                                  >
                                                  > then how can i do a sum on priceOfOrder
                                                  > it gives me 400 rather then 200 for order "1".
                                                  >
                                                  > thanks.
                                                  >
                                                  >
                                                  > _________________________________________________________________
                                                  > Get Hotmail on your mobile, text MSN to 63463!
                                                  > http://mobile.uk.msn.com/pc/mail.aspx
                                                  >
                                                  > [Non-text portions of this message have been removed]
                                                  >
                                                  >
                                                  >
                                                  >
                                                  > Yahoo! Groups Links
                                                  >
                                                  >
                                                  >
                                                  >
                                                • Carmen Popescu
                                                  I m not sure if i understand what you want but maybe this is what you need: Select sum(priceOrder) from orders group by orderId O S
                                                  Message 24 of 25 , Mar 10 10:54 AM
                                                  • 0 Attachment
                                                    I'm not sure if i understand what you want but maybe this is what you need:

                                                    Select sum(priceOrder)
                                                    from orders
                                                    group by orderId


                                                    O S <owaiz_vaiyani@...> wrote:

                                                    Hi
                                                    I like to know how to do something like this

                                                    table:
                                                    orderid, orderitemid, stockid, priceOfOrder

                                                    1 1 1 200
                                                    1 2 2 200
                                                    3 4 4 300


                                                    then how can i do a sum on priceOfOrder
                                                    it gives me 400 rather then 200 for order "1".

                                                    thanks.


                                                    __________________________________________________________
                                                    Get Hotmail on your mobile, text MSN to 63463!
                                                    http://mobile.uk.msn.com/pc/mail.aspx

                                                    [Non-text portions of this message have been removed]






                                                    ---------------------------------------
                                                    Carmen Popescu
                                                    "Gheorghe Lazar" National College
                                                    Sibiu - Romania
                                                    http://www.pc-info.ro

                                                    ---------------------------------
                                                    Never miss a thing. Make Yahoo your homepage.

                                                    [Non-text portions of this message have been removed]
                                                  • Noman Aftab
                                                    PriceOfOrder should be in the Order table. Your schema is not normalized. Anyway, here is your query: SELECT ISNULL(SUM(amount), 0) FROM (SELECT
                                                    Message 25 of 25 , Mar 11 9:32 AM
                                                    • 0 Attachment
                                                      PriceOfOrder should be in the Order table. Your schema is not normalized. Anyway, here is your query:

                                                      SELECT ISNULL(SUM(amount), 0)
                                                      FROM (SELECT DISTINCT id, amount
                                                      FROM Table1
                                                      WHERE (id = 1)) derivedTable

                                                      base on assumption that for every record where OrderId is 1, the amount is the same.

                                                      another solution based on same assumption:

                                                      SELECT ISNULL(SUM(amount), 0)
                                                      FROM (SELECT TOP 1 id, amount
                                                      FROM Table1
                                                      WHERE (id = 1)) derivedTable

                                                      O S <owaiz_vaiyani@...> wrote:

                                                      Hi
                                                      I like to know how to do something like this

                                                      table:
                                                      orderid, orderitemid, stockid, priceOfOrder

                                                      1 1 1 200
                                                      1 2 2 200
                                                      3 4 4 300


                                                      then how can i do a sum on priceOfOrder
                                                      it gives me 400 rather then 200 for order "1".

                                                      thanks.


                                                      __________________________________________________________
                                                      Get Hotmail on your mobile, text MSN to 63463!
                                                      http://mobile.uk.msn.com/pc/mail.aspx

                                                      [Non-text portions of this message have been removed]






                                                      ---------------------------------
                                                      Rise to the challenge for Sport Relief with Yahoo! for Good

                                                      [Non-text portions of this message have been removed]
                                                    Your message has been successfully submitted and would be delivered to recipients shortly.