4 Replies Latest reply on Mar 27, 2014 6:45 AM by gadeyne.bram

    Temporary tables unique rows

    gadeyne.bram Master

      Hi,

       

      I developed a Virtual procedure today and I noticed that a temp table only held unique rows while the query that fills the temporary table does not return unique rows.

       

      I've created it using this syntax

       

      INSERT INTO #tmp_table

      select cast(valuetime as date) as valuedate, patientid from table x;

       

      The reason that the rows are not unique is that we want to do a count on a group by on the patientid and date part of a timestamp after filling the table.

       

      Is it possible that local temporary tables only keep unique results? Is there a way to make it keep non unique rows? I could generate a unique key with the rank function.

       

      With kind regards

      Bram

        • 1. Re: Temporary tables unique rows
          Steven Hawkins Master

          > I developed a Virtual procedure today and I noticed that a temp table only held unique rows while the query that fills the temporary table does not return unique rows.

           

          That is not expected.  A temporary table without a primary key will internally assign an auto incrementing row identifier in a hidden column.  Can you confirm that the row counts are different from your select and what's inserted into the temp table?

          • 2. Re: Temporary tables unique rows
            gadeyne.bram Master

            Hi Steven,

             

            I think that the temporary table is not causing the issue (although internally it might). The issue seems to occur when I use a union of 2 selects. What I did was insert the result of this UNION into a temporary table and I thought this was the cause of this issue. But now it seems that executing the union itself also gives the same wrong result.

             

            SELECT pv.PatientID, PARSETIMESTAMP(FORMATTIMESTAMP(pv.ValueEnterTime, 'yyyy-MM-dd HH:mm:ss.S'), 'yyyy-MM-dd HH:mm:ss') AS ValueEnterTime,  'sometype' AS Type

            FROM /*+ MAKEDEP */ some_production_table AS pv

            WHERE some conditions...

             

            returns 22995 rows.

             

            SELECT pv.PatientID, PARSETIMESTAMP(FORMATTIMESTAMP(pv.ValueEnterTime, 'yyyy-MM-dd HH:mm:ss.S'), 'yyyy-MM-dd HH:mm:ss') AS ValueEnterTime,  'sometype' AS Type

            FROM /*+ MAKEDEP */ some_warehouse_table AS pv

            WHERE some conditions...

             

            returns 7972 rows

             

            The union of both:

             

            SELECT pv.PatientID, PARSETIMESTAMP(FORMATTIMESTAMP(pv.ValueEnterTime, 'yyyy-MM-dd HH:mm:ss.S'), 'yyyy-MM-dd HH:mm:ss') AS ValueEnterTime,  'sometype' AS Type

            FROM /*+ MAKEDEP */ some_production_table AS pv

            WHERE some conditions...

            UNION

            SELECT pv.PatientID, PARSETIMESTAMP(FORMATTIMESTAMP(pv.ValueEnterTime, 'yyyy-MM-dd HH:mm:ss.S'), 'yyyy-MM-dd HH:mm:ss') AS ValueEnterTime,  'sometype' AS Type

            FROM /*+ MAKEDEP */ some_warehouse_table AS pv

            WHERE some conditions...

             

            returns 10413 rows

             

            These last rows seem to be truncated to unique rows only.

            • 3. Re: Temporary tables unique rows
              Steven Hawkins Master

              Do you mean to do a UNION or a UNION ALL?

              1 of 1 people found this helpful
              • 4. Re: Temporary tables unique rows
                gadeyne.bram Master

                Steven,

                 

                You're right! I had to use UNION ALL