-
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?
-
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.
-
3. Re: Temporary tables unique rows
shawkins Mar 18, 2014 8:18 AM (in response to gadeyne.bram)1 of 1 people found this helpfulDo you mean to do a UNION or a UNION ALL?
-
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