Script - Why "FIELD like 'string*'" doesn't work ?

[#1]

Hi guys,

I'm trying to load a table from a DB and I want to exclude some records.

To do that I thought to use the LIKE clause in my SQL.

Unfortunately it doesn't work.

I'm expecting 9 records and instead I have two strange results.

if I write:

LOAD ACCOUNT_ID,

VALID_FOR_USER_GROUP;

SQL SELECT *

FROM CUST_VALUES

WHERE VALID_FOR_USER_GROUP LIKE '*Sesi*';

I have 0 records.

if I write:

LOAD ACCOUNT_ID,

VALID_FOR_USER_GROUP;

SQL SELECT *

FROM CUST_VALUES

WHERE VALID_FOR_USER_GROUP LIKE '%Sesi%';

I have 6 records, NOT 9.

Any suggestions?

Thanks a lot

Giampiero

[stephencredmond]

Hi Giampero,

SQL doesn't support * as a wild card - it uses %.

That is why the first one returns nothing and the second one works.

If you run the SQL (from SELECT * onwards) in a query tool, I would expect that you will only get 6 results also.

Regards,

Stephen

[#3]

Hi Stephen,

thanks a lot.

I tried to use * because on the manual there are some examples using * and I thought it was correct.

About the results with %.

I'm sure we have to obtain 9 records bacause I first tried to do the same Query on my DB, for this reason i'm not understanding the results.

Thank you so much for your help

Best regards

Giampiero

[stephencredmond]

Hi Giampiero,

The reason that I think there will only be 6 records in the query is that QlikView is not responsible for collecting the records - the database driver is responsible to execute the query to the database engine and return the records to QlikView.

So, if you are only getting 6 records in QlikView, it is because there are only 6 records returned by the driver.

Regards,

Stephen

[SunilChauhan]

LOAD ACCOUNT_ID,

VALID_FOR_USER_GROUP

WHERE wildmatch(VALID_FOR_USER_GROUP, '*Sesi*');

;

SQL SELECT *

FROM CUST_VALUES;

sorry steafen i  have not see select staement

hope this helps

[stephencredmond]

Hi Sunil,

First, your Wildmatch function is not formed correctly.

Secondly, Wildmatch is not a SQL function - it is a QlikView equivalent of a LIKE function (although QlikView also has LIKE).

You can include QlikView where clauses in a preceding load like this:

LOAD ACCOUNT_ID,

VALID_FOR_USER_GROUP

Where WildMatch(VALID_FOR_USER_GROUP, '*Sesi*')

;

SQL SELECT *

FROM CUST_VALUES;

However, that would mean that all of the table rows are returned from the database before they are filtered by QlikView.  This is less efficient than having the database engine filter them.

Regards,

Stephen

[SunilChauhan]

sorry steafen i  have not see select staement

LOAD ACCOUNT_ID,

VALID_FOR_USER_GROUP

WHERE wildmatch(VALID_FOR_USER_GROUP, '*Sesi*');

;

SQL SELECT *

FROM CUST_VALUES;

or

if we create a conneection then it creates the environment like  data base

so check syntax like also needs to work

hope this helps