update and insert qvd from DB with a string ID field

[luohda]

Hi,

I have an old QVD that i want to update, the DB table has a unique ID field which is a string field, and I want to load rows which ID is bigger than the existing biggest ID.

minimum Script:

temp:
LOAD
    B_RECHNUNG_POSITIONEN.REPO_ID,
    B_RECHNUNG_POSITIONEN.AUFT_ID,
    B_RECHNUNG_POSITIONEN.TEAU_ID,
FROM [B_RECHNUNG_POSITIONEN.QVD] (qvd);

last_AUFT_ID:
load

max(B_RECHNUNG_POSITIONEN.AUFT_ID) as lastAUFT
resident temp;

let last_AUFT_ID = peek('lastAUFT', 0, 'last_AUFT_ID');
drop table temp;

B_RECHNUNG_POSITIONEN:
QUALIFY *;

LOAD 
    REPO_ID,
    AUFT_ID;

SELECT * FROM "Oracle"."B_RECHNUNG_POSITIONEN"
 where TO_NUMBER(NVL("AUFT_ID")) >= $(last_AUFT_ID);

UNQUALIFY *;

below are normal concatinate where not exists (another_id)

the error is always, Field 'REPO_ID' not found, if i shift other fields to the first row in load, the error is 'that field' not found.

until drop table temp, everything was fine, so i think it's that the last_AUFT_ID is a number, but the field not, and they can compare, but even adding to_number doesn't work.

any idea?

[jonathandienst]

Try without the Qualify * - 

B_RECHNUNG_POSIQUALIFY *;
LOAD
   REPO_ID as B_RECHNUNG_POSITIONEN.REPO_ID,
   AUFT_ID as B_RECHNUNG_POSITIONEN.AUFT_ID;

SELECT * FROM "Oracle"."B_RECHNUNG_POSITIONEN"
   where TO_NUMBER(NVL("AUFT_ID")) >= $(last_AUFT_ID);

[luohda]

hi thanks for the reply.
Because after the script that I wrote here, it would concatinate with its QVD self which has a qualified field name, like B_RECHNUNG_POSITIONEN.REPO_ID instead of just REPO_ID.
And it didn't work even without qualify.

[jonathandienst]

There was a cut & paste error in my post. Did you try it like this:

B_RECHNUNG_POSITIONEN;
LOAD
   REPO_ID as B_RECHNUNG_POSITIONEN.REPO_ID,
   AUFT_ID as B_RECHNUNG_POSITIONEN.AUFT_ID;

SELECT * FROM "Oracle"."B_RECHNUNG_POSITIONEN"
  where TO_NUMBER(NVL("AUFT_ID")) >= $(last_AUFT_ID);

Qualify just aliases the field name in the same way as the "as" clauses above, but allows you to control exactly when it happens in the preceding load.

[luohda]

no, the error is still 'REPO_ID' not found, even if i use as B_RECHNUNG_POSITIONEN.REPO_ID.

[luohda]

I have tried to do it with date, just like many examples, but also has the same error regardless of qualify or not. So I guess the error is in some other directions?

temp:
LOAD
    B_RECHNUNG_POSITIONEN.REPO_ID,
    B_RECHNUNG_POSITIONEN.AUFT_ID,
    B_RECHNUNG_POSITIONEN.TEAU_ID,

    B_RECHNUNG_POSITIONEN.DAT_MOD,
FROM [B_RECHNUNG_POSITIONEN.QVD] (qvd);

last_AUFT_ID:
load

max(B_RECHNUNG_POSITIONEN.DAT_MOT) as lastDATE
resident temp;

let last_DAT_MOD = peek('lastDATE', 0, 'last_DAT_MOD');
drop table temp;

B_RECHNUNG_POSITIONEN:
QUALIFY *;

LOAD 
    REPO_ID,
    AUFT_ID,

    DAT_MOD;

SELECT * FROM "Oracle"."B_RECHNUNG_POSITIONEN"
 where  "DAT_MOD">= $(last_DAT_MOD);

UNQUALIFY *;