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

Re: [SQLQueriesNoCode] Trying to identify duplicate fields

Expand Messages
  • astrologer
    SELECT Policy_Number, Count(Policy_Number) FROM Table1 GROUP BY Policy_Number HAVING Count(Table1.Policy_Number) 1; Astrologer_
    Message 1 of 12 , Sep 16, 2003
    • 0 Attachment
      SELECT Policy_Number, Count(Policy_Number)
      FROM Table1
      GROUP BY Policy_Number
      HAVING Count(Table1.Policy_Number)>1;

      Astrologer_
      --- guitarguy2 <guitarguy2@...> wrote:
      > Hi everyone. I'm trying to write a sql statement in
      > sql 2000 that
      > will give me all fields that are NOT DISTINCT. In
      > this case, I am
      > trying to get policy numbers. I want all the policy
      > numbers that have
      > more than one record, and none of the policy numbers
      > that have only
      > one record. Any help is appreciated - thanks.
      >
      >
      >


      =====
      ----------Astrologer_ Where do the broken-hearted park their cars?
    • Brett R. Bloom
      http://www.15seconds.com/issue/011009.htm HTH Brett P.s. Thanks Dave! ... From: guitarguy2 [mailto:guitarguy2@yahoo.com] Sent: Thursday, September 11, 2003
      Message 2 of 12 , Sep 16, 2003
      • 0 Attachment
        http://www.15seconds.com/issue/011009.htm

        HTH

        Brett

        P.s. Thanks Dave!

        -----Original Message-----
        From: guitarguy2 [mailto:guitarguy2@...]
        Sent: Thursday, September 11, 2003 2:12 PM
        To: SQLQueriesNoCode@yahoogroups.com
        Subject: [SQLQueriesNoCode] Trying to identify duplicate fields


        Hi everyone. I'm trying to write a sql statement in sql 2000 that
        will give me all fields that are NOT DISTINCT. In this case, I am
        trying to get policy numbers. I want all the policy numbers that have
        more than one record, and none of the policy numbers that have only
        one record. Any help is appreciated - thanks.




        To unsubscribe from this group, send an email to:
        SQLQueriesNoCode-unsubscribe@yahoogroups.com



        Your use of Yahoo! Groups is subject to
        http://docs.yahoo.com/info/terms/
      • Andy.T
        In Nomad (the old programming language I used on the mainframe) it would be easy to do but as I am no SQL wizard I can not be sure how easy it is in SQL
        Message 3 of 12 , Sep 17, 2003
        • 0 Attachment
          In Nomad (the old programming language I used on the mainframe) it would be easy to do but as I am no SQL wizard I can not be sure how easy it is in SQL.
           
          Anyway, here is the method I would use to do it in Nomad:
           
          • Create a temporary table with all policy numbers and a count of records
          • Find all the records in this temporary table where the count of records is not 1
           
          Hope that helps, my SQL skills are still being developed so I would like to see your eventual solution.
           
          Andy
           
          -------Original Message-------
           
          Date: Wednesday, September 17, 2003 02:00:53
          Subject: [SQLQueriesNoCode] Trying to identify duplicate fields
           
          Hi everyone. I'm trying to write a sql statement in sql 2000 that
          will give me all fields that are NOT DISTINCT. In this case, I am
          trying to get policy numbers. I want all the policy numbers that have
          more than one record, and none of the policy numbers that have only
          one record. Any help is appreciated - thanks.

           
          ____________________________________________________
            IncrediMail - Email has finally evolved - Click Here
        • Peter Brenchley
          Hi All, I m trying to execute one sproc from another together but keep getting snagged with syntax errors (msg170) and can t figure out where I m going wrong,
          Message 4 of 12 , Oct 4, 2003
          • 0 Attachment
            Hi All,
            I'm trying to execute one sproc from another together but keep getting
            snagged with syntax errors (msg170) and can't figure out where I'm going
            wrong, here they are by themselves:

            1.
            CREATE PROCEDURE dbo.getModuleTitle

            @ModuleID int

            AS

            SELECT dbo.Modules.ModuleTitle
            FROM dbo.Modules
            WHERE dbo.Modules.ModuleID = @ModuleID

            GO

            2.
            CREATE PROCEDURE dbo.UpdateMyText

            @ModuleID int,
            @ModuleTitle nvarchar


            AS

            UPDATE HtmlText
            SET myTitle = @ModuleTitle
            WHERE ModuleID = @ModuleID

            GO


            pretty straightforward there, now what I want is something to combine the 2
            like this so I only have to pass the ModuleID (I'll get the title from the
            sproc getModuleTitle then)

            create proc myUpdate

            @ModuleID int


            exec getModuleTitle @ModuleID-- to return the ModuleTitle then use that in
            the update statement

            UPDATE HtmlText
            SET myTitle = @ModuleTitle
            WHERE ModuleID = @ModuleID

            GO

            Can anybody shed some light on how this should look?
            I think I need some OUTPUT somewhere but not sure where or how?

            TIA

            Peter
          • Dennis West
            Why not use a sql/function to access another sproc? Create User Defined Functions in SQL Server 2000.
            Message 5 of 12 , Oct 5, 2003
            • 0 Attachment
              Why not use a sql/function to access another sproc?
              Create User Defined Functions in SQL Server 2000.
              http://www.flws.com.au/showusyourcode/codeLib/code/SQLFunctions.asp?catI
              D=3

              -----Original Message-----
              From: Peter Brenchley [mailto:list@...]
              Sent: Saturday, October 04, 2003 2:52 AM
              To: SQLQueriesNoCode@yahoogroups.com
              Subject: [SQLQueriesNoCode] include sproc in another sproc

              Hi All,
              I'm trying to execute one sproc from another together but keep getting
              snagged with syntax errors (msg170) and can't figure out where I'm going
              wrong, here they are by themselves:

              1.
              CREATE PROCEDURE dbo.getModuleTitle

              @ModuleID int

              AS

              SELECT dbo.Modules.ModuleTitle
              FROM dbo.Modules
              WHERE dbo.Modules.ModuleID = @ModuleID

              GO

              2.
              CREATE PROCEDURE dbo.UpdateMyText

              @ModuleID int,
              @ModuleTitle nvarchar


              AS

              UPDATE HtmlText
              SET myTitle = @ModuleTitle
              WHERE ModuleID = @ModuleID

              GO


              pretty straightforward there, now what I want is something to combine
              the 2
              like this so I only have to pass the ModuleID (I'll get the title from
              the
              sproc getModuleTitle then)

              create proc myUpdate

              @ModuleID int


              exec getModuleTitle @ModuleID-- to return the ModuleTitle then use that
              in
              the update statement

              UPDATE HtmlText
              SET myTitle = @ModuleTitle
              WHERE ModuleID = @ModuleID

              GO

              Can anybody shed some light on how this should look?
              I think I need some OUTPUT somewhere but not sure where or how?

              TIA

              Peter










              To unsubscribe from this group, send an email to:
              SQLQueriesNoCode-unsubscribe@yahoogroups.com



              Your use of Yahoo! Groups is subject to
              http://docs.yahoo.com/info/terms/
            • Razvan Socol
              CREATE PROCEDURE dbo.getModuleTitle ( @ModuleID int, @ModuleTitle nvarchar(50) OUTPUT ) AS SELECT @ModuleTitle = dbo.Modules.ModuleTitle FROM
              Message 6 of 12 , Oct 5, 2003
              • 0 Attachment
                CREATE PROCEDURE dbo.getModuleTitle
                ( @ModuleID int, @ModuleTitle nvarchar(50) OUTPUT )
                AS

                SELECT @ModuleTitle = dbo.Modules.ModuleTitle
                FROM dbo.Modules
                WHERE dbo.Modules.ModuleID = @ModuleID

                GO

                CREATE PROCEDURE dbo.myUpdate ( @ModuleID int )
                DECLARE @ModuleTitle nvarchar(50)
                EXEC getModuleTitle @ModuleID, @ModuleTitle OUTPUT

                UPDATE HtmlText
                SET myTitle = @ModuleTitle
                WHERE ModuleID = @ModuleID

                GO

                But that only works for a single row;
                if you want to be more efficient you should use:

                CREATE PROCEDURE dbo.myUpdate ( @ModuleID int )

                UPDATE HtmlText
                FROM HtmlText INNER JOIN Modules
                ON HtmlText.ModuleID = Modules.ModuleID
                SET myTitle = ModuleTitle
                WHERE ModuleID = @ModuleID

                GO

                And, eventually, omit the where clause to affect all rows in the table.

                Razvan
              • saikiran_online
                Hi Peter, with my limited knowledge, i think you should use a trigger in place of your second sproc, that would update HtmlText. could i help ? Saikiran. ...
                Message 7 of 12 , Oct 6, 2003
                • 0 Attachment
                  Hi Peter,

                  with my limited knowledge, i think you should use a trigger in place
                  of your second sproc, that would update HtmlText. could i help ?

                  Saikiran.


                  --- In SQLQueriesNoCode@yahoogroups.com, "Peter Brenchley"
                  <list@n...> wrote:
                  > Hi All,
                  > I'm trying to execute one sproc from another together but keep
                  getting
                  > snagged with syntax errors (msg170) and can't figure out where I'm
                  going
                  > wrong, here they are by themselves:
                  >
                  > 1.
                  > CREATE PROCEDURE dbo.getModuleTitle
                  >
                  > @ModuleID int
                  >
                  > AS
                  >
                  > SELECT dbo.Modules.ModuleTitle
                  > FROM dbo.Modules
                  > WHERE dbo.Modules.ModuleID = @ModuleID
                  >
                  > GO
                  >
                  > 2.
                  > CREATE PROCEDURE dbo.UpdateMyText
                  >
                  > @ModuleID int,
                  > @ModuleTitle nvarchar
                  >
                  >
                  > AS
                  >
                  > UPDATE HtmlText
                  > SET myTitle = @ModuleTitle
                  > WHERE ModuleID = @ModuleID
                  >
                  > GO
                  >
                  >
                  > pretty straightforward there, now what I want is something to
                  combine the 2
                  > like this so I only have to pass the ModuleID (I'll get the title
                  from the
                  > sproc getModuleTitle then)
                  >
                  > create proc myUpdate
                  >
                  > @ModuleID int
                  >
                  >
                  > exec getModuleTitle @ModuleID-- to return the ModuleTitle then use
                  that in
                  > the update statement
                  >
                  > UPDATE HtmlText
                  > SET myTitle = @ModuleTitle
                  > WHERE ModuleID = @ModuleID
                  >
                  > GO
                  >
                  > Can anybody shed some light on how this should look?
                  > I think I need some OUTPUT somewhere but not sure where or how?
                  >
                  > TIA
                  >
                  > Peter
                • Naveed Rekabsaz
                  ... Hi Peter There is at least 2 way for your problem.in both of them you should write 1 stored procedure not 2. ... light 1:(using subquery) CREATE PROCEDURE
                  Message 8 of 12 , Oct 6, 2003
                  • 0 Attachment
                    --- Peter Brenchley <list@...> wrote:
                    > Hi All,
                    > I'm trying to execute one sproc from another
                    > together but keep getting
                    > snagged with syntax errors (msg170) and can't figure
                    > out where I'm going
                    > wrong, here they are by themselves:
                    >
                    > 1.
                    > CREATE PROCEDURE dbo.getModuleTitle
                    >
                    > @ModuleID int
                    >
                    > AS
                    >
                    > SELECT dbo.Modules.ModuleTitle
                    > FROM dbo.Modules
                    > WHERE dbo.Modules.ModuleID = @ModuleID
                    >
                    > GO
                    >
                    > 2.
                    > CREATE PROCEDURE dbo.UpdateMyText
                    >
                    > @ModuleID int,
                    > @ModuleTitle nvarchar
                    >
                    >
                    > AS
                    >
                    > UPDATE HtmlText
                    > SET myTitle = @ModuleTitle
                    > WHERE ModuleID = @ModuleID
                    >
                    > GO
                    >
                    >
                    > pretty straightforward there, now what I want is
                    > something to combine the 2
                    > like this so I only have to pass the ModuleID (I'll
                    > get the title from the
                    > sproc getModuleTitle then)
                    >
                    > create proc myUpdate
                    >
                    > @ModuleID int
                    >
                    >
                    > exec getModuleTitle @ModuleID-- to return the
                    > ModuleTitle then use that in
                    > the update statement
                    >
                    > UPDATE HtmlText
                    > SET myTitle = @ModuleTitle
                    > WHERE ModuleID = @ModuleID
                    >
                    > GO
                    >
                    > Can anybody shed some light on how this should look?
                    > I think I need some OUTPUT somewhere but not sure
                    > where or how?
                    >
                    > TIA
                    >
                    > Peter
                    >
                    >
                    Hi Peter

                    There is at least 2 way for your problem.in both of
                    them you should write 1 stored procedure not 2.

                    -------------------------
                    light 1:(using subquery)

                    CREATE PROCEDURE dbo.UpdateMyText

                    @ModuleID int,

                    AS

                    UPDATE HtmlText
                    SET myTitle = (SELECT dbo.Modules.ModuleTitle
                    FROM dbo.Modules
                    WHERE dbo.Modules.ModuleID =
                    @ModuleID)

                    WHERE ModuleID = @ModuleID

                    GO
                    ------------------------
                    light 2:(uisng variable)

                    CREATE PROCEDURE dbo.UpdateMyText

                    @ModuleID int,

                    AS

                    Declare @ModuleTitle nvarchar

                    @ModuleTitle = (SELECT dbo.Modules.ModuleTitle FROM
                    dbo.Modules
                    WHERE dbo.Modules.ModuleID =
                    @ModuleID)

                    UPDATE HtmlText
                    SET myTitle = @ModuleTitle
                    WHERE ModuleID = @ModuleID

                    GO
                    --------------------
                    second way is usefull when you want to do more
                    calculation on @ModuleTitle

                    Naveed.

                    __________________________________
                    Do you Yahoo!?
                    The New Yahoo! Shopping - with improved product search
                    http://shopping.yahoo.com
                  • Peter Brenchley
                    Thanks Razvan, good tip on affecting all rows too... and Saikiran thanks for your suggestion I actually got it to work with some help like this: ALTER PROC
                    Message 9 of 12 , Oct 13, 2003
                    • 0 Attachment
                      Thanks Razvan,

                      good tip on affecting all rows too...

                      and Saikiran

                      thanks for your suggestion

                      I actually got it to work with some help like this:

                      ALTER PROC myUpdate
                      @ModuleID int
                      AS
                      SET NOCOUNT ON
                      UPDATE t
                      SET myTitle = m.ModuleTitle
                      FROM dbo.HtmlText t JOIN dbo.Modules m ON t.ModuleID = m.ModuleID

                      cheers

                      Peter



                      -----Original Message-----
                      From: saikiran_online [mailto:saikiran_online@...]
                      Sent: 06 October 2003 14:04
                      To: SQLQueriesNoCode@yahoogroups.com
                      Subject: [SQLQueriesNoCode] Re: include sproc in another sproc


                      Hi Peter,

                      with my limited knowledge, i think you should use a trigger in place
                      of your second sproc, that would update HtmlText. could i help ?

                      Saikiran.


                      --- In SQLQueriesNoCode@yahoogroups.com, "Peter Brenchley"
                      <list@n...> wrote:
                      > Hi All,
                      > I'm trying to execute one sproc from another together but keep
                      getting
                      > snagged with syntax errors (msg170) and can't figure out where I'm
                      going
                      > wrong, here they are by themselves:
                      >
                      > 1.
                      > CREATE PROCEDURE dbo.getModuleTitle
                      >
                      > @ModuleID int
                      >
                      > AS
                      >
                      > SELECT dbo.Modules.ModuleTitle
                      > FROM dbo.Modules
                      > WHERE dbo.Modules.ModuleID = @ModuleID
                      >
                      > GO
                      >
                      > 2.
                      > CREATE PROCEDURE dbo.UpdateMyText
                      >
                      > @ModuleID int,
                      > @ModuleTitle nvarchar
                      >
                      >
                      > AS
                      >
                      > UPDATE HtmlText
                      > SET myTitle = @ModuleTitle
                      > WHERE ModuleID = @ModuleID
                      >
                      > GO
                      >
                      >
                      > pretty straightforward there, now what I want is something to
                      combine the 2
                      > like this so I only have to pass the ModuleID (I'll get the title
                      from the
                      > sproc getModuleTitle then)
                      >
                      > create proc myUpdate
                      >
                      > @ModuleID int
                      >
                      >
                      > exec getModuleTitle @ModuleID-- to return the ModuleTitle then use
                      that in
                      > the update statement
                      >
                      > UPDATE HtmlText
                      > SET myTitle = @ModuleTitle
                      > WHERE ModuleID = @ModuleID
                      >
                      > GO
                      >
                      > Can anybody shed some light on how this should look?
                      > I think I need some OUTPUT somewhere but not sure where or how?
                      >
                      > TIA
                      >
                      > Peter




                      To unsubscribe from this group, send an email to:
                      SQLQueriesNoCode-unsubscribe@yahoogroups.com



                      Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
                    Your message has been successfully submitted and would be delivered to recipients shortly.