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

Temporary tables unique rows

gadeyne.bram Mar 17, 2014 12:00 PM

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

shawkins Mar 17, 2014 2:07 PM (in response to gadeyne.bram)

> 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?
Actions
2. Re: Temporary tables unique rows

gadeyne.bram Mar 18, 2014 6:37 AM (in response to shawkins)

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.
Actions
3. Re: Temporary tables unique rows

shawkins Mar 18, 2014 8:18 AM (in response to gadeyne.bram)

Do you mean to do a UNION or a UNION ALL?
1 of 1 people found this helpful
Actions
4. Re: Temporary tables unique rows

gadeyne.bram Mar 27, 2014 6:45 AM (in response to shawkins)

Steven,

You're right! I had to use UNION ALL
Actions

Go to original post