Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 !
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
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 !