3 Replies Latest reply: Jan 20, 2014 1:54 AM by Stanislav Jäger

# How to use QV function WHERE exists for qvd optimized load

Hi everybody,

I'm not so sure if you
already know and use following trick. The first time I was disappointed with

Example:

(qvd)

WHERE
FIELD2='Value_for_condition';

really long and condition caused not optimized load.

I tried following:

1) Define temporary table

2) using this temp table in
condition as where exists ();

Example:

TEMP:

[FIELD2

Value_for_or_condition

Value_for_or_condition2

];

TABLE:

My.qvd (qvd) WHERE EXISTS ([FIELD2]);

drop table TEMP;

*******

Well, this has certain
issues.

- Field used in a condition
has to be selected as well, even if it is not needed (otherwise not qvd optimized)

as y; load * from (qvd); -> is slow, not a qvd optimized

- two condition fileds are
not possible with where exists -> FIELD2='11' AND FIELD3='22' -> causes

How do you handle more
data in qvd are not in the needed format for any reason)

Regards

Stan

• ###### Re: How to use QV function WHERE exists for qvd optimized load

Thanks for the idea! I didn't know this way to do an optimized load.

So, if I understood, you are asking how you could do a combination of conditions. I think you could do something like:

From table.qvd

Where (a=100 and b=78) or (b=500 and a=69);

I guess you could do:

TEMP:

[a, b

100, 78

69,   500

];

Left join

Load Distinct a, b, a&'_'&b as a_b Resident TEMP;

Table:

From table.qvd(qvd)

Where exists (a&'_'&b, a_b);

I dont know if this will be optimized, but you can try at less!!

• ###### Re: How to use QV function WHERE exists for qvd optimized load

Hi , If you concatenate in the where exists function , it changed qvd load as un-optimized load. In this case in the QVD loader we need to create the composite keys.

TABLENAME:

LOAD * , A&'-'&B AS KEY;

SELECT * FROM TABLENAME;

STORE TABLENAME into TABLENAME.qvd (qvd) ;

------- MAIN QVW --------

TEMP:

LOAD A & '-' & B AS KEY INLINE [

A , B

10, 100

32, 165

45, 90

];

Where EXISTS(KEY);

DROP Table TEMP ;

This Load keeps Optimized load. If you are not doing any incremental load , use Autonumber functions for composite keys.

• ###### Re: How to use QV function WHERE exists for qvd optimized load

I was thinking about the same composite key approach. However you can never know if there won't be a similar need with another concatenated key. Then there has to be another A&C field.

In a selection, all possible combinations should be given, not just where Field (A,B,C) AND Field2(D,E,F). You should know then combinations like AD, AE, AF, BD, BE, BF ....

But I will keep it on my mind.

Regards

Stan