Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Hi Mark,
just use Peek() function:
Let vLoadFromID = peek('LoadFromID',-1,'Tablename');
- Ralf
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;
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.
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.
Because you cannot access a field value in this way. You need to use a function.
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.
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.