Problem setting variable from QVD file for incremental load

[#1]

I have an odd problem that I don't quite understand. I have been testing incremental loading and I have what I believe all the parts are set up and ready to go but I cannot get the SQL SELECT statement to work correctly. What I have is this

SELECT * FROM TableName WHERE FieldName > '$(vLoadFromID)'

To test this I was manually setting the variable as follows before executing the SELECT statement to load just the new data

vLoadFromID = '2675710';

vLoadFromID = 2675710;

Using either of these statements to set the value for the variable, the SELECT statement correctly loads 1 new record from the database, then I load the previously stored QVD file and then store the new data back in to the QVD file again. I then store the value for the latest ID in another QVD file before exiting the script.

The problem is that when I try and set the variable by loading the value from a QVD file rather than setting it manually, the SELECT loads all the data rather than the just the new data...so I read in the value for the last ID that I stored

LOAD
   LastID AS LoadFromID
  FROM
      LastID.qvd (qvd);   

Then set the variable used in the SELECT statement, using either of these

vLoadFromID = LoadFromID;

vLoadFromID = LastID;

The variable vLoadFromID always seem to end up blank or null and the SELECT statement always loads all the data...rather than zero records in this case as no new data has been added since I stored the last ID.

Any advice appreciated on how to correctly read in and set the variable that is to be used in the SEELCT statement

[rbecher]

Hi Mark,

just use Peek() function:

Let vLoadFromID = peek('LoadFromID',-1,'Tablename');

- Ralf

[wallinpeter · Accepted solution]

1) Get Last ID from QVD

MaxIDTable:

LOAD

     max(ID) as maxID

FROM existing_data.qvd (qvd);

2) Store field value in Variable:

LET vLoadFromID = peek('maxID',0,'MaxIDTable');

DROP TABLE MaxIDTable;

3) Extract records from SQL Table and Concatenate existing QVD.

NewData:

SELECT * FROM TableName WHERE FieldName > '$(vLoadFromID)';

CONCATENATE (NewData)

LOAD

     *

FROM existing_data.qvd (qvd);

4) Store results

STORE NewData INTO Data.qvd;

DROP TABLE NewData;

[#4]

Thanks Ralf and Peter....it works now.

Can you shed any light on why setting the variable using PEEK works and setting it the way I was doing it originally didn't work.

[wallinpeter]

Because a field can potentially have more than one value. What if this load statement returned 100 rows....

LOAD

   LastID AS LoadFromID
  FROM
      LastID.qvd (qvd);

Which of the 100 rows would the variable be equal to?  That is why you wrap in peek function, to guarantee only one value is passed to LET statement.

[rbecher]

Because you cannot access a field value in this way. You need to use a function.

[#7]

Ralf

Thanks, I think this was my problem as I was only creating a table with a single row.

Mark Boomer

SupportLine Manager

Micro Focus

mark.boomer@microfocus.com<mailto:mark.boomer@microfocus.com>

Micro Focus House,

2 East Bridge Street

Belfast BT1 3NK,

Northern Ireland

Direct: +44 (0)2890 260035

Mobile:

Fax:

You'll find answers to more questions as well as discussions, forums and articles at the NEW Borland Community http://community.microfocus.com/borland/

Visit. Engage. Share.

[#8]

Peter

I was using the AUTOGENERATE(1) to create the table with a single row but using PEEK in this way is good advice as then I don’t need to care how many rows are in the table.

Thanks again for the help.

Mark Boomer

SupportLine Manager

Micro Focus

mark.boomer@microfocus.com<mailto:mark.boomer@microfocus.com>

Micro Focus House,

2 East Bridge Street

Belfast BT1 3NK,

Northern Ireland

Direct: +44 (0)2890 260035

Mobile:

Fax:

You'll find answers to more questions as well as discussions, forums and articles at the NEW Borland Community http://community.microfocus.com/borland/

Visit. Engage. Share.