topic Re: SQL error in QlikView

SQL error

Fri, 31 Jan 2014 16:44:26 GMT

Hi, what's wrong in this script?

[EBAN_W]:

ADD

LOAD *

;

SQL

SELECT DISTINCT MATNR, TXZ01

FROM EBAN

WHERE Right(MATNR,1) = '4'

;

But this scrpt works:

[EBAN_W]:

ADD

LOAD *

;

SQL

SELECT DISTINCT MATNR, TXZ01

FROM EBAN

WHERE MATNR = '4000000'

;

Thanks

Re: SQL error

Fri, 31 Jan 2014 16:47:54 GMT

Hi Javier

You can't use QlikView functions in WHERE clause of SQL query. This query is send to database thus only DBMS functions are supported.

Lukasz

Re: SQL error

Fri, 31 Jan 2014 16:48:53 GMT

Hi,

I have this too, because our version of SQL doesn't deal with Right( function, only with substr.

Right(MATNR,1) is then translated into Substr(MATNR,Length(MATNR),1). This is in Oracle SQL.

Hope this helps.

Re: SQL error

Fri, 31 Jan 2014 16:50:18 GMT

You probably need something like SUBSTR(MANTR, -1) = '4' instead of RIGHT

Re: SQL error

Fri, 31 Jan 2014 16:52:40 GMT

Hi Javier,

The SQL Function let you write code with the same syntax and functions as your Database would have, so that is a space where you make a query.

Re: SQL error

Anonymous — Fri, 31 Jan 2014 16:52:46 GMT

Javier

The reply by Lukasz is absolutely correct.

For SQL on Oracle [other brands of SQL may vary] you would need :

SELECT DISTINCT MATNR, TXZ01

FROM EBAN

WHERE MATNR like '%4' ;

Assuming I have understood correctly and need the last character to be 4.

Best Regards, Bill

Re: SQL error

Fri, 31 Jan 2014 17:11:07 GMT

Thanks, This was the script, really I need the number that star with 4, and the functions Left, Rigth, don't work.

[EBAN_W]:

ADD

LOAD *

;

SQL

SELECT DISTINCT MATNR, TXZ01

FROM EBAN

WHERE MATNR LIKE '4%'

;

Re: SQL error

jaimeaguilar — Fri, 31 Jan 2014 17:14:15 GMT

Hi,

It is important to mention that when you're extracting data from a Database, QlikView script can be divided in 2 segments: