Date range on USGRP_USER - no selection.

[matt]

Like USR02, USGRP_USER has a validity range. When selecting from USR02, you have to treat an empty validity date field as beginning (or end) of time.

    SELECT usgrp_user~bname usgrp_user~from_dat usgrp_user~to_dat
           usr02~gltgv usr02~gltgb
        INTO TABLE userids
        FROM usgrp_user
        INNER JOIN usr02
                ON usr02~bname EQ usgrp_user~bname
        WHERE usergroup EQ c_deputy_user_group
*          AND ( from_dat LE sy-datum OR from_dat EQ '00000000' )
*          AND ( to_dat   GE sy-datum OR to_dat   EQ '00000000' )
          AND ( gltgv    LE sy-datum OR gltgv    EQ '00000000' )
          AND ( gltgb    GE sy-datum OR gltgb    EQ '00000000' ).<br>

I've a bunch of valid users, with valid groups - some with the dates set, some not. At the moment, I'm looping through userids, after the select.

    LOOP AT userids ASSIGNING <userid>.
      CHECK ( <userid>-from_date LE sy-datum OR <userid>-from_date IS INITIAL ) AND
            ( <userid>-to_date   GE sy-datum OR <userid>-to_date   IS INITIAL ).

Now, if I uncomment lines 2 and 3 of the WHERE clause, I'd expect not to need that CHECK. But then the SELECT gets no data...

Any ideas?

[jmodaal]

Hello,

maybe you mix up the user group visible in SU01(D), tab "Logon data" (field "CLASS" in table USR02) and the additional assignment of a user to a group, visible in tab "Groups" (entry in table USGRP_USER). As the additional assignment is optional and rarely used you can have lots of valid users and still no entry in table USGRP_USER.

[aoyang]

matthew.billingham Tested your code where 2nd and 3rd lines are uncommented and I am getting the user data(as long as the user is both in usgrp_user and usr02 table) in my system. Can you provide a sample user in SE16N that shows that user is in both usgrp_user and usr02 tables?

[matt]

jmodaal It's really USGRP_USER, not CLASS.

[FredericGirod]

The code ... works in the SQL console.

   SELECT usgrp_user~bname,  usgrp_user~from_dat,
          usgrp_user~to_dat, usr02~gltgv,         usr02~gltgb
        INTO TABLE @data(userids)
        FROM usgrp_user
        INNER JOIN usr02
                ON usr02~bname EQ usgrp_user~bname
        WHERE
              ( gltgv    LE @sy-datum OR gltgv    EQ '00000000' )
          AND ( gltgb    GE @sy-datum OR gltgb    EQ '00000000' )
          AND ( from_dat LE @sy-datum OR from_dat EQ '00000000' )
          AND ( to_dat   GE @sy-datum OR to_dat   EQ '00000000' ).

Something strange, I check all the source code accessing this table, no-one make a filter on the date.

[matt]

aocheng

USGRP_USER - Only Username and group are populated

MATT	        ZDEP			
TESTUSR	        ZDEP			
TESTUSR_SS	ZDEP			
TEST_USER	ZDEP			

USR02 - just showing username and validity

MATT		
TESTUSR	        01.01.2010	31.12.2022
TESTUSR_SS	01.01.2010	31.12.2022
TEST_USER		

Bear in mind, that if I omit the WHERE clause for the USGRP_USER fields, all the data is selected without a problem - it would get filtered out during the LOOP AT.

[matt]

System is 7.50, by the way. I'm beginning to suspect that it's the underlying database... (MaxDB 7.9).

[Former Member]

If USGRP_USER~FROM_DAT and USGRP_USER~TO_DAT are not populated then

EQ '00000000' will be false.

you will need OR EQ ' '

[aoyang]

matthew.billingham I have users with the data populated exact same way and in my system your code is working to get the user. Now just in case, can you check in SE16N if internal value for usgrp_user~to_dat is really ''00000000"? (I am assuming so because the data type is DATS and it's initial value is '00000000").

If it is '00000000' like it's supposed to be, as the next step, can you try "OR from_dat IS INITIAL" instead of "OR from_dat EQ '00000000'"? (You need to separate fields with comma and escape variable with @ and so on. 7.50 system should support this.) Do this on to_dat also.

[matt]

Former Member But '00000000' works in USR02.

aocheng I looked in debug, and they're properly '00000000' dats fields in both tables. I've tried EQ SPACE, and EQ '' neither work. I can't use INITIAL since the code must be compatible with 7.31.

[aoyang]

matthew.billingham If INITIAL can't be used in your system, honestly it's hard to know the why it's not working in the ABAP code level, especially when the code works in my system and it seems to work also in Fredreic's.

As the next step, it's better to analyze what's happening in SQL.

Open your HANA studio and login to HANA DB. Write the same SQL and left click->Visualize plan->Execute.

You will see the SQL explained in details. Hopefully you will find some hints there. You can refer to these tutorials.

https://developers.sap.com/tutorials/dt-query-processing-part1.html

https://developers.sap.com/tutorials/dt-query-processing-part2.html

[Former Member]

For USR02 works, but you have values in the date fields. For the rest in the party here it works also for USGRP_USER.

For me it works: NW 7.50 on both Oracle and MaxDB 7.9.

2 screenshots from se16 and from OS MaxDB.

[Sandra_Rossi]

I don't see any mention of "null" in the question and comments. Did you try WHERE ... IS NULL?

[matt]

When I created a standalone program with the same select, it worked fine. That implies the issue lies elsewhere.

Maybe I activated it and didn't notice it had syntax errors. Or some buffer wasn't refreshed.

Anyway, many thanks for your suggestions and comments. I'm sorry I apparently have lead you down the garden path.