Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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?

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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;

View solution in original post

7 Replies
Clever_Anjos
Employee
Employee

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

Not applicable
Author

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
Not applicable
Author

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
Partner - Specialist
Partner - Specialist

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
Employee
Employee

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
Not applicable
Author

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
Employee
Employee

Thank you for sharing your wisdom Michael Solomovich