7 Replies Latest reply: Jan 19, 2012 7:07 AM by Henco van Ee RSS

    Subselect in sql script

    Henco van Ee

      Hi all,

       

      With this problem I need a subselect I think.

      Let me give you an illustration of the problem: imagine we store our products in a storehouse. Product A120 is stored in storehouse 10, as you can see below. I would like to get the number 10 also in the row of AD121, because it has the jointproduct: AD120. Does anyone know how to perform this?

       

      The problem is:

       

      PRODUCTID
      JOINTPRODUCT
      STOREHOUSEID
      AD120-10
      AD121AD120-

       

      My SQL looks like:

       

      SELECT

           product.productid

           product.jointproduct

           storehouse.storehouseid

      FROM

           product,

           storehouse

      WHERE

           product.id = storehouse.productid;

       

      As I said, I think a subselect can solve the problem but I don't know how to perform that.

      I hope someone can help me out.

       

      Kind regards,

      Henco

        • Subselect in sql script
          Krunoslav Pap

          Hi Henco,

          it seems that table can be reorganized in QlikView  by using hierarchies (please see QV Reference Manual, page 489; NodeId=PRODUCTID, PARENTID=JOINTPRODUCT),  in order to get described result.


          regards,

          Kruno

            • Subselect in sql script
              Henco van Ee

              Hi Krunoslav,

               

              Thanks for your reaction. I have read the Reference Manual, but can't translate it to my specific issue.

              Don't you think it can be solved with a subselect / subquery?

               

              Kind regards,

              Henco

                • Re: Subselect in sql script
                  nstefaniuk

                  Hi. You have modified the query no ? This query can't retrieve the results you show us, because of a inner join between product and storehouse and AD121 have no product.id. Here is the SQL that can retrieve the results:

                   

                  WITH PRODUCT AS
                  (SELECT 'AD120' "PRODUCTID",
                  NULL "JOINTPRODUCT",
                  1 "ID" FROM DUAL
                  UNION
                  SELECT 'AD121' "PRODUCTID",
                  'AD120' "JOINTPRODUCT",
                  NULL "ID" FROM DUAL),
                  STOREHOUSE AS
                  (SELECT 1 "PRODUCTID",
                  10 "STOREHOUSEID" FROM DUAL)
                  -- Start of query
                  SELECT
                       PRODUCT.PRODUCTID,
                       PRODUCT.JOINTPRODUCT,
                       STOREHOUSE.STOREHOUSEID
                  FROM
                       PRODUCT,
                       STOREHOUSE
                  WHERE
                       PRODUCT.ID = STOREHOUSE.PRODUCTID(+);
                  

                   

                  And here is the query that solves your issue (query starts at "Start of query", the WITH clauses are here to give me virtual tables).

                  What the query does? It pre-calculate a virtual table product by seeking the product.id from parent if there is no product.id in the row. So for AD121, as there is no product.id, the sub-query will seek the product.id of AD121's parent : AD120.

                   

                  WITH PRODUCT AS
                  (SELECT 'AD120' "PRODUCTID",
                  NULL "JOINTPRODUCT",
                  1 "ID" FROM DUAL
                  UNION
                  SELECT 'AD121' "PRODUCTID",
                  'AD120' "JOINTPRODUCT",
                  NULL "ID" FROM DUAL),
                  STOREHOUSE AS
                  (SELECT 1 "PRODUCTID",
                  10 "STOREHOUSEID" FROM DUAL)
                  -- Start of query
                  SELECT
                       "PRODUCT_UNIFIED".PRODUCTID,
                       "PRODUCT_UNIFIED".JOINTPRODUCT,
                       STOREHOUSE.STOREHOUSEID
                  FROM
                       (Select
                          "PRODUCT".PRODUCTID "PRODUCTID",
                          "PRODUCT".JOINTPRODUCT "JOINTPRODUCT",
                          nvl("PRODUCT".ID, "PARENT_PRODUCT".ID) "ID"
                        from
                          PRODUCT "PRODUCT"
                          left join PRODUCT "PARENT_PRODUCT" on ("PARENT_PRODUCT".PRODUCTID = "PRODUCT".JOINTPRODUCT)
                       ) "PRODUCT_UNIFIED",
                       STOREHOUSE
                  WHERE
                       "PRODUCT_UNIFIED".ID = STOREHOUSE.PRODUCTID(+);
                  
                  
                  • Re: Subselect in sql script
                    Krunoslav Pap

                    Hi,

                    here is example how it can be done with hierarchy (just copy code below and reload script):

                     

                    TABLE:

                    LOAD * INLINE [

                        PRODUCTID, JOINTPRODUCT, STOREHOUSEID

                        AD120, , 10

                        AD121, AD120

                    ];

                     

                    TABLE1:

                    Hierarchy (PRODUCTID, JOINTPRODUCT, STOREHOUSEID) LOAD Distinct PRODUCTID, JOINTPRODUCT, STOREHOUSEID

                    Resident  TABLE;

                     

                    drop Field STOREHOUSEID, STOREHOUSEID2;

                    RENAME Field STOREHOUSEID1 to STOREHOUSEID;

                    DROP Table TABLE;

                    • Subselect in sql script
                      Henco van Ee

                      Hi everyone,

                       

                      Thanks a lot for your help, I'm impressed

                      Tonight or tomorrow I have time to find out which solution works for me. I will definately let you know what my findings are, and of course check your answers as helpful / correct.

                       

                      Thanks again.


                      Kind regards,

                      Henco

                  • Re: Subselect in sql script
                    qliksus

                    Assuming  you are writing this query in the edit script  you can try like this

                     

                    FinalTable1 :

                    select

                    productid,
                    jointproduct,
                    storehouseid

                    from product,
                    storehouse
                    where product.id = storehouse.productid;

                     

                    join

                     

                     

                    select

                     

                    productid as jointproduct,
                    storehouseid as storehouseidfornull

                    from product,
                    storehouse
                    where product.id = storehouse.productid;

                     

                    FinalTable:

                    load

                    productid,
                    jointproduct,
                    if( isnull(storehouseid) ,storehouseidfornull ,storehouseid) as storehouseid

                    load

                    productid,
                    jointproduct,
                    storehouseid,

                    storehouseidfornull

                     

                     

                    resident FinalTable1 ;

                     

                    drop table FinalTable1;

                    • Re: Subselect in sql script
                      flipside

                      Hi,

                       

                      As ever with Qlikview, there's always a number of ways to do things, here's another ...

                       

                      SourceData:
                      LOAD * INLINE [
                      PRODUCTID, JOINTPRODUCT, STOREHOUSEID
                      AD120,,10
                      AD121,AD120,];


                      Products:
                      LOAD
                      PRODUCTID,
                      if(LEN(LTRIM(STOREHOUSEID))=0,Lookup('STOREHOUSEID','PRODUCTID',JOINTPRODUCT,'SourceData'),STOREHOUSEID) as STOREHOUSEID
                      resident SourceData;

                      drop table SourceData;

                       

                      flipside