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

[#1]

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?

[Clever_Anjos]

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

[#3]

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?

[Anonymous · Accepted solution]

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;

[jpapador]

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;

[Clever_Anjos]

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.

[Anonymous]

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

[Clever_Anjos]

Thank you for sharing your wisdom Michael Solomovich