topic Re: Can I filter functional locations by using a view, or open sql? in Application Development and Automation Discussions

Can I filter functional locations by using a view, or open sql?

Former Member — Wed, 29 May 2019 18:54:14 GMT

I have a view that I am using that contains fields on IFLOT, ILOA, and IFLOTX.

The view is called ZAPMFLOCS.

I want to be able to exclude functional locations with the deletion flag set.

I was able to figure out the SQL to exclude records.

This is my code without variables substituted.

SELECT (selectpart) FROM (lv_from) UP TO lv_rowcount ROWS INTO CORRESPONDING FIELDS OF TABLE <fs_result> WHERE (wherepart_modified).

This is what it would look like with the substitutions:

SELECT * FROM ZAPMFLOCS UP TO 50000 ROWS INTO CORRESPONDING FIELDS OF TABLE <fs_result> WHERE OBJNR not in ( select f.OBJNR from ZAPMFLOCS f left outer join JEST j on f.OBJNR = j.OBJNR where j.STAT = 'I0076' and j.INACT <> 'X' )

But this fails, I'm guessing my where clause is too complicated to use with open sql.

I'm wondering if there is a way to either alter the view or the code to be able to exclude functional locations with the deletion flag set?

sql-error.jpg

Re: Can I filter functional locations by using a view, or open sql?

Sandra_Rossi — Thu, 30 May 2019 06:22:07 GMT

Depending on your ABAP version, there may be different restrictions.

So first, make sure the substituted SELECT is valid at compile time and at run time.

If it doesn't work, you may also try to switch to the strict mode syntax, because it may accept more complex SQL (adding @ and change order of blocks):

SELECT (selectpart) FROM (lv_from)
WHERE (wherepart_modified)
INTO CORRESPONDING FIELDS OF TABLE @<fs_result>
UP TO @lv_rowcount ROWS.

Re: Can I filter functional locations by using a view, or open sql?

Former Member — Thu, 30 May 2019 11:35:00 GMT

Thanks Sandra!

I'm getting:
@LV_ROWCOUNT may not be converted into a number. number.

It is declared as:

lv_rowcount TYPE i.

Am I missing something?

Looking at the up to documentation:

https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abapselect_up_to_offset.htm

The addition UP TO limits the number of rows in the results set of a SELECT statement to n. n expects a host variable prefixed by an escape character @, a host expression or literal of type i that can represent all non-negative numbers from the value range of i except its maximum value +2,147,483,647. In the strict mode from Release 7.51, n must have the types b, s, i, or int8. Furthermore, a literal or constant specified for n cannot have the value 0 in this strict mode. The content of n must match the data type i in accordance with the rules for a lossless assignment. A host variable should be prefixed by the escape character @.

Our system has "SAP_BASIS" = "740" maybe that means we don't have the required stuff for strict mode in ABAP?

Re: Can I filter functional locations by using a view, or open sql?

DoanManhQuynh — Fri, 31 May 2019 00:33:59 GMT

may I ask above sql is open sql or native sql? the syntax of open SQL shouldnt be:

 table.field

but

table~field

i dont think your sql run. it should be:

SELECT * FROM zapmflocs
UP TO 50000 ROWS
INTO CORRESPONDING FIELDS OF TABLE <fs_result>
WHERE objnr NOT IN ( SELECT f~objnr FROM zapmflocs AS f LEFT OUTER JOIN jest AS j ON f~objnr = j~objnr
                     WHERE j~stat = 'I0076' AND j~inact <> 'X' )

Re: Can I filter functional locations by using a view, or open sql?

Sandra_Rossi — Fri, 31 May 2019 05:45:38 GMT

As I said, first, make sure the substituted SELECT is valid at compile time and at run time. Unfortunately, you probably didn't do it, cf Quynh Doan Manh answer.

Re: Can I filter functional locations by using a view, or open sql?

Sandra_Rossi — Fri, 31 May 2019 06:00:09 GMT

The strict mode is valid from 7.40 SP 2.

" The syntax of this program is valid in 7.52, and it works at runtime too.


DATA: selectpart         TYPE string VALUE '*',
      lv_from            TYPE string VALUE 'SCARR',
      lv_rowcount        TYPE i,
      wherepart_modified TYPE string,
      fs_result          TYPE TABLE OF scarr.


" if strict mode is NOT used, it's NOT possible to have a JOIN in the nested SELECT
SELECT * FROM scarr UP TO lv_rowcount ROWS
  INTO CORRESPONDING FIELDS OF TABLE fs_result
  WHERE carrid NOT IN ( SELECT carrid FROM scarr ).


" if strict mode is used, it's possible to have a JOIN in the nested SELECT
SELECT * FROM scarr UP TO @lv_rowcount ROWS
  INTO CORRESPONDING FIELDS OF TABLE @fs_result
  WHERE carrid NOT IN ( SELECT f~carrid FROM scarr AS f LEFT OUTER JOIN spfli 
        AS j ON f~carrid = j~carrid WHERE j~airpfrom = 'JFK' ).


" the dynamic variant works at runtime only if the strict mode is used
wherepart_modified = |carrid not in ( select f~carrid from scarr as f left outer |
                  && |join spfli as j on f~carrid = j~carrid where j~airpfrom = 'JFK' )|.
SELECT (selectpart) FROM (lv_from)
  UP TO @lv_rowcount ROWS
  INTO CORRESPONDING FIELDS OF TABLE @fs_result
  WHERE (wherepart_modified).

Re: Can I filter functional locations by using a view, or open sql?

