topic Select Query...... Combinations in Application Development and Automation Discussions

Select Query...... Combinations

Former Member — Tue, 25 Feb 2014 12:42:11 GMT

Hi Experts,

I have a Custom table for finding approver which looks like the below data. If no data is entered in the highlighted fields it means " * " which is applicable for all.

Appl Type	Appl Subtype	Pers Area	Pers SubArea	Emp Grp	Emp Subgrp	End Date	Appr Level	Begin Date	Approver
CLAIM	1111					31.12.9999	3	01.01.2010	XXX
CLAIM	1111	PA				31.12.9999	3	01.01.2010	YYY

I am trying to write Select Query on this table as below.

DATA: t_appl_subty TYPE RANGE OF zzp_appl_sub,

w_appl_subty-sign = |I|.
w_appl_subty-option = |EQ|.
w_appl_subty-low = i_appl_subty.
APPEND w_appl_subty TO t_appl_subty.
CLEAR w_appl_subty.
w_appl_subty-sign = |I|.
w_appl_subty-option = |EQ|.
w_appl_subty-low = ''.
APPEND w_appl_subty TO t_appl_subty.
CLEAR w_appl_subty.

w_persa-sign = |I|.
w_persa-option = |EQ|.
w_persa-low = i_persa.
APPEND w_persa TO t_persa.
CLEAR w_persa.
w_persa-sign = |I|.
w_persa-option = |EQ|.
w_persa-low = ''.
APPEND w_persa TO t_persa.
CLEAR w_persa.

w_btrtl-sign = |I|.
w_btrtl-option = |EQ|.
w_btrtl-low = i_btrtl.
APPEND w_btrtl TO t_btrtl.
CLEAR w_btrtl.
w_btrtl-sign = |I|.
w_btrtl-option = |EQ|.
w_btrtl-low = ''.
APPEND w_btrtl TO t_btrtl.
CLEAR w_btrtl.

w_persg-sign = |I|.
w_persg-option = |EQ|.
w_persg-low = i_persg.
APPEND w_persg TO t_persg.
CLEAR w_persg.
w_persg-sign = |I|.
w_persg-option = |EQ|.
w_persg-low = ''.
APPEND w_persg TO t_persg.
CLEAR w_persg.

w_persk-sign = |I|.
w_persk-option = |EQ|.
w_persk-low = i_persk.
APPEND w_persk TO t_persk.
CLEAR w_persk.
w_persk-sign = |I|.
w_persk-option = |EQ|.
w_persk-low = ''.
APPEND w_persk TO t_persk.
CLEAR w_persk.

SELECT SINGLE * FROM zapprover
                  INTO wa_approver
                 WHERE appl_typ = i_appl_typ
                   AND appl_subty IN t_appl_subty
                   AND persa IN t_persa
                   AND btrtl IN t_btrtl
                   AND persg IN t_persg
                   AND persk IN t_persk
                   AND appr_lvl = i_appr_lvl
                   AND endda GE sy-datum.

I am passing data as

i_appl_typ = 'CLAIM'

i_appl_subty = '1111'

i_persa = 'PA'

i_btrtl = ' '

i_persg = ' '

i_persk = ' '

i_appr_lvl = 3

It is picking the First record, but as per my requirement if no data available with data “PA” then it has to fetch first record. I am not sure,It seems to be leading to combinations.

How can I write Select Query?

Please help me.

Re: Select Query...... Combinations

Former Member — Tue, 25 Feb 2014 15:17:19 GMT

Hi, try to remove rows:

CLEAR w_persa.
w_persa-sign = |I|.
w_persa-option = |EQ|.
w_persa-low = ''.
APPEND w_persa TO t_persa.
CLEAR w_persa.

Regards

Re: Select Query...... Combinations

former_member183073 — Tue, 25 Feb 2014 15:37:43 GMT

Hi Riyaz,

your select statement is correct, i is working as coded.

if you want blank records only if there are no records found for the filled values.

