Need to query max time and date in QV doc to load Append from ODBC

[#1]

To a helping hand,

We want to reduce our current document load times by appending the new data. On a QV table we have the date and time but do not know how to retrieve the maximum value in the load script.

If we were using sql we'd like to implement something like:

select max(date) from myQV_Table into var_max_date;

select max(time) from myQV_table into var_max_time where my_date = var_max_date;

concatenate load to current_QV_doc;

select col1, col2, col3 from myOracle_table where my_date >= var:max_date and my_time > var_max_time;

.... and yes, we're new... any guide, insight or reference will be greatly appreciated.

Regards,

Julio

[Anonymous]

There is function ReloadTime(), it keeps the timestamp of the application reload.
Or, you can create your own variable in the script:
LET MaxTime=now();

[#3]

Michael,

Thanks for your response. What we're really trying to do is to load a "time transaction table". The table is very large so we need an incremental load approach.

When we reload the document we would like to load all transacions since the last load. By obtaining the max date and max time we could use those values in the oracle-sql query to get the newer transacctions.

On some posts we found a "peek" function... is there something similar to a max... for a loaded QV table?

Again, thanks for all your help

[Anonymous]

Julio,
I think I understand what you need. First time load all data from the transaction table available at the moment, and each next load only add the new data. And, the criteria for the new records is the ones that have "date created" > "last reload time". Is it correct?
If yes, you have to use QVD and use the last reload timestamp as criteria.
It may look more or less like this:

IF isnull(QvdCreateTime('OldData.qvd')) THEN
// First load - no QVD file yet
 Table:
 SQL SELECT
 ...
 FROM OracleTable;
 LET MaxTime=now(); // store data load time
ELSE
// There is QVD - load it first
 Table:
 LOAD
 ...
 FROM OldData.qvd;
// Append new data from database
 CONCATENATE (Table)
 SQL SELECT
 ...
 FROM OracleTable
 WHERE "date created" > '$(MaxTime)'; // syntax maybe a little more complex - see here
 LET MaxTime=now(); // store new data load time
END IF
// Keep this all in new OldData.qvd
STORE Table INTO OldData.qvd (qvd);

[#5]

Michael,

Thank you very much for the help and the details included - it's just what we wer looking for.

WeI've implemented the solution with a small difference since I get the date and time from the actual qvd table after the initial load. However I think we're implementing it pourly (specially on how we retrieve the max_time since they're on different columns)... Perhaps someone can give us some feedback on a better approach. The actual code:

--------------------------------------------------------------------------------------

//After the first load:

ORIG_TRX_TAB:

LOAD * FROM trx_table.qvd (qvd);

MAX_DATE_TAB:

LOAD MAX (TRX_DATE) AS MAX_TRX_DATE

RESIDENT ORIG_TRX_TAB;

LET v_max_trx_date = peek('MAX_TRX_DATE');

MAX_TIME_TAB:

LOAD MAX (TRX_TIME) AS MAX_TRX_TIME WHERE TRX_DATE = $(v_max_trx_date)

RESIDENT ORIG_TRX_TAB;

LET v_max_trx_timee = peek('MAX_TRX_TIME');

--------------------------------------------------------------------------------------------

With the date and time we execute the oracle-sql query and use the concatenate option for the previous transacioins on the QVD file.

Perhaps it can be simplified - I'll be greatful for any guidance.

Regards,

Julio

[Anonymous]

That's inconvenient to have Date and Time in the separate fields. Consider creating single timestamp:

LOAD
...
timestamp(TRX_DATE + TRX_TIME) as TRX_TIMESTAMP
RESIDENT ORIG_TRX_TAB;

Or, better do it on SQL SELECT level. I'm not sure what's Oracle syntax here, shouldn't be complex anyway.