Former Member — Fri, 31 May 2019 11:54:46 GMT

Great answer! I still have a compile problem. I didn't think changing the wherepart would affect the compile problem but I did it anyway. Still have the compile problem, but good to know how to fix the wherepart.

I decided to try to run your program as-is, since I couldn't figure out my compile problems.

With your program I get:

"@LV_ROWCOUNT" may not be converted into a number. number.

I tried changing to:

lv_rowcount        TYPE i VALUE 50000,

But I still got:

"@LV_ROWCOUNT" may not be converted into a number. number.

So I tried hardcoding:

SELECT * FROM scarr UP TO 50000 ROWS

And then I got:

No fields from the right-hand table of a LEFT OUTER JOIN may appear in

the WHERE condition: "J~AIRPFROM".

On Installed Software Component Versions I see:

SAP_BASIS
Release 740

SP-Level 0004

Is that the correct place to be looking?

Re: Can I filter functional locations by using a view, or open sql?

Sandra_Rossi — Fri, 31 May 2019 12:05:48 GMT

I guess it's just because J~AIRPFROM would be better in the ON because it's an outer join: "on f~carrid = j~carrid and j~airpfrom ='JFK'" (WHERE not used)

Re: Can I filter functional locations by using a view, or open sql?

Former Member — Fri, 31 May 2019 12:34:26 GMT

Did your code run for you?

I tried altering to use as you did with (WHERE not used)

SELECT * FROM scarr UP TO 50000 ROWS
  INTO CORRESPONDING FIELDS OF TABLE @fs_result
  WHERE carrid NOT IN ( SELECT f~carrid FROM scarr AS f LEFT OUTER JOIN spfli 
        AS j ON f~carrid = j~carrid AND j~airpfrom = 'JFK' ).

And got:

The field "@FS_RESULT" is unknown, but there is a field with the

similar name "FS_RESULT". "FS_RESULT".

Re: Can I filter functional locations by using a view, or open sql?

Former Member — Fri, 31 May 2019 12:37:39 GMT

Here is the complete code listing for the ABAP program with your code:

*&---------------------------------------------------------------------*
*& Report  Z_FLOC_OPEN_SQL_TEST
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*


REPORT Z_FLOC_OPEN_SQL_TEST.

*DATA: T_TABLE TYPE TABLE OF ZAPM_TYP_ZSZAPMFLOCS.
*
*SELECT * FROM zapmflocs
*UP TO 50000 ROWS
*INTO CORRESPONDING FIELDS OF TABLE @T_TABLE
*WHERE objnr NOT IN ( SELECT f~objnr FROM zapmflocs AS f LEFT OUTER JOIN jest AS j ON f~objnr = j~objnr
*                     WHERE j~stat = 'I0076' AND j~inact <> 'X' ).

DATA: selectpart         TYPE string VALUE '*',
      lv_from            TYPE string VALUE 'SCARR',
      lv_rowcount        TYPE i VALUE 50000,
      wherepart_modified TYPE string,
      fs_result          TYPE TABLE OF scarr.

" if strict mode is NOT used, it's NOT possible to have a JOIN in the nested SELECT
SELECT * FROM scarr UP TO lv_rowcount ROWS
  INTO CORRESPONDING FIELDS OF TABLE fs_result
  WHERE carrid NOT IN ( SELECT carrid FROM scarr ).

" if strict mode is used, it's possible to have a JOIN in the nested SELECT
SELECT * FROM scarr UP TO 50000 ROWS
  INTO CORRESPONDING FIELDS OF TABLE @fs_result
  WHERE carrid NOT IN ( SELECT f~carrid FROM scarr AS f LEFT OUTER JOIN spfli 
        AS j ON f~carrid = j~carrid AND j~airpfrom = 'JFK' ).

" the dynamic variant works at runtime only if the strict mode is used
wherepart_modified = |carrid not in ( select f~carrid from scarr as f left outer |
                  && |join spfli as j on f~carrid = j~carrid where j~airpfrom = 'JFK' )|.
SELECT (selectpart) FROM (lv_from)
  UP TO @lv_rowcount ROWS
  INTO CORRESPONDING FIELDS OF TABLE @fs_result
  WHERE (wherepart_modified).

Re: Can I filter functional locations by using a view, or open sql?

Sandra_Rossi — Fri, 31 May 2019 12:53:11 GMT

Yes it works for ABAP 7.52. But the order I used is a little bit incorrect. Maybe only the correct order is accepted in 7.40 (INTO and UP TO at the end):

SELECT (selectpart) 
  FROM (lv_from)
  WHERE (wherepart_modified)
  INTO CORRESPONDING FIELDS OF TABLE @fs_result
  UP TO @lv_rowcount ROWS.

Re: Can I filter functional locations by using a view, or open sql?

Former Member — Fri, 31 May 2019 13:16:09 GMT

That one gives me:

Couldn't find Error Message: E SELECT 473 because the system is running
with the database objects from release 740. The system has not
completely upgraded to release 742.

p.s Thanks so much for all of your help!

Re: Can I filter functional locations by using a view, or open sql?

Sandra_Rossi — Fri, 31 May 2019 13:32:23 GMT

Unfortunately, it seems impossible to work well with your version. It seems that you need to upgrade the kernel, at least. Or you have to find a workaround by yourself (try all possible ways until something works...)

Re: Can I filter functional locations by using a view, or open sql?

DoanManhQuynh — Mon, 03 Jun 2019 00:28:11 GMT

Derek Morin

for your information, my system is 7.4 SP 14 and your code work for me.