Non-unique fields causing duplicate records when tables joined.

[#1]

I have been given a script with the following:

CallTable:
SQL SELECT
     unique_id,
     start_time,
     region_key,
     result
FROM $(DB_NAME).$(vTableName);
join (CallTable)
Load
start_time,
maketime(hour(start_time),minute(start_time),second(start_time)) as [Call Start Time]
Resident
CallTable;

The resulting CallTable then gets stored in a QVD and later loaded by another script for reporting purposes.

This works fine except in certain cases where, I believe, two records have the same start_time value. When this happens, then we end up with duplicate records in the table/QVD which we can see by creating a list box for unique_id with the frequency option enabled.  How can I modify this script to prevent these duplicate records? Since we're working with a fairly large dataset an efficient solution is  preferable. 

Thanks

Steve

[swuehl · Accepted solution]

Maybe I misunderstood what you are trying to achieve, but why not try a preceding load:

CallTable:
LOAD *,
      maketime(hour(start_time),minute(start_time),second(start_time)) as [Call Start Time];
SQL SELECT      
     unique_id,
     start_time,
     region_key,
     result 
FROM $(DB_NAME).$(vTableName); 

[#3]

Perfect, thanks.