Professional Web Applications Themes

A question on UDF programming - Microsoft SQL / MS SQL Server

In a user-defined function, I have 31 variables each of which stores a "float" type value. The values stored in all these variables are supposedly random, and have no defining order. say, I have var1 = 2300.0, var2 = 2312.4, var3 = 2312.4, var4 = 2504.2, ..... and so on upto var31 = 2400.0 What I have to accomplish is to get adjacent (or consecutive) equal values, say if we take the above example then I should get a table similar to the following: FROM TO No.of hits Value ----------------------------------------------------------------------- 1 1 1 2300.0 2 3 2 2312.4 ........... you ...

  1. #1

    Default A question on UDF programming

    In a user-defined function, I have 31 variables each of which stores a
    "float" type value.
    The values stored in all these variables are supposedly random, and have no
    defining order.

    say, I have
    var1 = 2300.0,
    var2 = 2312.4,
    var3 = 2312.4,
    var4 = 2504.2,
    .....
    and so on upto
    var31 = 2400.0

    What I have to accomplish is to get adjacent (or consecutive) equal values,
    say if we take the above example then I should get a table similar to the
    following:

    FROM TO No.of hits Value
    -----------------------------------------------------------------------
    1 1 1
    2300.0
    2 3 2
    2312.4
    ...........
    you get the picture......
    Any ideas on how to get this? I am free to use a table variable to store the
    result in the above form. But I require such a result exactly in this form.
    Thanks....


    Vivek Dangwal Guest

  2. #2

    Default Re: A question on UDF programming

    Try this. You can extend it to 31 vars just be adding the extra INSERTs.

    CREATE FUNCTION dbo.VarTable (var1 FLOAT, var2 FLOAT, var3 FLOAT, var4
    FLOAT)
    RETURNS result TABLE (vfrom INTEGER, vto INTEGER, hits INTEGER, value
    FLOAT)
    AS
    BEGIN
    DECLARE t TABLE (varno INTEGER, value FLOAT)

    INSERT INTO t VALUES (1,var1)
    INSERT INTO t VALUES (2,var2)
    INSERT INTO t VALUES (3,var3)
    INSERT INTO t VALUES (4,var4)
    /* ...etc... */

    INSERT INTO result
    SELECT MIN(varno), MAX(varno), COUNT(*) hits, value
    FROM
    (SELECT value, varno,
    (SELECT MIN(varno)
    FROM t AS T2
    WHERE T2.varno > T1.varno AND T1.value <> T2.value) AS vto
    FROM t AS T1) AS T
    GROUP BY value, vto

    RETURN
    END


    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Vivek Dangwal" <vivdangwalhotmail.com> wrote in message
    news:e0I5MJSRDHA.2424tk2msftngp13.phx.gbl...
    > In a user-defined function, I have 31 variables each of which stores a
    > "float" type value.
    > The values stored in all these variables are supposedly random, and have
    no
    > defining order.
    >
    > say, I have
    > var1 = 2300.0,
    > var2 = 2312.4,
    > var3 = 2312.4,
    > var4 = 2504.2,
    > ....
    > and so on upto
    > var31 = 2400.0
    >
    > What I have to accomplish is to get adjacent (or consecutive) equal
    values,
    > say if we take the above example then I should get a table similar to the
    > following:
    >
    > FROM TO No.of hits Value
    > -----------------------------------------------------------------------
    > 1 1 1
    > 2300.0
    > 2 3 2
    > 2312.4
    > ..........
    > you get the picture......
    > Any ideas on how to get this? I am free to use a table variable to store
    the
    > result in the above form. But I require such a result exactly in this
    form.
    > Thanks....
    >
    >

    David Portas Guest

  3. #3

    Default Re: A question on UDF programming

    This code works fine for the purpose. Thanks a lot, I owe you one.... :)
    Regards
    Vivek


    "David Portas" <REMOVE_BEFORE_REPLYING_dportasacm.org> wrote in message
    news:uAoSXdSRDHA.3796tk2msftngp13.phx.gbl...
    > Try this. You can extend it to 31 vars just be adding the extra INSERTs.
    >
    > CREATE FUNCTION dbo.VarTable (var1 FLOAT, var2 FLOAT, var3 FLOAT, var4
    > FLOAT)
    > RETURNS result TABLE (vfrom INTEGER, vto INTEGER, hits INTEGER, value
    > FLOAT)
    > AS
    > BEGIN
    > DECLARE t TABLE (varno INTEGER, value FLOAT)
    >
    > INSERT INTO t VALUES (1,var1)
    > INSERT INTO t VALUES (2,var2)
    > INSERT INTO t VALUES (3,var3)
    > INSERT INTO t VALUES (4,var4)
    > /* ...etc... */
    >
    > INSERT INTO result
    > SELECT MIN(varno), MAX(varno), COUNT(*) hits, value
    > FROM
    > (SELECT value, varno,
    > (SELECT MIN(varno)
    > FROM t AS T2
    > WHERE T2.varno > T1.varno AND T1.value <> T2.value) AS vto
    > FROM t AS T1) AS T
    > GROUP BY value, vto
    >
    > RETURN
    > END
    >
    >
    > --
    > David Portas
    > ------------
    > Please reply only to the newsgroup
    > --
    >
    > "Vivek Dangwal" <vivdangwalhotmail.com> wrote in message
    > news:e0I5MJSRDHA.2424tk2msftngp13.phx.gbl...
    > > In a user-defined function, I have 31 variables each of which stores a
    > > "float" type value.
    > > The values stored in all these variables are supposedly random, and have
    > no
    > > defining order.
    > >
    > > say, I have
    > > var1 = 2300.0,
    > > var2 = 2312.4,
    > > var3 = 2312.4,
    > > var4 = 2504.2,
    > > ....
    > > and so on upto
    > > var31 = 2400.0
    > >
    > > What I have to accomplish is to get adjacent (or consecutive) equal
    > values,
    > > say if we take the above example then I should get a table similar to
    the
    > > following:
    > >
    > > FROM TO No.of hits Value
    > > -----------------------------------------------------------------------
    > > 1 1 1
    > > 2300.0
    > > 2 3 2
    > > 2312.4
    > > ..........
    > > you get the picture......
    > > Any ideas on how to get this? I am free to use a table variable to store
    > the
    > > result in the above form. But I require such a result exactly in this
    > form.
    > > Thanks....
    > >
    > >
    >
    >

    Vivek Dangwal Guest

Similar Threads

  1. OCR Programming
    By jjemoi@gmail.com in forum Adobe Acrobat SDK
    Replies: 0
    Last Post: January 31st, 12:44 AM
  2. Replies: 0
    Last Post: May 5th, 06:41 PM
  3. Programming Question: Open Cursor failed ,why ?
    By Jonathan Leffler in forum Informix
    Replies: 0
    Last Post: August 23rd, 08:31 AM
  4. Shell programming question...urgent help please!
    By Jens.Toerring@physik.fu-berlin.de in forum UNIX Programming
    Replies: 1
    Last Post: August 7th, 11:52 PM
  5. asking help for a peer-to-peer socket programming question
    By Matt Gessner in forum UNIX Programming
    Replies: 1
    Last Post: July 17th, 04:21 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139