topic Re: Query to SAP scritp with: " where right(OBJNR,7) > '1030000' " in QlikView

Query to SAP scritp with: " where right(OBJNR,7) > '1030000' "

Thu, 23 Jan 2014 18:03:10 GMT

Hi everyone

I got the the following select in my script:

Load

OBJNR,

STAT,

INACT,

CHGNR;

SQL Select OBJNR STAT INACT CHGNR from JEST where right(OBJNR,7) > 1031000 ;

The field OBJNR is like "OR0000001033466", and i just need to get where the last 7 numbers are > '1030000'.

I want to make a where right(OBJNR,7) > '1030000' in the where clause. but does not work.

I also try: where num(right(OBJNR,7)) > '1030000'

but doesn't work too.

can anyone help me?

Re: Query to SAP scritp with: " where right(OBJNR,7) > '1030000' "

Clever_Anjos — Thu, 23 Jan 2014 18:13:09 GMT

SQL queries does not support QlikView Functions, they´re handled by the Database

If you using Oracle for example try to convert to a number using Oracle Functions

where to_number(right(OBJNR,7)) > 1030000

Re: Query to SAP scritp with: " where right(OBJNR,7) > '1030000' "

Thu, 23 Jan 2014 18:13:35 GMT

The statement you have written:

SQL Select OBJNR STAT INACT CHGNR from JEST where right(OBJNR,7) > 1031000 ;

don't you think you need commas between each field and also try using '1031000'.

Are you getting any error, it is just returning all records?

Re: Query to SAP scritp with: " where right(OBJNR,7) > '1030000' "

Anonymous — Thu, 23 Jan 2014 20:50:43 GMT

Combining responses from Clever Anjos and Angad Singh, and adding from myself just a little:

Load

OBJNR,

STAT,

INACT,

CHGNR

where right(OBJNR,7) > 1031000;

SQL Select OBJNR, STAT, INACT, CHGNR from JEST;

Re: Query to SAP scritp with: " where right(OBJNR,7) > '1030000' "

jpapador — Thu, 23 Jan 2014 20:58:12 GMT

I agree with Michael, though I have noticed that most data fields are stored as text in SAP you may need to use a num wrapper to format the field as such, for example:

Load

OBJNR,

STAT,

INACT,

CHGNR

where right(Num(OBJNR),7) > 1031000;

SQL Select OBJNR, STAT, INACT, CHGNR from JEST;

Re: Query to SAP scritp with: " where right(OBJNR,7) > '1030000' "

Clever_Anjos — Fri, 24 Jan 2014 13:51:00 GMT

Michael Solomovich, I´m your fan and I´d like to ask about one doubt about this type of code (Where condition at LOAD, not SQL).

In this case, all lines are retrievied by database and QlikView filters? I think this could be unnapropriate for example if database retrives millions of rows if QV need thousands or even less.

Re: Query to SAP scritp with: " where right(OBJNR,7) > '1030000' "

Anonymous — Fri, 24 Jan 2014 15:08:30 GMT

Clever Anjos - a valid doubt.

Yes, if we filter on LOAD instead of SQL, all records are retrieved from the data source and filtered after that. Hence it is slower. By using filtering on LOAD, we're gaining clarity and flexibility of the script (at least for me), but loosing load speed. So, it depends on priorities.

If the number of rows is large, and we need only a small fraction of it, and reload time is a priority, it is better to filter on the SQL SELECT rather than on LOAD.

Million rows take seconds to load, so the delay is insignificant even if you need just a few records. When there are hundreds of millions, and we need a small portion, then filtering on SQL SELECT makes noticeable difference.

Thanks for pointing it out - it may be essential when choosing how to filter.

Regards,

Michael

Re: Query to SAP scritp with: " where right(OBJNR,7) > '1030000' "

Clever_Anjos — Fri, 24 Jan 2014 15:15:33 GMT

Thank you for sharing your wisdom Michael Solomovich