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

VB Module in SQL???

Expand Messages
  • jerome3141
    Hi, I m using SQL 2000, and Access 2000. I m more experienced in Access than SQL so I used Access to make some complex queries instead of making procedures
    Message 1 of 7 , Jul 31, 2007
      Hi,

      I'm using SQL 2000, and Access 2000. I'm more experienced in Access
      than SQL so I used Access to make some complex queries instead of
      making procedures directly in SQL. I'm now learning to make the
      procedures in SQL by using the access query as a sample.

      The Access query required a VB module to find a "rate" within another
      access table. The table has 3 columns, "Low", "High", and "Markup".
      If the Rate is between the range specified on the row for Low and
      High, then that Markup was used. Else it would go to the next row and
      compare those vaules, until it was found and the Markup was assigned.

      Can I make a module or something in SQL to do this same function?

      Here's the sample from access:
      Function GetMarkup(Rate As Double) As Double
      Dim rs As DAO.Recordset
      Set rs = CurrentDb.OpenRecordset("commrate")
      While Not rs.EOF
      If Rate >= rs!Low And Rate < rs!High Then
      GetMarkup = rs!Rate
      End If
      rs.MoveNext
      Wend
      End Function

      Any ideas?

      Thanks,
      Jeff
    • John Warner
      Sorry for the delay in responding to your problem, I was in meetings all day yesterday. Hope this doesn t come too late to help you. Take a look at the CASE
      Message 2 of 7 , Aug 2, 2007
        Sorry for the delay in responding to your problem, I was in meetings all
        day yesterday. Hope this doesn't come too late to help you. Take a look at
        the CASE structure in SQL. It provides you with a very powerful IF capacity
        in your queries.

        John Warner




        > -----Original Message-----
        > From: SQLQueriesNoCode@yahoogroups.com
        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of jerome3141
        > Sent: Tuesday, July 31, 2007 6:03 PM
        > To: SQLQueriesNoCode@yahoogroups.com
        > Subject: [SQLQueriesNoCode] VB Module in SQL???
        >
        >
        > Hi,
        >
        > I'm using SQL 2000, and Access 2000. I'm more experienced in Access
        > than SQL so I used Access to make some complex queries instead of
        > making procedures directly in SQL. I'm now learning to make the
        > procedures in SQL by using the access query as a sample.
        >
        > The Access query required a VB module to find a "rate" within another
        > access table. The table has 3 columns, "Low", "High", and "Markup".
        > If the Rate is between the range specified on the row for Low and
        > High, then that Markup was used. Else it would go to the next row and
        > compare those vaules, until it was found and the Markup was assigned.
        >
        > Can I make a module or something in SQL to do this same function?
        >
        > Here's the sample from access:
        > Function GetMarkup(Rate As Double) As Double
        > Dim rs As DAO.Recordset
        > Set rs = CurrentDb.OpenRecordset("commrate")
        > While Not rs.EOF
        > If Rate >= rs!Low And Rate < rs!High Then
        > GetMarkup = rs!Rate
        > End If
        > rs.MoveNext
        > Wend
        > End Function
        >
        > Any ideas?
        >
        > Thanks,
        > Jeff
      • Jerome Paris
        John, An example would be the best thing in the world right now. I searched the web for the CASE that I m looking for and couldn t find anything. My original
        Message 3 of 7 , Aug 2, 2007
          John,

          An example would be the best thing in the world right now. I searched the web for the CASE that I'm looking for and couldn't find anything. My original message below has the details, I just need to know if it will work within SQL directly, and if so, how (by example).
          Thank you,

          Jeff

          John Warner <john@...> wrote:
          Sorry for the delay in responding to your problem, I was in meetings all
          day yesterday. Hope this doesn't come too late to help you. Take a look at
          the CASE structure in SQL. It provides you with a very powerful IF capacity
          in your queries.

          John Warner

          > -----Original Message-----
          > From: SQLQueriesNoCode@yahoogroups.com
          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of jerome3141
          > Sent: Tuesday, July 31, 2007 6:03 PM
          > To: SQLQueriesNoCode@yahoogroups.com
          > Subject: [SQLQueriesNoCode] VB Module in SQL???
          >
          >
          > Hi,
          >
          > I'm using SQL 2000, and Access 2000. I'm more experienced in Access
          > than SQL so I used Access to make some complex queries instead of
          > making procedures directly in SQL. I'm now learning to make the
          > procedures in SQL by using the access query as a sample.
          >
          > The Access query required a VB module to find a "rate" within another
          > access table. The table has 3 columns, "Low", "High", and "Markup".
          > If the Rate is between the range specified on the row for Low and
          > High, then that Markup was used. Else it would go to the next row and
          > compare those vaules, until it was found and the Markup was assigned.
          >
          > Can I make a module or something in SQL to do this same function?
          >
          > Here's the sample from access:
          > Function GetMarkup(Rate As Double) As Double
          > Dim rs As DAO.Recordset
          > Set rs = CurrentDb.OpenRecordset("commrate")
          > While Not rs.EOF
          > If Rate >= rs!Low And Rate < rs!High Then
          > GetMarkup = rs!Rate
          > End If
          > rs.MoveNext
          > Wend
          > End Function
          >
          > Any ideas?
          >
          > Thanks,
          > Jeff






          ---------------------------------
          Be a better Globetrotter. Get better travel answers from someone who knows.
          Yahoo! Answers - Check it out.

          [Non-text portions of this message have been removed]
        • Arnie Rowland
          Seems like a straightforward query. Create a SQL Function. CREATE FUNCTION dbo.GetMarkUp ( @MyLookUpValue double ) RETURNS double AS RETURN ( SELECT Rate
          Message 4 of 7 , Aug 2, 2007
            Seems like a straightforward query. Create a SQL Function.

            CREATE FUNCTION dbo.GetMarkUp
            ( @MyLookUpValue double )
            RETURNS double
            AS
            RETURN
            ( SELECT Rate
            FROM MyMarkUpTable
            WHERE ( @MyLookUpValue >= Low
            AND @MyLookUpValue < High
            )
            )
            GO

            - 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 Jerome Paris
            Sent: Thursday, August 02, 2007 2:51 PM
            To: Arnie
            Subject: RE: [SQLQueriesNoCode] VB Module in SQL???

            John,

            An example would be the best thing in the world right now. I searched the
            web for the CASE that I'm looking for and couldn't find anything. My
            original message below has the details, I just need to know if it will work
            within SQL directly, and if so, how (by example).
            Thank you,

            Jeff

            John Warner <john@...> wrote:
            Sorry for the delay in responding to your problem, I was in
            meetings all
            day yesterday. Hope this doesn't come too late to help you. Take a look at
            the CASE structure in SQL. It provides you with a very powerful IF capacity
            in your queries.

            John Warner

            > -----Original Message-----
            > From: SQLQueriesNoCode@yahoogroups.com
            > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of jerome3141
            > Sent: Tuesday, July 31, 2007 6:03 PM
            > To: SQLQueriesNoCode@yahoogroups.com
            > Subject: [SQLQueriesNoCode] VB Module in SQL???
            >
            >
            > Hi,
            >
            > I'm using SQL 2000, and Access 2000. I'm more experienced in Access
            > than SQL so I used Access to make some complex queries instead of
            > making procedures directly in SQL. I'm now learning to make the
            > procedures in SQL by using the access query as a sample.
            >
            > The Access query required a VB module to find a "rate" within another
            > access table. The table has 3 columns, "Low", "High", and "Markup".
            > If the Rate is between the range specified on the row for Low and
            > High, then that Markup was used. Else it would go to the next row and
            > compare those vaules, until it was found and the Markup was assigned.
            >
            > Can I make a module or something in SQL to do this same function?
            >
            > Here's the sample from access:
            > Function GetMarkup(Rate As Double) As Double
            > Dim rs As DAO.Recordset
            > Set rs = CurrentDb.OpenRecordset("commrate")
            > While Not rs.EOF
            > If Rate >= rs!Low And Rate < rs!High Then
            > GetMarkup = rs!Rate
            > End If
            > rs.MoveNext
            > Wend
            > End Function
            >
            > Any ideas?
            >
            > Thanks,
            > Jeff






            ---------------------------------
            Be a better Globetrotter. Get better travel answers from someone who knows.
            Yahoo! Answers - Check it out.

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




            Yahoo! Groups Links







            Disclaimer - August 2, 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/


            [Non-text portions of this message have been removed]
          • navaneethan karuppusamy
            hope dis vil b useful create proc GetMarkUp(ipRate int) as select rate from commrate where iprate low and iprate
            Message 5 of 7 , Aug 5, 2007
              hope dis vil b useful
              create proc GetMarkUp(ipRate int)
              as
              select rate from commrate where iprate>low and iprate<high

              Jerome Paris <jerome3141@...> wrote: John,

              An example would be the best thing in the world right now. I searched the web for the CASE that I'm looking for and couldn't find anything. My original message below has the details, I just need to know if it will work within SQL directly, and if so, how (by example).
              Thank you,

              Jeff

              John Warner <john@...> wrote:
              Sorry for the delay in responding to your problem, I was in meetings all
              day yesterday. Hope this doesn't come too late to help you. Take a look at
              the CASE structure in SQL. It provides you with a very powerful IF capacity
              in your queries.

              John Warner

              > -----Original Message-----
              > From: SQLQueriesNoCode@yahoogroups.com
              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of jerome3141
              > Sent: Tuesday, July 31, 2007 6:03 PM
              > To: SQLQueriesNoCode@yahoogroups.com
              > Subject: [SQLQueriesNoCode] VB Module in SQL???
              >
              >
              > Hi,
              >
              > I'm using SQL 2000, and Access 2000. I'm more experienced in Access
              > than SQL so I used Access to make some complex queries instead of
              > making procedures directly in SQL. I'm now learning to make the
              > procedures in SQL by using the access query as a sample.
              >
              > The Access query required a VB module to find a "rate" within another
              > access table. The table has 3 columns, "Low", "High", and "Markup".
              > If the Rate is between the range specified on the row for Low and
              > High, then that Markup was used. Else it would go to the next row and
              > compare those vaules, until it was found and the Markup was assigned.
              >
              > Can I make a module or something in SQL to do this same function?
              >
              > Here's the sample from access:
              > Function GetMarkup(Rate As Double) As Double
              > Dim rs As DAO.Recordset
              > Set rs = CurrentDb.OpenRecordset("commrate")
              > While Not rs.EOF
              > If Rate >= rs!Low And Rate < rs!High Then
              > GetMarkup = rs!Rate
              > End If
              > rs.MoveNext
              > Wend
              > End Function
              >
              > Any ideas?
              >
              > Thanks,
              > Jeff

              ---------------------------------
              Be a better Globetrotter. Get better travel answers from someone who knows.
              Yahoo! Answers - Check it out.

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






              ---------------------------------
              Why delete messages? Unlimited storage is just a click away.

              [Non-text portions of this message have been removed]
            • Jerome Paris
              Arnie, So, I m assuming (which I don t want to do) that I ll put this function into the stored procedure itself that is gathering all this data? If so, and if
              Message 6 of 7 , Aug 5, 2007
                Arnie,
                So, I'm assuming (which I don't want to do) that I'll put this function into the stored procedure itself that is gathering all this data? If so, and if not, then how do I use this function in puting the results in the MarkUp field of the table created in the stored procedure?

                Arnie Rowland <arnie@...> wrote:
                Seems like a straightforward query. Create a SQL Function.

                CREATE FUNCTION dbo.GetMarkUp
                ( @MyLookUpValue double )
                RETURNS double
                AS
                RETURN
                ( SELECT Rate
                FROM MyMarkUpTable
                WHERE ( @MyLookUpValue >= Low
                AND @MyLookUpValue < High
                )
                )
                GO

                - 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 Jerome Paris
                Sent: Thursday, August 02, 2007 2:51 PM
                To: Arnie
                Subject: RE: [SQLQueriesNoCode] VB Module in SQL???

                John,

                An example would be the best thing in the world right now. I searched the
                web for the CASE that I'm looking for and couldn't find anything. My
                original message below has the details, I just need to know if it will work
                within SQL directly, and if so, how (by example).
                Thank you,

                Jeff

                John Warner <john@...> wrote:
                Sorry for the delay in responding to your problem, I was in
                meetings all
                day yesterday. Hope this doesn't come too late to help you. Take a look at
                the CASE structure in SQL. It provides you with a very powerful IF capacity
                in your queries.

                John Warner

                > -----Original Message-----
                > From: SQLQueriesNoCode@yahoogroups.com
                > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of jerome3141
                > Sent: Tuesday, July 31, 2007 6:03 PM
                > To: SQLQueriesNoCode@yahoogroups.com
                > Subject: [SQLQueriesNoCode] VB Module in SQL???
                >
                >
                > Hi,
                >
                > I'm using SQL 2000, and Access 2000. I'm more experienced in Access
                > than SQL so I used Access to make some complex queries instead of
                > making procedures directly in SQL. I'm now learning to make the
                > procedures in SQL by using the access query as a sample.
                >
                > The Access query required a VB module to find a "rate" within another
                > access table. The table has 3 columns, "Low", "High", and "Markup".
                > If the Rate is between the range specified on the row for Low and
                > High, then that Markup was used. Else it would go to the next row and
                > compare those vaules, until it was found and the Markup was assigned.
                >
                > Can I make a module or something in SQL to do this same function?
                >
                > Here's the sample from access:
                > Function GetMarkup(Rate As Double) As Double
                > Dim rs As DAO.Recordset
                > Set rs = CurrentDb.OpenRecordset("commrate")
                > While Not rs.EOF
                > If Rate >= rs!Low And Rate < rs!High Then
                > GetMarkup = rs!Rate
                > End If
                > rs.MoveNext
                > Wend
                > End Function
                >
                > Any ideas?
                >
                > Thanks,
                > Jeff

                ---------------------------------
                Be a better Globetrotter. Get better travel answers from someone who knows.
                Yahoo! Answers - Check it out.

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

                Yahoo! Groups Links

                Disclaimer - August 2, 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/

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






                ---------------------------------
                Park yourself in front of a world of choices in alternative vehicles.
                Visit the Yahoo! Auto Green Center.

                [Non-text portions of this message have been removed]
              • John Warner
                Use it like any other function like say LTRIM() John Warner
                Message 7 of 7 , Aug 6, 2007
                  Use it like any other function like say LTRIM()

                  John Warner


                  > -----Original Message-----
                  > From: SQLQueriesNoCode@yahoogroups.com
                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Jerome Paris
                  > Sent: Sunday, August 05, 2007 1:09 PM
                  > To: SQLQueriesNoCode@yahoogroups.com
                  > Subject: RE: [SQLQueriesNoCode] VB Module in SQL???
                  >
                  >
                  > Arnie,
                  > So, I'm assuming (which I don't want to do) that I'll put
                  > this function into the stored procedure itself that is
                  > gathering all this data? If so, and if not, then how do I use
                  > this function in puting the results in the MarkUp field of
                  > the table created in the stored procedure?
                  >
                  > Arnie Rowland <arnie@...> wrote:
                  > Seems like a straightforward query. Create a SQL Function.
                  >
                  > CREATE FUNCTION dbo.GetMarkUp
                  > ( @MyLookUpValue double )
                  > RETURNS double
                  > AS
                  > RETURN
                  > ( SELECT Rate
                  > FROM MyMarkUpTable
                  > WHERE ( @MyLookUpValue >= Low
                  > AND @MyLookUpValue < High
                  > )
                  > )
                  > GO
                  >
                  > - 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 Jerome Paris
                  > Sent: Thursday, August 02, 2007 2:51 PM
                  > To: Arnie
                  > Subject: RE: [SQLQueriesNoCode] VB Module in SQL???
                  >
                  > John,
                  >
                  > An example would be the best thing in the world right now. I
                  > searched the web for the CASE that I'm looking for and
                  > couldn't find anything. My original message below has the
                  > details, I just need to know if it will work within SQL
                  > directly, and if so, how (by example). Thank you,
                  >
                  > Jeff
                  >
                  > John Warner <john@...> wrote:
                  > Sorry for the delay in responding to your problem, I was in
                  > meetings all day yesterday. Hope this doesn't come too late
                  > to help you. Take a look at the CASE structure in SQL. It
                  > provides you with a very powerful IF capacity in your queries.
                  >
                  > John Warner
                  >
                  > > -----Original Message-----
                  > > From: SQLQueriesNoCode@yahoogroups.com
                  > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of jerome3141
                  > > Sent: Tuesday, July 31, 2007 6:03 PM
                  > > To: SQLQueriesNoCode@yahoogroups.com
                  > > Subject: [SQLQueriesNoCode] VB Module in SQL???
                  > >
                  > >
                  > > Hi,
                  > >
                  > > I'm using SQL 2000, and Access 2000. I'm more experienced in Access
                  > > than SQL so I used Access to make some complex queries instead of
                  > > making procedures directly in SQL. I'm now learning to make the
                  > > procedures in SQL by using the access query as a sample.
                  > >
                  > > The Access query required a VB module to find a "rate"
                  > within another
                  > > access table. The table has 3 columns, "Low", "High", and "Markup".
                  > > If the Rate is between the range specified on the row for Low and
                  > > High, then that Markup was used. Else it would go to the
                  > next row and
                  > > compare those vaules, until it was found and the Markup was
                  > assigned.
                  > >
                  > > Can I make a module or something in SQL to do this same function?
                  > >
                  > > Here's the sample from access:
                  > > Function GetMarkup(Rate As Double) As Double
                  > > Dim rs As DAO.Recordset
                  > > Set rs = CurrentDb.OpenRecordset("commrate")
                  > > While Not rs.EOF
                  > > If Rate >= rs!Low And Rate < rs!High Then
                  > > GetMarkup = rs!Rate
                  > > End If
                  > > rs.MoveNext
                  > > Wend
                  > > End Function
                  > >
                  > > Any ideas?
                  > >
                  > > Thanks,
                  > > Jeff
                  >
                  > ---------------------------------
                  > Be a better Globetrotter. Get better travel answers from
                  > someone who knows. Yahoo! Answers - Check it out.
                  >
                  > [Non-text portions of this message have been removed]
                  >
                  > Yahoo! Groups Links
                  >
                  > Disclaimer - August 2, 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/
                Your message has been successfully submitted and would be delivered to recipients shortly.