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];

[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];

[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?

[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];

[eduardo_dimperio]

Very smart, but the maximum in a list is 1000 and it exceeds vID_MODULE

[eduardo_dimperio]

Can I do a seach in a qvd using a sql command?

[sunny_talwar]

Not sure what you mean when you say a search in qvd using a sql command? Also, what is the list of 1000? I am not sure I understand that part as well?

[eduardo_dimperio]

Hi Sunny, sory my delayed

1- Not sure what you mean when you say a search in qvd using a sql command?

Get all ID_MODULE from my DB there is not in qvd.

Example:

LOAD

ID_MODULE

FROM

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

Load

*

;

SQL SELECT

ID_MODULE

WHERE NOT EXIST(ID_MODULE) //THIS ID_MODULE IN RED CAME FROM MY QVD

from MyDB

2- what is the list of 1000?

When i use this code

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

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

The field ID_MODULE_CONCAT cannot concatenate more that 1000 values, so doesn't work for me.