1. use the select statement with filled value and if there no records select again using blank(Do this only for the highlighted column) and use read to check if the second highlighted column in the internal table has required value

if yes deleted the itab where second column is blank. need to do this for each column

Re: Select Query...... Combinations

Former Member — Wed, 26 Feb 2014 04:33:55 GMT

Hi Ali,

You are right, but it is not with single column data. In real time it may consist 4 columns data at a time and 3 and 2 and 1, with combinations. So i can not delete itab with single column data..if i understodd correctly...right?

Re: Select Query...... Combinations

dibyajeeban_jena — Wed, 26 Feb 2014 05:03:18 GMT

Hi,

You are appending two values in to t_persa i.e i_persa = 'PA' and ' ' .

AS u r selecting single record the first record having one of the above values will be selected by system . the same is happening in ur case.

Solution- If you r passing values dynamically ie. i_persa = 'PA' then no need to append a blank value . Remove below lines ,in other cases also.

w_persa-sign = |I|.
w_persa-option = |EQ|.
w_persa-low = i_persa.
APPEND w_persa TO t_persa.
CLEAR w_persa.
w_persa-sign = |I|.
w_persa-option = |EQ|.
w_persa-low = ''.
APPEND w_persa TO t_persa.
CLEAR w_persa.

Regards

Re: Select Query...... Combinations

Former Member — Wed, 26 Feb 2014 05:26:31 GMT

Hi DJ, Boris,

I think you are not getting my point. Lets say i need to get data based on i_persa = 'PA' but there is no entry in table with i_persa = 'PA' . Then i need to pick with SPACE entry. Just for example i had given only two records of data.

Re: Select Query...... Combinations

dibyajeeban_jena — Wed, 26 Feb 2014 05:35:06 GMT

Hi,

Then u need to achieve it by putting conditions . like..

w_persa-sign = |I|.
w_persa-option = |EQ|.
w_persa-low = i_persa. " i_persa = 'AA'
APPEND w_persa TO t_persa.

SELECT SINGLE * FROM zapprover
                  INTO wa_approver
                 WHERE appl_typ = i_appl_typ
                   AND appl_subty IN t_appl_subty
                   AND persa IN t_persa
                   AND btrtl IN t_btrtl
                   AND persg IN t_persg
                   AND persk IN t_persk
                   AND appr_lvl = i_appr_lvl
                   AND endda GE sy-datum.

IF SY-SUBRC NE 0 .

w_persa-sign = |I|.
w_persa-option = |EQ|.
w_persa-low = i_persa. " i_persa = ''
APPEND w_persa TO t_persa.

ENDIF .

Re: Select Query...... Combinations

Former Member — Wed, 26 Feb 2014 07:50:46 GMT

You mean to say i have to check 4! times? (Not sure whether it could be factorial symbol or other)

Re: Select Query...... Combinations

Former Member — Wed, 26 Feb 2014 08:17:11 GMT

do not complicate the select statement. Fetch all the data for the corresponding appl type. Then in your abap do the logic needed on a internal table, e.g.

1. if there is an exact match - that is it

2. else - if there is an empty match - that is it

Re: Select Query...... Combinations

Former Member — Wed, 26 Feb 2014 09:03:03 GMT

Hi Jozef,

Initially i tried like this,

GET RUN TIME FIELD DATA(t1).

DATA: it_approver TYPE STANDARD TABLE OF zapprover,
        lv_subrc TYPE sy-subrc.

DATA: lv_appl_typ TYPE zzp_appl_typ,
        lv_appl_subty TYPE zzp_appl_sub,
        lv_persa TYPE persa,
        lv_btrtl TYPE btrtl,
        lv_persg TYPE persg,
        lv_persk TYPE persk,
        lv_appr_lvl TYPE zzp_appr_lvl.

REFRESH it_approver[].
CLEAR: wa_approver,lv_appl_typ,lv_appl_subty,lv_persa,lv_btrtl,lv_persg,lv_persk,lv_appr_lvl,lv_subrc.

