6 Replies Latest reply: Sep 10, 2015 12:50 PM by Clever Anjos RSS

    Does Autonumber work with SQL Union Statement?

    Jarrell Dunson

      I'm trying to use the Autonumber() function to remove the Symbol width... for performance tuning.

      However it's not working...

       

      Background, we are adding tables together via an SQL union

      1. The first table uses 'gkey' as primary key (about 2,000,000 rows)

      2  The secondary table uses 'dkey' as 'gkey'  as the primary key [but we add 9 Trillion to the dkey, to avoid any overlapping, duplicate keys:  so 561,930 becomes 9,000,000,561,930...so after the union, all the keys are still distinct, howbeit there is a large gap between keys from different tables]

       

      My question is... can I use autonumber with an SQL Union statement?  Could the SQL union be causing the problem?

       

      Here is my Load statement, and SQL Union statement....

       

      Table1:

      LOAD

        Autonumber(gkey,'gkey') AS [Primary Gkey],

        [other fields],

        ...

      ;

       

      SQL

      Select

        gkey,

        [other fields],

        ...etc.

      UNION

      Select

        dkey as gkey.

        [other fields],

        ...etc.

      ;

       

      Here is my current size (in Rob Wunderlich's Document Analyzer):

      Distinct Values    Symbol Width    ~ "Symbol Bytes"

      2,214,889           8.00                     19,934,001