0

I have a query that generally goes

SELECT * 
FROM BILL_HISTORY a

JOIN 
(
Sel *
from SERVICE_INFO

Qualify Row_Number() OVER (PARTITION BY Service, Product_Code1, 
Product_Code2 ORDER BY Effective_Date1 DESC, Effective_Date2 DESC) = 1
) b
ON a.SERVICE = b.SERVICE

The expected result set is 6 months of past invoices with additional columns from SERVICE_INFO. If I run this query manually with a WHERE clause on a specific service ID, I receive the expected result. However, if I run the same query wrapped in a CREATE VOLATILE TABLE ... AS (SELECT * ... ) then when I query the same service ID, I only receive the most recent invoice ID for that service.

Any reasons why this may occur? Same result if I create the table as perm or volatile. Real head-scratcher...

EDIT: show the general sub-query that was made on the JOIN as this seemed to be related to the cause of the problem.

user3867061
  • 43
  • 2
  • 16
  • `SERVICE = SERVICE` is not a real `JOIN` condition. Your query doesn't make sense. – Gordon Linoff Oct 29 '18 at 03:00
  • Try adding `ON COMMIT PRESERVE ROWS` per this [SO answer](https://stackoverflow.com/questions/17112104/teradata-volatile-table-statement-is-not-creating-any-rows). – Parfait Oct 29 '18 at 03:07
  • Its a highly generalised query but of course it makes sense. I could have said SERVICE_ID = SERVICE_ID instead as the identifier exists in both tables. – user3867061 Oct 29 '18 at 04:03
  • Yes ON COMMIT PRESERVE ROWS is used. I am receiving results just inconsistent results running the exact same query. If ON COMMIT PRESERVE ROWS was not there the table would be empty. – user3867061 Oct 29 '18 at 04:04
  • The default for new table is probably SET (based on your session, see SHOW TABLE) which removes duplicate rows. Try `create multiset volatile table...` – dnoeth Oct 29 '18 at 10:09
  • Thanks dieter, I have tried both SET and MULTISET with the same results. The duplicate rows would only exist on the columns returned from the second table while the first table has unique rows based on the Invoice_Date key on the first table – user3867061 Oct 29 '18 at 11:12
  • Can you compare Explains for Select and Create Table As? If they're different it migh be a optimizer bug. As a workaround create the table WITH NO DATA and then Insert/Select – dnoeth Oct 29 '18 at 12:09
  • Thanks Dieter, that was my next step. I was able to resolve the issue by placing the results of the join table into its own Volatile Table (I had not shown there was a sub-query against the join to qualify results). Once I made the JOIN against the Volatile Table rather than a sub-query, it returned the expected results. Really unusual as I have never encountered this type of problem before – user3867061 Oct 29 '18 at 12:25

1 Answers1

0

Although I am not sure the reason why this occured, I was able to resolve the issue by first placing the sub-query against the joined table into its own Volatile Table first then completing the join on the original query.

Instead of 

JOIN 
 (
 Sel *
 from SERVICE_INFO

 Qualify Row_Number() OVER (PARTITION BY Service, Product_Code1, 
 Product_Code2 ORDER BY Effective_Date1 DESC, Effective_Date2 DESC) = 1
 ) b

First

 Create Multiset Volatile Table SERVICE_INFO_VT AS (
 Sel *
 from SERVICE_INFO

 Qualify Row_Number() OVER (PARTITION BY Service, Product_Code1, 
 Product_Code2 ORDER BY Effective_Date1 DESC, Effective_Date2 DESC) = 1

then complete the original query

 SELECT * 
 FROM BILL_HISTORY a

 JOIN SERVICE_INFO_VT b
 ON a.SERVICE = b.SERVICE

which returned the same results whether being run as a manual query or wrapped around a CREATE TABLE / VOLATILE TABLE statement

dnoeth
  • 54,996
  • 3
  • 29
  • 45
user3867061
  • 43
  • 2
  • 16