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

RE: [SQLQueriesNoCode] A question of using table alias

Expand Messages
  • Paul Anca
    Hi, group by clause is to be used together with aggregate function such as sum(), max() i.e. select sum(money) from B2 group by sth , maybe here is the
    Message 1 of 9 , Oct 31, 2007
    • 0 Attachment
      Hi,

      "group by" clause is to be used together with aggregate function such as sum(), max() i.e. " select sum(money) from B2 group by sth ", maybe here is the problem, I'm not seeing something like this in your example code.

      Paul Anca  

      paul@...
      www.ancasolutions.com



       



      hello everybody. ^_^

      i have write a sql like this:

      select * from
      (select sth from A1) B1,
      (select sth from A2) B2,
      (select sth from B2 group by sth)
      where
      condition 1;
      condition 2;
      ...

      when i put it to into execution... it shows that the B2 table was
      not exists...

      i hope some body can teach me how to use B2, in the coming goup by
      sqls... ^_^

      thanks so much!!



      [Non-text portions of this message have been removed]
    • John Warner
      I have a stored proc with several parameters. I provide default values for the params. Example: CREATE PROC MyTest ( @p1 varchar(10) = % , @p2 varchar(5) =
      Message 2 of 9 , Nov 1, 2007
      • 0 Attachment
        I have a stored proc with several parameters. I provide default values for
        the params. Example:

        CREATE PROC MyTest (
        @p1 varchar(10) = '%',
        @p2 varchar(5) = 'value' )
        As blah blah
        WHERE field LIKE @p1 AND field2 Like @p2


        Exec MyTest 'Value', 'AnotherValue'

        Result set returned all is well.

        Exec MyTest '', 'AnotherValue'

        Error.

        What am I doing wrong? What I mean is I want these two parameters to be
        optional and if omitted to use my default values. I don't want to drop
        them out of the where clause, I want them to revert to my default. My other
        issue is what do I do for a place holder. My real proc that is in question
        here has about a dozen parameters, I'd like to supply values at times to
        only a few.

        Hope I'm being clear here. Thanks.

        (SQL Server 2K)

        John Warner
      • Arnie Rowland
        John, Here is my teaching example of overloaded or optional parameters. -- Demonstrates the use of optional parameters (Overloading) USE Northwind
        Message 3 of 9 , Nov 1, 2007
        • 0 Attachment
          John,

          Here is my 'teaching' example of 'overloaded' or 'optional' parameters.

          -- >>>> Demonstrates the use of optional parameters (Overloading)

          USE Northwind
          GO

          IF EXISTS
          ( SELECT ROUTINE_NAME
          FROM INFORMATION_SCHEMA.ROUTINES
          WHERE ROUTINE_NAME = 'EmployeesSELECT '
          )
          DROP PROCEDURE dbo.EmployeesSELECT
          GO

          CREATE PROCEDURE dbo. EmployeesSELECT
          ( @EmployeeID int = NULL
          , @FirstName varchar(20) = NULL
          , @LastName varchar(20) = NULL
          )
          AS

          SELECT
          EmployeeID
          , FirstName
          , LastName
          FROM Employees
          WHERE ( EmployeeID = coalesce( @EmployeeID, EmployeeID )
          AND FirstName = coalesce( @FirstName, FirstName )
          AND LastName = coalesce( @LastName, LastName )
          )
          GO

          EXECUTE dbo.EmployeesSELECT @EmployeeID = 2
          EXECUTE dbo.EmployeesSELECT @LastName = 'Fuller'
          EXECUTE dbo.EmployeesSELECT @FirstName = 'Nancy'

          IF this is not quite what you need, let me know.

          (Note: this is method usually does NOT create an efficient execution
          plan. Performance may be improved by add the [ WITH RECOMPILE ] option.)


          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: Thursday, November 01, 2007 7:42 AM
          To: Arnie
          Subject: [SQLQueriesNoCode] Default values in a T-SQL proc ??

          I have a stored proc with several parameters. I provide default values
          for
          the params. Example:

          CREATE PROC MyTest (
          @p1 varchar(10) = '%',
          @p2 varchar(5) = 'value' )
          As blah blah
          WHERE field LIKE @p1 AND field2 Like @p2


          Exec MyTest 'Value', 'AnotherValue'

          Result set returned all is well.

          Exec MyTest '', 'AnotherValue'

          Error.

          What am I doing wrong? What I mean is I want these two parameters to be
          optional and if omitted to use my default values. I don't want to drop
          them out of the where clause, I want them to revert to my default. My
          other
          issue is what do I do for a place holder. My real proc that is in
          question
          here has about a dozen parameters, I'd like to supply values at times to
          only a few.

          Hope I'm being clear here. Thanks.

          (SQL Server 2K)

          John Warner





          Yahoo! Groups Links







          Disclaimer - November 1, 2007
          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
          Performance isn t really an issue here (creating reports, as long as it doesn t consume and entire lunch hour ). This gets it. Thanks. I was not using
          Message 4 of 9 , Nov 1, 2007
          • 0 Attachment
            Performance isn't really an issue here (creating reports, as long as it
            doesn't consume and entire lunch hour <grin>). This gets it. Thanks. I was
            not using the right calling syntax and just could not get my brain around
            named params. In a couple of the defaults I actually have to have more then
            null or the wild card, that was why I really wasn't looking at coalesce or
            isnull. But you provided me with the kick in the head I needed.

            Thanks again!

            John Warner




            > -----Original Message-----
            > From: SQLQueriesNoCode@yahoogroups.com
            > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
            > Sent: Thursday, November 01, 2007 10:53 AM
            > To: SQLQueriesNoCode@yahoogroups.com
            > Subject: RE: [SQLQueriesNoCode] Default values in a T-SQL proc ??
            >
            >
            > John,
            >
            > Here is my 'teaching' example of 'overloaded' or 'optional'
            > parameters.
            >
            > -- >>>> Demonstrates the use of optional parameters (Overloading)
            >
            > USE Northwind
            > GO
            >
            > IF EXISTS
            > ( SELECT ROUTINE_NAME
            > FROM INFORMATION_SCHEMA.ROUTINES
            > WHERE ROUTINE_NAME = 'EmployeesSELECT '
            > )
            > DROP PROCEDURE dbo.EmployeesSELECT
            > GO
            >
            > CREATE PROCEDURE dbo. EmployeesSELECT
            > ( @EmployeeID int = NULL
            > , @FirstName varchar(20) = NULL
            > , @LastName varchar(20) = NULL
            > )
            > AS
            >
            > SELECT
            > EmployeeID
            > , FirstName
            > , LastName
            > FROM Employees
            > WHERE ( EmployeeID = coalesce( @EmployeeID, EmployeeID )
            > AND FirstName = coalesce( @FirstName, FirstName )
            > AND LastName = coalesce( @LastName, LastName )
            > )
            > GO
            >
            > EXECUTE dbo.EmployeesSELECT @EmployeeID = 2
            > EXECUTE dbo.EmployeesSELECT @LastName = 'Fuller'
            > EXECUTE dbo.EmployeesSELECT @FirstName = 'Nancy'
            >
            > IF this is not quite what you need, let me know.
            >
            > (Note: this is method usually does NOT create an efficient
            > execution plan. Performance may be improved by add the [ WITH
            > RECOMPILE ] option.)
            >
            >
            > 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: Thursday, November 01, 2007 7:42 AM
            > To: Arnie
            > Subject: [SQLQueriesNoCode] Default values in a T-SQL proc ??
            >
            > I have a stored proc with several parameters. I provide
            > default values for the params. Example:
            >
            > CREATE PROC MyTest (
            > @p1 varchar(10) = '%',
            > @p2 varchar(5) = 'value' )
            > As blah blah
            > WHERE field LIKE @p1 AND field2 Like @p2
            >
            >
            > Exec MyTest 'Value', 'AnotherValue'
            >
            > Result set returned all is well.
            >
            > Exec MyTest '', 'AnotherValue'
            >
            > Error.
            >
            > What am I doing wrong? What I mean is I want these two
            > parameters to be optional and if omitted to use my default
            > values. I don't want to drop them out of the where clause, I
            > want them to revert to my default. My other issue is what do
            > I do for a place holder. My real proc that is in question
            > here has about a dozen parameters, I'd like to supply values
            > at times to only a few.
            >
            > Hope I'm being clear here. Thanks.
            >
            > (SQL Server 2K)
            >
            > John Warner
            >
            >
          • Dinesh Agrawal
            hi, it is working fine at my end. I am using sql 2000. Thanks & Regards: Dinesh K. Agrawal To: SQLQueriesNoCode@yahoogroups.comFrom: john@jwarner.comDate: Thu,
            Message 5 of 9 , Nov 1, 2007
            • 0 Attachment
              hi,

              it is working fine at my end. I am using sql 2000.

              Thanks & Regards:
              Dinesh K. Agrawal


              To: SQLQueriesNoCode@yahoogroups.comFrom: john@...: Thu, 1 Nov 2007 10:40:31 -0400Subject: [SQLQueriesNoCode] Default values in a T-SQL proc ??




              I have a stored proc with several parameters. I provide default values forthe params. Example:CREATE PROC MyTest (@p1 varchar(10) = '%',@p2 varchar(5) = 'value' )As blah blahWHERE field LIKE @p1 AND field2 Like @p2Exec MyTest 'Value', 'AnotherValue'Result set returned all is well.Exec MyTest '', 'AnotherValue'Error.What am I doing wrong? What I mean is I want these two parameters to beoptional and if omitted to use my default values. I don't want to dropthem out of the where clause, I want them to revert to my default. My otherissue is what do I do for a place holder. My real proc that is in questionhere has about a dozen parameters, I'd like to supply values at times toonly a few.Hope I'm being clear here. Thanks.(SQL Server 2K)John Warner







              [Non-text portions of this message have been removed]
            • Charles Carroll
              http://blogs.aspfriends.com/afblogs/blogs/insightdb/archive/2006/08/04/26.aspx has exactly what you want. To quote from Doug Nelson s blog entry: Here is a
              Message 6 of 9 , Nov 4, 2007
              • 0 Attachment
                http://blogs.aspfriends.com/afblogs/blogs/insightdb/archive/2006/08/04/26.aspx
                has exactly what you want.

                To quote from Doug Nelson's blog entry:
                Here is a very simple trick to allow your stored procedures to have
                optional parameters. By assigning default values to the parameters of
                the procedure you can get a lot of mileage out of a single query.
                This is particularly useful for searching based on various criteria.

                For example

                create procedure [dbo].[sp_search_books]
                @ISBN varchar(15) = null,
                @AuthorLastName varchar(50) = '',
                @Publisher varchar(50) = ''
                @Title varchar(50) = ''
                as

                select
                ISBN,
                Author_FirstName,
                Auther_LastName,
                Title,
                Publisher
                from
                BookTable
                where
                ISBN = IsNull(@ISBN, ISBN),
                and Author_FirstName like
                case @AuthorFirstName when '' then Author_FirstName
                else @AuthorFirstName +'%' end
                and Author_LastName like
                case @AuthorLastName when '' then Author_LastName
                else @AuthorLastName +'%' end
                and Publisher like
                case @Publisher when '' then Publisher
                else @Publisher + '%' end
                and Title like
                case @Title when '' then Title
                else '%' + @Title + '%' end

                This single query can search for books by any combination of the
                parameters. The conditions in the where clause are such that when the
                default value is used, the condition will always be true. So to search
                for books by a publisher starting with 'Apr' the call would look like
                the following:

                sp_search_books null,'','','Apr'

                The conditional logic contains a couple of different ways to do this
                and they are both equivalent.

                First, if the default value of a parameter is null then the IsNull
                construct of the first condition is a simple way to implement this.

                In the second example, the case statement is used to test for the
                default value and simply subsitute the field value in place of the
                parameter value, if the parameter value is something other than the
                default, then the parameter value is used instead.

                For the authors name and publisher, the condition is a "starts with"
                match. For the title the condition is a "contains" match.

                One more thing to remember, in SQL, a null = null is not a match. So
                if the data columns are allowed to have null values, we need to use
                IsNull to supply a dummy value for the match and in that case the
                syntax for the condition will be as follows:

                and IsNull(Author_FirstName,'') like
                case @AuthorFirstName when '' then IsNull(Author_FirstName,'')
                else @AuthorFirstName +'%' end

                Not everyone has a first name so we allow that field to be null. If
                the value of the author field is null, if will be convert to an empty
                string so instead of null = null which does not match, we now have
                ''='' ( 'empty string' = 'empty string' ) which does match.

                On Nov 1, 2007 9:40 AM, John Warner <john@...> wrote:
                >
                >
                >
                >
                >
                >
                > I have a stored proc with several parameters. I provide default values for
                > the params. Example:
                >
                > CREATE PROC MyTest (
                > @p1 varchar(10) = '%',
                > @p2 varchar(5) = 'value' )
                > As blah blah
                > WHERE field LIKE @p1 AND field2 Like @p2
                >
                > Exec MyTest 'Value', 'AnotherValue'
                >
                > Result set returned all is well.
                >
                > Exec MyTest '', 'AnotherValue'
                >
                > Error.
                >
                > What am I doing wrong? What I mean is I want these two parameters to be
                > optional and if omitted to use my default values. I don't want to drop
                > them out of the where clause, I want them to revert to my default. My other
                > issue is what do I do for a place holder. My real proc that is in question
                > here has about a dozen parameters, I'd like to supply values at times to
                > only a few.
                >
                > Hope I'm being clear here. Thanks.
                >
                > (SQL Server 2K)
                >
                > John Warner
                >
                >
              • monojit aich
                I can give u an example ... create proc aa ( @a varchar(100)= % , @b varchar(10)= tt ) as select * from sysobjects where name like @a or name like @b here
                Message 7 of 9 , Nov 5, 2007
                • 0 Attachment
                  I can give u an example ...

                  create proc aa
                  (
                  @a varchar(100)='%',
                  @b varchar(10)='tt'
                  )
                  as
                  select * from sysobjects
                  where name like @a or name like @b


                  here both the input parameters are optional ,there are several ways to execute..

                  exec aa '%sys%','abc'
                  exec aa '','23'
                  exec aa -- both take default
                  exec aa @a='%sys%' --2nd parameter take default
                  exec aa @b='%sys%' --1st parameter take default
                  exec aa '%sys%' --2nd parameter take default


                  I didnt find any wrong on calling convention as u mentioned Exec MyTest '', 'AnotherValue'



                  ----- Original Message ----
                  From: Charles Carroll <911@...>
                  To: SQLQueriesNoCode@yahoogroups.com
                  Sent: Monday, November 5, 2007 4:28:26 AM
                  Subject: Re: [SQLQueriesNoCode] Default values in a T-SQL proc ??

                  http://blogs. aspfriends. com/afblogs/ blogs/insightdb/ archive/2006/ 08/04/26. aspx
                  has exactly what you want.

                  To quote from Doug Nelson's blog entry:
                  Here is a very simple trick to allow your stored procedures to have
                  optional parameters. By assigning default values to the parameters of
                  the procedure you can get a lot of mileage out of a single query.
                  This is particularly useful for searching based on various criteria.

                  For example

                  create procedure [dbo].[sp_search_ books]
                  @ISBN varchar(15) = null,
                  @AuthorLastName varchar(50) = '',
                  @Publisher varchar(50) = ''
                  @Title varchar(50) = ''
                  as

                  select
                  ISBN,
                  Author_FirstName,
                  Auther_LastName,
                  Title,
                  Publisher
                  from
                  BookTable
                  where
                  ISBN = IsNull(@ISBN, ISBN),
                  and Author_FirstName like
                  case @AuthorFirstName when '' then Author_FirstName
                  else @AuthorFirstName +'%' end
                  and Author_LastName like
                  case @AuthorLastName when '' then Author_LastName
                  else @AuthorLastName +'%' end
                  and Publisher like
                  case @Publisher when '' then Publisher
                  else @Publisher + '%' end
                  and Title like
                  case @Title when '' then Title
                  else '%' + @Title + '%' end

                  This single query can search for books by any combination of the
                  parameters. The conditions in the where clause are such that when the
                  default value is used, the condition will always be true. So to search
                  for books by a publisher starting with 'Apr' the call would look like
                  the following:

                  sp_search_books null,'','',' Apr'

                  The conditional logic contains a couple of different ways to do this
                  and they are both equivalent.

                  First, if the default value of a parameter is null then the IsNull
                  construct of the first condition is a simple way to implement this.

                  In the second example, the case statement is used to test for the
                  default value and simply subsitute the field value in place of the
                  parameter value, if the parameter value is something other than the
                  default, then the parameter value is used instead.

                  For the authors name and publisher, the condition is a "starts with"
                  match. For the title the condition is a "contains" match.

                  One more thing to remember, in SQL, a null = null is not a match. So
                  if the data columns are allowed to have null values, we need to use
                  IsNull to supply a dummy value for the match and in that case the
                  syntax for the condition will be as follows:

                  and IsNull(Author_ FirstName, '') like
                  case @AuthorFirstName when '' then IsNull(Author_ FirstName, '')
                  else @AuthorFirstName +'%' end

                  Not everyone has a first name so we allow that field to be null. If
                  the value of the author field is null, if will be convert to an empty
                  string so instead of null = null which does not match, we now have
                  ''='' ( 'empty string' = 'empty string' ) which does match.

                  On Nov 1, 2007 9:40 AM, John Warner <john@jwarner. com> wrote:
                  >
                  >
                  >
                  >
                  >
                  >
                  > I have a stored proc with several parameters. I provide default values for
                  > the params. Example:
                  >
                  > CREATE PROC MyTest (
                  > @p1 varchar(10) = '%',
                  > @p2 varchar(5) = 'value' )
                  > As blah blah
                  > WHERE field LIKE @p1 AND field2 Like @p2
                  >
                  > Exec MyTest 'Value', 'AnotherValue'
                  >
                  > Result set returned all is well.
                  >
                  > Exec MyTest '', 'AnotherValue'
                  >
                  > Error.
                  >
                  > What am I doing wrong? What I mean is I want these two parameters to be
                  > optional and if omitted to use my default values. I don't want to drop
                  > them out of the where clause, I want them to revert to my default. My other
                  > issue is what do I do for a place holder. My real proc that is in question
                  > here has about a dozen parameters, I'd like to supply values at times to
                  > only a few.
                  >
                  > Hope I'm being clear here. Thanks.
                  >
                  > (SQL Server 2K)
                  >
                  > John Warner
                  >
                  >



                  __________________________________________________
                  Do You Yahoo!?
                  Tired of spam? Yahoo! Mail has the best spam protection around
                  http://mail.yahoo.com

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