How to exclude data using excel file

[L_Hop]

Hi all,

I have (Table_1)below sql script which have too many GLICU numbers to exclude.

What I want is, change my script like  'Table_2' and use excel file which stores the excluded GLICU numbers in a column and  want to use this excel file to exclude those GLICU numbers.

I have tried 'not exists' in my script but could not manage to exclude those numbers.

How can I  manage this task? Any suggestions would be appreciated.

Thanks in advance.

Best Regards;

Onur

[marcus_sommer]

Try it with a different order in the exists-function like:

My_Table:
load * from [lib://QVDs/TEST.QVD] (qvd) where not Exists([Excluded GLICU], BATCH_NUMBER);

Beside this it might be more useful to store the excluding-list within a variable and using it within the sql directly because it would reduce the number of records direct by the source. I mean something like:

EXCLUDED_GLICU:
LOAD
concat("EXCLUDED_GLICU", chr(39) & ',' & chr(39)) as [Excluded GLICU]
FROM [lib://SharedFiles/INTEGRITY CHECK .xlsx]
(ooxml, embedded labels, table is [Excluded GLICU]);

let vExcludedGLICU = peek('Excluded GLICU', 0, 'EXCLUDED_GLICU');

Table_1:
select distinct GLDCT DOC_TYPE,GLICU BATCH_NUMBER,GLFY FISCAL_YEAR,GLPN PERIOD,GLTORG GL_USER from F0911 G,F0901 A
where G.GLMCU not in (' 5',' 3',' 2',' 6') and GLFY>16 and
G.GLAID=A.GMAID and A.GMR005<>'LC' and GLLT='AA' and GLRE=' ' and GLABR3<>' ' and GLICU not in
('$(vExcludedGLICU)') and GLICU not in (select distinct ORICU from F65HDOC1) order by GLICU;

- Marcus

[Jacek]

If you want to focus only for filtering out GLICU ids please use Excluded table (Excel) as a first in script and add "where not exists" for main table:

Excluded:
LOAD GLICU
FROM
[exc.xlsx]
(ooxml, embedded labels, table is Sheet1);

Tabel1:
Load 
BATCH_NO, GLICU

From .... qvd
where not exists(GLICU);

[L_Hop]

Hi Marcus,

Thank you for your reply.

Your suggestion with variable seems more efficient and I tried to accomplish it.

Once I have tried to run the script, I am getting "ORA-01722: invalid number" errors, first and last number of variable seems not have (')chr(39).

regards;

Onur

[marcus_sommer · Accepted solution]

In many cases it worked in exact the same way because the first and last single-quote is set through the variable call which was: '$(var)' but we could change it with the following like:

EXCLUDED_GLICU:
LOAD
chr(39) & concat("EXCLUDED_GLICU", chr(39) & ',' & chr(39)) & chr(39) as [Excluded GLICU]
FROM [lib://SharedFiles/INTEGRITY CHECK .xlsx]
(ooxml, embedded labels, table is [Excluded GLICU]);

let vExcludedGLICU = peek('Excluded GLICU', 0, 'EXCLUDED_GLICU');

Table_1:
select distinct GLDCT DOC_TYPE,GLICU BATCH_NUMBER,GLFY FISCAL_YEAR,GLPN PERIOD,GLTORG GL_USER from F0911 G,F0901 A
where G.GLMCU not in (' 5',' 3',' 2',' 6') and GLFY>16 and
G.GLAID=A.GMAID and A.GMR005<>'LC' and GLLT='AA' and GLRE=' ' and GLABR3<>' ' and GLICU not in
($(vExcludedGLICU)) and GLICU not in (select distinct ORICU from F65HDOC1) order by GLICU;

- Marcus

[L_Hop]

Hi Marcus,

This time variable result is as expected and script gives no error but output data is not expected.

Once I have run  my initial script, I am getting 26 records. I want to exclude some of the records using excel file.

best regards;

Onur

[L_Hop]

Hi again Marcus,

I am really appreciated your help.

Script worked as expected.

Thank you.

Onur