9 Replies Latest reply: Sep 21, 2013 4:44 PM by Ori Tal RSS

    How to use an earlier retrieved column in previous SQL?

      Hi there,

       

      May I know how to use an earlier retrieved column in previous SQL?

       

      E.g.

       

      Load

      vv_c,

      vr_create_dt,

      vr_type_c,

      vr_remark_x

      FROM

      [Data\vessel_remark.csv]

       

      Then in another SQL, I want to use say earlier retrieved vv_c.

       

      select "CREATE_DT", "TYPE_C","REMARK_X"
      from "VESSEL_REMARK"

      where "VESSEL_REMARK"."VV_C" = EARLIER_RETRIEVED_VV_C

       

      How to retrieve the EARLIER_RETRIEVED_VV_C ??

       

      Thanks

       

       

        • Re: How to use an earlier retrieved column in previous SQL?
          Tresesco B

          Store the vaule in a variable like:

           

          Let vRemark=Peek('vv_c');

          And then try using this variable in the SQL where cluase.

            • Re: How to use an earlier retrieved column in previous SQL?


              Do you mean something like this?

               

              Let vRemark = Peek('vv_c');

              select "CREATE_DT", "TYPE_C","REMARK_X"
              from "VESSEL_REMARK"

              where "VESSEL_REMARK"."VV_C" = $(vRemark)

                • Re: How to use an earlier retrieved column in previous SQL?
                  Tresesco B

                  Try putting single quotes around the variable expansion like : "VESSEL_REMARK"."VV_C" = '$(vRemark)';

                   

                  Remeber, here your variable will hold the last value in that field. If you want to take all the values in the field 'vv_c', you have to use EXISTS() function, but that would not work in SQL part.

                    • Re: How to use an earlier retrieved column in previous SQL?

                      Remeber, here your variable will hold the last value in that field.

                       

                      I actually need the whole set of values in field vv_c. That means I want to retrieve data for all those vv_c records, not just the last value. How to achieve in this case?

                        • Re: How to use an earlier retrieved column in previous SQL?
                          Tresesco B


                          In that case you have to load all the data from DB using SQL, then filter in qv using exists() like:

                           

                          Load

                          vv_c,

                          vr_create_dt,

                          vr_type_c,

                          vr_remark_x

                          FROM

                          [Data\vessel_remark.csv]

                           

                          FilteredData:

                          Load * Where Exists(VV_C,vv_c);

                           

                          select "CREATE_DT", "TYPE_C","REMARK_X", "VV_C"
                          from "VESSEL_REMARK";

                           

                            • Re: How to use an earlier retrieved column in previous SQL?

                              Hi tresesco,

                               

                              Base on your suggestion, I tried the following but I hit error. How do I load all the values in say "ABBR_VESSEL_M" for use in the next SQL in this case.

                               

                              Static_Info_1:

                              SQL select "VOYAGE"."ABBR_VESSEL_M", "VOYAGE"."VV_C", "SERVICE_C", "ETB_DT", "ETU_DT", "VOYAGE_IN_N", "VOYAGE_OUT_N", "BERTH_SEQ_N", "BERTH_M", "CNTR_CAPACITY_Q","HATCH_COVER"."TYPE_C" AS "HATCH_COVER","BERTH_ALLOCATION"."TERMINAL_C", "VESSEL"."CLASS_N"

                              from "VOYAGE", "BERTH_ALLOCATION", "BERTH","VESSEL", "HATCH_COVER"

                              where "VOYAGE"."VV_C" = "BERTH_ALLOCATION"."VV_C" and "BERTH_ALLOCATION"."ETB_DT" > trunc(sysdate-4) and "BERTH_ALLOCATION"."ETB_DT" <= trunc(sysdate+5) and

                              "BERTH_ALLOCATION"."TERMINAL_C" = "BERTH"."TERMINAL_C" and "BERTH_ALLOCATION"."SECTION_N" = "BERTH"."SECTION_N" and

                              "BERTH_ALLOCATION"."BERTH_N" = "BERTH"."BERTH_N" and "VOYAGE"."ABBR_VESSEL_M" != 'BANGKOK E' and "VESSEL"."VESSEL_ID_N" = "VOYAGE"."VESSEL_ID_N"

                              and "VOYAGE"."ABBR_VESSEL_M" = "VESSEL"."ABBR_VESSEL_M" and "VESSEL"."CLASS_N" = "HATCH_COVER"."CLASS_N";

                               

                               

                              Load * where exists (CLASS_N, "ABBR_VESSEL_M");

                    • Re: How to use an earlier retrieved column in previous SQL?

                      Hi Steve

                      The suggestions below works good as long as you have only 1 previous value.

                      If you have more you will need a loop, but this is hard work and similar to simple Join.

                       

                      This lead to the question : Is there any reason you don't join both tables?

                        • Re: How to use an earlier retrieved column in previous SQL?

                          Hi Ori,

                           

                          I did use a LEFT JOIN but the performance was horrible.

                           

                          Static_Info_1:

                          SQL select "VOYAGE"."ABBR_VESSEL_M", "VOYAGE"."VV_C", "SERVICE_C", "ETB_DT", "ETU_DT", "VOYAGE_IN_N", "VOYAGE_OUT_N", "BERTH_SEQ_N", "BERTH_M", "CNTR_CAPACITY_Q","HATCH_COVER"."TYPE_C" AS "HATCH_COVER","BERTH_ALLOCATION"."TERMINAL_C", "VESSEL"."CLASS_N"

                          from "VOYAGE", "BERTH_ALLOCATION", "BERTH","VESSEL", "HATCH_COVER"

                          where "VOYAGE"."VV_C" = "BERTH_ALLOCATION"."VV_C" and "BERTH_ALLOCATION"."ETB_DT" > trunc(sysdate-4) and "BERTH_ALLOCATION"."ETB_DT" <= trunc(sysdate+5) and

                          "BERTH_ALLOCATION"."TERMINAL_C" = "BERTH"."TERMINAL_C" and "BERTH_ALLOCATION"."SECTION_N" = "BERTH"."SECTION_N" and

                          "BERTH_ALLOCATION"."BERTH_N" = "BERTH"."BERTH_N" and "VOYAGE"."ABBR_VESSEL_M" != 'BANGKOK E' and "VESSEL"."VESSEL_ID_N" = "VOYAGE"."VESSEL_ID_N"

                          and "VOYAGE"."ABBR_VESSEL_M" = "VESSEL"."ABBR_VESSEL_M" and "VESSEL"."CLASS_N" = "HATCH_COVER"."CLASS_N";

                           

                           

                          Static_Info_2:

                          LEFT JOIN(Static_Info_1)

                          SQL select "STD_BAY_N", "BAY_N", "SIZE_Q", "LCG_Q" from "VESSEL_BAY";

                           

                          The column to join is "CLASS_N"

                           

                          What did I do wrong here?

                           

                          ===========================================================================================

                           

                          I looked up the reference manual of QlikView. It says:

                           

                          select [ all | distinct | distinctrow | top n [ percent ] ] *fieldlist

                          from tablelist

                          [ where criterion ]

                          [ group by fieldlist [ having criterion ] ]

                          [ order by fieldlist [ asc | desc ] ]

                          [ ( inner | left | right | full ) join tablename on fieldref = fieldref

                          ]

                           

                           

                          But when I run this particular SQL, it says command not properly ended.

                           

                          SQL select "VOYAGE"."ABBR_VESSEL_M", "VOYAGE"."VV_C", "SERVICE_C", "ETB_DT", "ETU_DT", "VOYAGE_IN_N", "VOYAGE_OUT_N", "BERTH_SEQ_N", "BERTH_M", "CNTR_CAPACITY_Q","HATCH_COVER"."TYPE_C" AS "HATCH_COVER","BERTH_ALLOCATION"."TERMINAL_C", "VESSEL"."CLASS_N"

                          from "VOYAGE", "BERTH_ALLOCATION", "BERTH","VESSEL", "HATCH_COVER"

                          where "VOYAGE"."VV_C" = "BERTH_ALLOCATION"."VV_C" and "BERTH_ALLOCATION"."ETB_DT" > trunc(sysdate-4) and "BERTH_ALLOCATION"."ETB_DT" <= trunc(sysdate+5) and

                          "BERTH_ALLOCATION"."TERMINAL_C" = "BERTH"."TERMINAL_C" and "BERTH_ALLOCATION"."SECTION_N" = "BERTH"."SECTION_N" and

                          "BERTH_ALLOCATION"."BERTH_N" = "BERTH"."BERTH_N" and "VOYAGE"."ABBR_VESSEL_M" != 'BANGKOK E' and "VESSEL"."VESSEL_ID_N" = "VOYAGE"."VESSEL_ID_N"

                          and "VOYAGE"."ABBR_VESSEL_M" = "VESSEL"."ABBR_VESSEL_M" and "VESSEL"."CLASS_N" = "HATCH_COVER"."CLASS_N" left join "VESSEL_BAY" on "CLASS_N" = "VESSEL"."CLASS_N";

                           

                           

                          Thanks.