- SAP Community
- Groups
- Interest Groups
- Application Development
- Discussions
- Select Query...... Combinations
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Select Query...... Combinations
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-25-2014 12:42 PM
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.
- SAP Managed Tags:
- ABAP Development
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-25-2014 3:17 PM
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
- SAP Managed Tags:
- ABAP Development
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-25-2014 3:37 PM
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
- SAP Managed Tags:
- ABAP Development
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-26-2014 4:33 AM
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?
- SAP Managed Tags:
- ABAP Development
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-26-2014 9:13 AM
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
- SAP Managed Tags:
- ABAP Development
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-26-2014 9:35 AM
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.
- SAP Managed Tags:
- ABAP Development
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-26-2014 10:09 AM
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.
- SAP Managed Tags:
- ABAP Development
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-26-2014 10:58 AM
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 |
|
|
| 3 |
| XXX |
CLAIM | 1111 |
|
| EG | ESG |
| 3 |
| YYY |
- SAP Managed Tags:
- ABAP Development
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-26-2014 12:04 PM
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?
- SAP Managed Tags:
- ABAP Development
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-26-2014 1:03 PM
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?
- SAP Managed Tags:
- ABAP Development
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-26-2014 5:03 AM
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
DJ
- SAP Managed Tags:
- ABAP Development
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-26-2014 5:26 AM
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.
- SAP Managed Tags:
- ABAP Development
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-26-2014 5:35 AM
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.
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.
ENDIF .
- SAP Managed Tags:
- ABAP Development
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-26-2014 7:50 AM
You mean to say i have to check 4! times? (Not sure whether it could be factorial symbol or other)
- SAP Managed Tags:
- ABAP Development
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-26-2014 8:17 AM
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
- SAP Managed Tags:
- ABAP Development
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-26-2014 9:03 AM
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.
- SAP Managed Tags:
- ABAP Development
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-26-2014 11:06 AM
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.
- SAP Managed Tags:
- ABAP Development