PERFORM fill_local_values USING i_appl_typ
                                  i_appl_subty
                                  i_persa
                                  i_btrtl
                                  i_persg
                                  i_persk
                                  i_appr_lvl
                         CHANGING lv_appl_typ
                                  lv_appl_subty
                                  lv_persa
                                  lv_btrtl
                                  lv_persg
                                  lv_persk
                                  lv_appr_lvl.

SELECT * FROM zapprover
                  INTO TABLE it_approver
                 WHERE appl_typ = i_appl_typ
                 AND endda GT sy-datum.
IF 0 = sy-subrc.
    SORT it_approver.
    IF i_appl_typ = 'CLAIM'.
      PERFORM read_appr TABLES it_approver
                         USING lv_appl_subty
                               lv_persa
                               lv_btrtl
                               lv_persg
                               lv_persk
                               lv_appr_lvl
                      CHANGING lv_subrc
                               wa_approver.
      IF NOT lv_subrc IS INITIAL AND wa_approver IS INITIAL.
        DO 4 TIMES.
          CLEAR lv_subrc.
          PERFORM fill_local_values USING i_appl_typ
                                          i_appl_subty
                                          i_persa
                                          i_btrtl
                                          i_persg
                                          i_persk
                                          i_appr_lvl
                                 CHANGING lv_appl_typ
                                          lv_appl_subty
                                          lv_persa
                                          lv_btrtl
                                          lv_persg
                                          lv_persk
                                          lv_appr_lvl.
          CASE sy-index.
            WHEN 1.
              CLEAR: lv_persk.
            WHEN 2.
              CLEAR: lv_persg.
            WHEN 3.
              CLEAR: lv_btrtl.
            WHEN 4.
              CLEAR: lv_persa.
            WHEN OTHERS.
          ENDCASE.
          PERFORM read_appr TABLES it_approver
                             USING lv_appl_subty
                                   lv_persa
                                   lv_btrtl
                                   lv_persg
                                   lv_persk
                                   lv_appr_lvl
                          CHANGING lv_subrc
                                   wa_approver.
          IF lv_subrc IS INITIAL.
            EXIT.
          ENDIF.
        ENDDO.
        IF NOT lv_subrc IS INITIAL AND wa_approver IS INITIAL.
          DO 6 TIMES.
            CLEAR lv_subrc.
            PERFORM fill_local_values USING i_appl_typ
                                            i_appl_subty
                                            i_persa
                                            i_btrtl
                                            i_persg
                                            i_persk
                                            i_appr_lvl
                                   CHANGING lv_appl_typ
                                            lv_appl_subty
                                            lv_persa
                                            lv_btrtl
                                            lv_persg
                                            lv_persk
                                            lv_appr_lvl.
            CASE sy-index.
              WHEN 1.
                CLEAR: lv_persa,lv_btrtl.
              WHEN 2.
                CLEAR: lv_persa,lv_persg.
              WHEN 3.
                CLEAR: lv_persa,lv_persk.
              WHEN 4.
                CLEAR: lv_persg,lv_btrtl.
              WHEN 5.
                CLEAR: lv_persg,lv_persk.
              WHEN 6.
                CLEAR: lv_persk,lv_btrtl.
              WHEN OTHERS.
            ENDCASE.
            PERFORM read_appr TABLES it_approver
                               USING lv_appl_subty
                                     lv_persa
                                     lv_btrtl
                                     lv_persg
                                     lv_persk
                                     lv_appr_lvl
                            CHANGING lv_subrc
                                     wa_approver.
            IF lv_subrc IS INITIAL.
              EXIT.
            ENDIF.
          ENDDO.
        ELSE.
          EXIT.
        ENDIF.
        IF NOT lv_subrc IS INITIAL AND wa_approver IS INITIAL.
          DO 4 TIMES.
            CLEAR lv_subrc.
            PERFORM fill_local_values USING i_appl_typ
                                            i_appl_subty
                                            i_persa
                                            i_btrtl
                                            i_persg
                                            i_persk
                                            i_appr_lvl
                                   CHANGING lv_appl_typ
                                            lv_appl_subty
                                            lv_persa
                                            lv_btrtl
                                            lv_persg
                                            lv_persk
                                            lv_appr_lvl.

            CASE sy-index.
              WHEN 1.
                CLEAR: lv_persa,lv_btrtl,lv_persg.
              WHEN 2.
                CLEAR: lv_persa,lv_persg,lv_persk.
              WHEN 3.
                CLEAR: lv_persa,lv_persk,lv_btrtl.
              WHEN 4.
                CLEAR: lv_btrtl,lv_persg,lv_persk.
              WHEN OTHERS.
            ENDCASE.
            PERFORM read_appr TABLES it_approver
                               USING lv_appl_subty
                                     lv_persa
                                     lv_btrtl
                                     lv_persg
                                     lv_persk
                                     lv_appr_lvl
                            CHANGING lv_subrc
                                     wa_approver.
            IF lv_subrc IS INITIAL.
              EXIT.
            ENDIF.
          ENDDO.
        ELSE.
          EXIT.
        ENDIF.
        IF NOT lv_subrc IS INITIAL AND wa_approver IS INITIAL.
          CLEAR: lv_persa,lv_btrtl,lv_persg,lv_persk.
          PERFORM read_appr TABLES it_approver
                             USING lv_appl_subty
                                   lv_persa
                                   lv_btrtl
                                   lv_persg
                                   lv_persk
                                   lv_appr_lvl
                          CHANGING lv_subrc
                                   wa_approver.
        ELSE.
          EXIT.
        ENDIF.
      ENDIF.
    ENDIF.
