12 Replies Latest reply: Apr 17, 2009 10:56 PM by Jose Adriano Baltieri RSS

    Add a field to a QVD file

    Jose Adriano Baltieri

      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

       

       

       

       

       

        • Add a field to a QVD file
          Gordon Savage

          I am not quite sure I understand your problem.

          When you say that that the databases are dumped onto QVD do you mean you have a QVW that does a 'LOAD' and then 'STORE' ? If so why not create the new field in that. Otherwise, what is the mechanism that dumps the data into QVDs - why not create the new field through the SQL statement or whatever?

          Regards,

          Gordon

            • Add a field to a QVD file
              Jose Adriano Baltieri

              That's it : I have a empty QVW (no interface) that reads a CSV file , generated from the mainframe, and stores it into a QVD.

              As a matter of fact it works like this : a Cobol program, running on a Unisys mainframe, extracts table data and generates a CSV. It also generates automatically the script that will transform this into a QVD. When both are ready, it triggers QV.EXE outside of it (outside of the mainframe) to run upon them and generate the QVD file.

              We did this all. It's working pretty nice.

              So, when we build our final applications, we only read QVDs. Way faster. And it's very organized/automated.

              Get a sample of a script like this from here, if you will : http://www.des.online.unimep.br/au/pub/FCMBI.QVS

              So, what's the problem ? The problem is that many, many times a QVD has been used amongst several applications (QVWs). And, on all of them we're repeating a complex formula to calculate derivated fields.

              So, I wanna generate this QVD with this calculated field already. I could do that on the mainframe. That is, I could do a pseudo-field only to carry on that new value, calculated. But I'd be wasting mainframe time to do that. I only would do this (and I already do it in fact) if the derivation formula can only be evaluated ON THE MAINFRAME.

              On other situations, I can use low cost CPUs to calculate the fields.

              After all, I want simply do something like this :

              Load all of the fields from a QVD, add some more fields to it (columns) and then, store it again.

              Easy ?

               

               

               

               

               

               

                • Add a field to a QVD file
                  Johan Hessler

                  Csv:

                  LOAD

                  A,

                  B,

                  C,

                  D

                  from csv.qvd;

                   

                  join (CSV) LOAD

                  A,

                  A*B as E

                  resident CSV;

                   

                    • Add a field to a QVD file
                      Jose Adriano Baltieri

                      Sounds great !

                      I did it like this :

                       

                      JOIN (CSV) LOAD

                      *

                      A*B AS E

                      RESIDENT CSV;

                       

                      Very nice.

                      I should understand better the JOIN statement. It looks very flexible.

                      Thanks !

                       

                       

                      • Add a field to a QVD file
                        Rob Wunderlich

                        If you use the JOIN method, make sure to use the DISTINCT keyword and load enough key fields to create uniqueness -- otherwise you may generate additional rows. For a calculated field, it should be enough to list only the fields used in the calc.

                        join (Csv) load DISTINCT A, B, A * B as E resident Csv

                        -Rob

                          • Add a field to a QVD file
                            Jose Adriano Baltieri

                            Well, every table of mine has a field called RECNO, which is the result of the RECNO function. This is unique by nature.

                            Can't I use simply the "*" (star) syntax ?

                            Like this :

                            join (Csv) load DISTINCT * , A * B as E resident Csv

                            IOW:

                            load *anything* and add a new field E with the value A*B.

                             

                            I just want to create a new column. CAN NOT, CREATE NEW ROWS. NEVER. NOT AT ALL.

                            Thanks !

                             

                              • Add a field to a QVD file
                                Rob Wunderlich

                                Yes, you can use the "*" which is the way I always do it. Should have typed my original answer that way.

                                -Rob

                                  • Add a field to a QVD file
                                    Jose Adriano Baltieri

                                    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 !

                                     

                                      • Add a field to a QVD file
                                        Rob Wunderlich

                                        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

                                         

                                          • Add a field to a QVD file
                                            Jose Adriano Baltieri

                                            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 !

                                             

                                             

                                             

                                • Add a field to a QVD file
                                  Gordon Savage

                                  If you dont want to change the existing process, you can load a QVD with derived fields and then store it back to the original name:

                                  [in]:

                                  LOAD A,

                                  B,

                                  C,

                                  D,

                                  B * C as E

                                  FROM test.qvd (qvd);

                                  store [in] into test.qvd;

                                   

                                  If you want to automate this perhaps you could initiate a batch file containing 2 calls to qv.exe (using /r) to create the 'original' QVD then another to recreate it in the 'extended' format.

                                  Regards,

                                  Gordon