7 Replies Latest reply: Jun 27, 2017 3:31 PM by Eduardo DImperio RSS

    EXIST() SQL with QS

    Eduardo DImperio

      Hi !

       

      Im need some help, i have this qvd with some ID's (ID_MODULE) and i need to get from my BD all ID's that i not have in qvd.

      How can i do that please?

       

       

      MODCONNINFO:

      Load

      ID_MODULE

      from [lib://Dados/MI/RS/CM_MODCONNINFO_DETAIL.qvd](qvd);

       

       

       

      LIB CONNECT TO 'diveo_iris';

       

       

      [CM_MODCONNINFO_DETAIL]:

      LOAD

      MODULE_ID AS ID_MODULE,

      CELLID,

      NUM(NUM#(CELLID,'(HEX)')) AS CELLID_HEX,

      MCC_MNC,

      MID(MCC_MNC,4,2) AS MNC,

      LAC,

      NUM(NUM#(LAC,'(HEX)')) AS LAC_HEX

      WHERE NOT EXISTS(ID_MODULE);

      SQL SELECT

        "MODULE_ID",

          "MCC_MNC",

          LAC,

          CELLID

      FROM CM_MODCONNINFO_DETAIL;

       

       

      LAC_CELLID:

      LOAD

      *

      Resident MODCONNINFO;

       

      Concatenate

       

      LOAD

      *

      Resident CM_MODCONNINFO_DETAIL;

       

      store LAC_CELLID into [lib://Dados/MI/RS/CM_MODCONNINFO_DETAIL.qvd];

        • Re: EXIST() SQL with QS
          Sunny Talwar

          May be this

           

          LAC_CELLID:

          LOAD ID_MODULE

          from [lib://Dados/MI/RS/CM_MODCONNINFO_DETAIL.qvd](qvd);

           

          LIB CONNECT TO 'diveo_iris';

           

          Concatenate (LAC_CELLID)

          LOAD MODULE_ID AS ID_MODULE,

              CELLID,

              NUM(NUM#(CELLID,'(HEX)')) AS CELLID_HEX,

              MCC_MNC,

              MID(MCC_MNC,4,2) AS MNC,

              LAC,

              NUM(NUM#(LAC,'(HEX)')) AS LAC_HEX

          WHERE NOT EXISTS(ID_MODULE, MODULE_ID);

          SQL SELECT

            "MODULE_ID",

              "MCC_MNC",

              LAC,

              CELLID

          FROM CM_MODCONNINFO_DETAIL;


          STORE LAC_CELLID into [lib://Dados/MI/RS/CM_MODCONNINFO_DETAIL.qvd];

            • Re: EXIST() SQL with QS
              Eduardo DImperio

              Hi Sunny, how are you?

               

              I thought to do that, but with this i'll not Select all data from my DB and after that pick only where Not Exists ID_MODULE?

                • Re: EXIST() SQL with QS
                  Sunny Talwar

                  May be this

                   

                  Temp:

                  LOAD CONCAT(DISTINCT Chr(39) & ID_MODULE & Chr(39, ', ') as ID_MODULE_CONCAT

                  from [lib://Dados/MI/RS/CM_MODCONNINFO_DETAIL.qvd](qvd);

                   

                  LET vID_MODULE = Peek('ID_MODULE_CONCAT');

                  DROP Table Temp;

                   

                  LAC_CELLID

                  LOAD *

                  FROM [lib://Dados/MI/RS/CM_MODCONNINFO_DETAIL.qvd](qvd);

                   

                  LIB CONNECT TO 'diveo_iris';

                   

                  Concatenate (LAC_CELLID)

                  LOAD MODULE_ID AS ID_MODULE,

                      CELLID,

                      NUM(NUM#(CELLID,'(HEX)')) AS CELLID_HEX,

                      MCC_MNC,

                      MID(MCC_MNC,4,2) AS MNC,

                      LAC,

                      NUM(NUM#(LAC,'(HEX)')) AS LAC_HEX;

                  SQL SELECT

                    "MODULE_ID",

                      "MCC_MNC",

                      LAC,

                      CELLID

                  FROM CM_MODCONNINFO_DETAIL

                  WHERE MODULE_ID NOT IN ($(vID_MODULE));

                   

                  STORE LAC_CELLID into [lib://Dados/MI/RS/CM_MODCONNINFO_DETAIL.qvd];