ELSE.
    " Error Handling
ENDIF.

GET RUN TIME FIELD DATA(t2).
DATA(t3) = t2 - t1.

FORM fill_local_values USING    VALUE(p_0135) TYPE zzp_appl_typ
                                 VALUE(p_0136) TYPE zzp_appl_sub
                                 VALUE(p_0137) TYPE persa
                                 VALUE(p_0138) TYPE btrtl
                                 VALUE(p_0139) TYPE persg
                                 VALUE(p_0140) TYPE persk
                                 VALUE(p_0141) TYPE zzp_appr_lvl
                        CHANGING p_appl_typ TYPE zzp_appl_typ
                                 p_appl_sub TYPE zzp_appl_sub
                                 p_persa    TYPE persa
                                 p_btrtl    TYPE btrtl
                                 p_persg    TYPE persg
                                 p_persk    TYPE persk
                                 p_appr_lvl TYPE zzp_appr_lvl.

p_appl_typ = p_0135.
p_appl_sub = p_0136.
p_persa     = p_0137.
p_btrtl     = p_0138.
p_persg     = p_0139.
p_persk     = p_0140.
p_appr_lvl = p_0141.ENDFORM.                    " FILL_LOCAL_VALUES

FORM read_appr TABLES   p_table STRUCTURE ztinp_approver
                USING    VALUE(p_0050) TYPE zzp_appl_sub
                         VALUE(p_0051) TYPE persa
                         VALUE(p_0052) TYPE btrtl
                         VALUE(p_0053) TYPE persg
                         VALUE(p_0054) TYPE persk
                         VALUE(p_0055) TYPE zzp_appr_lvl
                CHANGING p_subrc TYPE sy-subrc
                         p_result TYPE ztinp_approver.

READ TABLE p_table INTO p_result
                                WITH KEY appl_subty = p_0050
                                              persa = p_0051
                                              btrtl = p_0052
                                              persg = p_0053
                                              persk = p_0054
                                           appr_lvl = p_0055 BINARY SEARCH.
p_subrc = sy-subrc.
ENDFORM.                    " READ_APPR

