Vertica for loop to iterate over resultset

Hi jumanji

This seems so trivial to me that I assume I got something wrong.

But, from what you seem to be after,

• you're creating your own hash value when Vertica has a hash function returning int
• you're planning to write a user function to get a running sum.

The example below does all just in SQL - and it's not even Vertica specific
1. check the input values in the input Common Table expression in the WITH clause, including the hash value you're suggesting - of course, I have an ID to sort by later, as tables are never sorted.
2. check with_vertica_hash, where I add a HASH() function on the first two columns of the input in-line table
3. check the SUM(jumanji_hash) function call in the final SELECT, which behaves pretty much like you're expecting your function to behave.

This is ANSI-1999 SQL. Not even Vertica specific. If I'm right, SQL seems to become more and more one of the best kept secrets as to its versatility and support for productiveness ...

Or did I get something wrong?

Happy playing - Marco

    WITH
    input(id,_name,jumanji_hash) AS(
              SELECT 1,'Arthur Dent'      ,1
    UNION ALL SELECT 2,'Ford Prefect'     ,2
    UNION ALL SELECT 3,'Zaphod Beeblebrox',3
    UNION ALL SELECT 4,'Tricia McMillan'  ,4
    )
    ,
    with_vertica_hash AS(
    SELECT
     *
    ,HASH(id,_name) AS vertica_hash
    FROM input
    )
    SELECT
      *
    , SUM(jumanji_hash) OVER(
       ORDER BY id
      ) AS result
    FROM with_vertica_hash;
     id |       _name       | jumanji_hash |    vertica_hash     | result
    ----+-------------------+--------------+---------------------+--------
      1 | Arthur Dent       |            1 |  418582588360786296 |      1
      2 | Ford Prefect      |            2 | 7701546877505754318 |      3
      3 | Zaphod Beeblebrox |            3 | 7687242525272749043 |      6
      4 | Tricia McMillan   |            4 | 8286721946822845776 |     10

Hi Marcothesane,

Thanks Much!. On the similar lines in the actual problem. I have a SUM(jumanji_hash) which takes two arguments. I can't modify this function,so, I need to modify this query such that SUM(jumanji_hash,result) and finally return 10.

The function what I have only works on the two arguments that I pass and doesn't have access to rowset implicitly.

To put it in simpler words. how can we achieve the same output with

int user_sum(jumanji_hash,result) { return jumanji_hash+result;} it doesn't have access to the rows or any other informartion.