Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
I have som problem writing my script that should be used for incremental load of data. As always when using incremental load I just want to load the posts that is new by looking at the field called CREATE_TIMESTAMP.
The problem is that i have to tables that i want to perform incremental load on, lets call it table1 and table2, in an Oracle database. It works fine when i am loading only from table1, only the new posts is loaded in to my qvd-file each time. When this is done i just want to load the data in table2 that is connected to the loaded posts in table1 since the other data already should be loaded. Here's the catch, the CREATE_TIMESTAMP is only in table1 and table2 is associated with table1 with an key (table1.ID = table2.ID2). I ONLY want to load the posts from table2 that is associated via the key on the loaded posts in table1.
Hope you understand my problem.
Thank you.
Hannes
select TABLE_NAME from SYS.ALL_TABLES
WHERE TABLESPACE_NAME=USERS AND OWNER=OWNER_NAME;
Left Join
SELECT A.TABLE_NAME , B.COLUMN_NAME
from all_constraints A ,
all_cons_columns B
WHERE A.CONSTRAINT_TYPE ='P' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
ORDER BY A.TABLE_NAME;
This quarry Return Pk for all table and that pk is used in script.
Hello Hannesse,
I am not so very clear. If u provide me the script I may understand. And then I may provide you the solution.
Thank
Shantanu
shantanu73, heres the script:
/* Setup for incremental load. */
SET vQvdFile='File.qvd';
SET vPKID='ID1';
// Set a variable indicating if the QVD exists or not. -1 is True, 0 is False
LET vQvdExists = if(FileSize('$(vQvdFile)') > 0, -1, 0);
IF $(vQvdExists) THEN // QVD exists, we will do an incremental reload
maxdateTab:
// Get the max date from this QVD for use in incremental SELECT
LOAD max(CREATE_TIMESTAMP) as maxdate
FROM $(vQvdFile) (qvd);
LET vIncrementalExpression = 'WHERE CREATE_TIMESTAMP >= ' & chr(39) & timestamp(peek('maxdate')) & chr(39);
DROP table maxdateTab;
ELSE // QVD does not exist
LET vIncrementalExpression = ''; // No QVD. Force full reload
END IF
ODBC CONNECT TO [database;DBQ=DATABASE ] (XUserId is XXXXX, XPassword is YYYY);
Table1:
SQL SELECT BIRTHDATE,
"FIRST_NAME",
GENDER,
"CREATE_TIMESTAMP",
ID as "ID1", //Primary Key
"LAST_NAME",
"WORK_TITLE",
FROM PROD.TABLE1;
//Heres the problem, i only want to load the posts that is associated with the newly loaded posts from table1
//Maybe i need to store old posts from table2 in another qvd-file as well and concatenate the new posts to that?
Table2:
SQL SELECT "DATE",
"RESULT",
ID2,
ID1 as "Table2_ID" //NOTE THAT ID2 is the field that should be associated with ID1 in table1
FROM PROD.TABLE2;
/*
Update the QVD with changes.
*/
Directory;
IF $(vQvdExists) THEN
// Use CONCATENATE in case we've added any new fields.
CONCATENATE (Table1) LOAD * FROM $(vQvdFile) (qvd)
WHERE NOT exists($(vPKID)) // Load only QVD rows that were not already loaded in the data load.
;
END IF
//Overwrite the QVD with the QV datatable.
STORE Table1 INTO $(vQvdFile);
THANKS!
/Hannes
Hello Hannes,
Sorry of Late Answer,
Check the following Script. If you get some idea.
//Start from Here:
Let ThisExecTime = ReloadTime();
QV_Table:
SQL SELECT PrimaryKey, X, Y FROM DB_TABLE
WHERE ModificationTime >= #$(LastExecTime)#
AND ModificationTime < #$(ThisExecTime)#;
Concatenate LOAD PrimaryKey, X, Y FROM File.QVD
WHERE NOT EXISTS(PrimaryKey);
Inner Join SQL SELECT PrimaryKey FROM DB_TABLE;
If ScriptErrorCount = 0 then
STORE QV_Table INTO File.QVD;
Let LastExecTime = ThisExecTime;
End If
//End from Here:
Regards
Shantanu
Thank you Shantanu, after some modification it works fine!
Aanother problem: I want to count how many times the PrimaryKey occurs in the table. Problem is that one post in table1 can be connected to several posts in table2, but still, i only want to count it once. If I do count(PrimaryKey) it returns to many!
Thank you in advance!
Hannes
Hi Hannes,
Maybe you can do this.
table1:
Load *,
PrimaryKey as pKey
From xxx
Then count the number of pKey in table 1 should give you the correct count.
Regards,
Xue Bin
You Chek this One.
Table_Name:
LOAD *;
SQL SELECT * FROM Table_Name
where TO_CHAR(to_DATE($(vModificationTime),'YYYY/MM/DD HH24:MI:SS'),'YYYYMMDDHH24MISS')>='$(T)';
Concatenate ([Table_Name])
Table_Name:
LOAD * from Table_Name .qvd (qvd)
WHERE NOT Exists ($(Y));
inner join ([Table_Name])
sql select Pk FROM Table_Name;
ENDIF
store Table_Name into Table_Name.qvd ;
//..............................................................................................//
LET T= (DATE(NOW(),'YYYYMMDD') & ((hour(NOW()))*10000) + (MINUTE(NOW())*100) + SECOND(NOW()));
adityatiwari, that looks pretty much the same as my script. It still returns to many of PrimaryKey...
Hannes
select TABLE_NAME from SYS.ALL_TABLES
WHERE TABLESPACE_NAME=USERS AND OWNER=OWNER_NAME;
Left Join
SELECT A.TABLE_NAME , B.COLUMN_NAME
from all_constraints A ,
all_cons_columns B
WHERE A.CONSTRAINT_TYPE ='P' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
ORDER BY A.TABLE_NAME;
This quarry Return Pk for all table and that pk is used in script.
Thank you, I think i solved it!
/Hannes