I don't know whether this a best method or not, but working perfectly as i wanted. T3 is getting around 600 - 800ms. I don't how much time it could be for best execution. And i am feeling guilty to write this much code for single record. Please suggest me the simple and best way.

Re: Select Query...... Combinations

former_member183073 — Wed, 26 Feb 2014 09:13:31 GMT

Only one question Riyaz from your table,

Suppose you have a condition is

Appl Subtype = 1111 Pers Area = PA Pers SubArea = PSA

In Person Subarea you dont have any value in your example table, so should the query fetch both the records or only where Pers Area = PA(the second record)

If Only Second then the approach which i told is correct.

First filter ITAB with first column(Appl Subtype). Use Read Statement to check if required value exist

If col value match delete other rows which are blank

Second Filter ITAB with first and second column(Appl Subtyp and Pers Area)

If col value match delete other rows which are blank

third Filter ITAB with first second and third column(Appl Subtyp and Pers Area Pers Sub)

If col value match delete other rows which are blank

*** Follow same for remaining columns

elseif doesnt match delete the rows where third column is not blank

elseif doesnt match delete the rows where second column is not blank

elseif doesnt match delete the rows where first column is not blank

Re: Select Query...... Combinations

Former Member — Wed, 26 Feb 2014 09:35:28 GMT

Yes Ali. I think it will work. But in the first column elseif i have to write the above 3 if..else statements again. The same for the second,third and fourth columns repeatedly with different conditions. Right? Did I understood correctly? if not eloborate with small example.

Re: Select Query...... Combinations

former_member183073 — Wed, 26 Feb 2014 10:09:51 GMT

the logic i gave covers three column, you need to use

Ok Giving logic for two of the columns.

sort itab by ApplSubtyp PersArea ...

Read table itab transporting no fields with key ApplSubtyp = <value>... binary search.

if sy-subrc is initial.

Delete itab where ApplSybTyp is initial.

read table itab transporting no fields with key ApplSubtyp = <value>

PersArea = <value> binary search.

if sy-subrc is initial.

Delete itab where PersArea is initial.

**** Insert same if else code here for other columns.

else.

Delete itab where PersArea is not initial.

endif.

else.

Delete itab where ApplSubtyp is not initial.

endif.

Re: Select Query...... Combinations

Former Member — Wed, 26 Feb 2014 10:58:45 GMT

Sorry Ali i didn't get you clearly. If first IF is failed, after deleting itab then how do i get data?

May be you are not considering this kind of data.

Appl Type	Appl Subtype	Pers Area	Pers SubArea	Emp Grp	Emp Subgrp	End Date	Appr Level	Begin Date	Approver
CLAIM	1111		PSA			31.12.9999	3	01.01.2010	XXX
CLAIM	1111			EG	ESG	31.12.9999	3	01.01.2010	YYY

Re: Select Query...... Combinations

Former Member — Wed, 26 Feb 2014 11:06:34 GMT

Well your fill_local_values form is probably not needed when used this way (would not use a form, just direct variables assignment. Your code can be shortened much. But if you follow the inner voice, which says - once it does what it has to, it should be ok for the customer, too .

I do not know the specific logic, you used in those parts leaving the HR fields empty. But once this is a common logic, you could customize those rules in a Z-table. On the other side - you need to compare the additional value to the additional effort needed. Those are questions only you can answer.

Re: Select Query...... Combinations

former_member183073 — Wed, 26 Feb 2014 12:04:27 GMT

when will the first column filter fails here?

if you have APPLSUBTYPE EQ 1111.

condtion passes, and you will have other columns to filter.

If condition fails, in case if APPLSUBTYPE EQ 1112

the two records will be deleted.

do you need to consider records even if first column doesnt match?

Re: Select Query...... Combinations

Former Member — Wed, 26 Feb 2014 13:03:32 GMT

No. It could be a SPACE which means '*', applicable for all. So as per your logic it will be based on if else conditions(may be more no'of ) till we find the correct record. Am i right?