Add a field to a QVD file

[#1]

Hello !

We have a infra-structure here where our Unisys mainframe databases are dumped onto QVD files. So, we have a kind of a QVD database.

I want to pick some of this QVD files and add a column to it , keeping all of the other columns intact.

So say that the QVD has fields A,B and C. I want to add a D field , which is say a formula of A + B.

What would be the best way to do it ?

Thanks !

PS : have not found how to upload a file here but, if you want a kit to start rapidly your test, get one from here :

http://www.des.online.unimep.br/au/pub/kit116.zip

[#2]

OK !

I've got very concerned thought, when you said that records might not be kept. That is, I could have more (or less) records than before. That would be a disaster, of course !

What are the situations when that happen ? How can I detect and/or prevent them ?

Cause I plan to use very much this technique.

Here's another situation when I'd like to use it :

STEP_1:LOAD A ,B ,C ,A_VERY_COMPLEX_FORMULA_BUILT_FROM(SQUARE_ROOT(A) * B * SOMETHING_ELSE_VERY_COMPLEX(UPON(C)) AS DFROM SOMEWHERE;// NOW THEN THAT I ALREADY HAVE 'D' OK...LOAD * ,IF(D = 0,'ZEROED D #$*&%@','D IS OK !!!!!!') AS STATUS_DRESIDENT STEP_1;
On the traditional model, I would have to repeat that complex formula inside the 'IF'. It would be very cumbersome.

Another thing that would be nice is to be able to use , inside the LOAD statement, a previously created column. That is, on the example above, a way to use 'D' which has just been created. Like this :

STEP_1:LOAD A ,B ,C ,A_VERY_COMPLEX_FORMULA_BUILT_FROM(SQUARE_ROOT(A) * B * SOMETHING_ELSE_VERY_COMPLEX(UPON(C)) AS D,IF(D = 0,'ZEROED D #$*&%@','D IS OK !!!!!!') AS STATUS_DFROM SOMEWHERE;

Well, that's it. I hope you have understood why I'm asking to create a new column. ....

Thanks for your support !

[rwunderlich]

Re the JOIN issue. If you don't use the DISTINCT keyword, you can generate MORE rows if you have duplicate rows. I always use DISTINCT on a JOIN even if I think I don't have any duplicates. You don't always notice the extra records.

The typical way to avoid repeating calculated field formulas is with a preceeding load. For example:

LOAD *,
IF(D = 0, 'bad D', 'good D') as D_STATUS;

LOAD A, B, C,
(some formula) AS D
FROM SOMEWHERE;

Another trick is to define complex formulas in variables and then reference the formula as $(calculatePrice).

-Rob

[#4]

I like pretty much of this alternative here :

LOAD *,
IF(D = 0, 'bad D', 'good D') as D_STATUS;

LOAD A, B, C,
(some formula) AS D
FROM SOMEWHERE;

The trick about defining formulas as $(formula) I already knew but I dislike it. Think it very cumbersome.

The syntax above is very nice. However I dont find it on the docs...

But I believe this is the best solution for my problem !

Thank you !