Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Select statement not working properly what is the problem in below code

Former Member

‎05-11-2010 8:19 AM

0 Kudos

HI all,

I have written some logic in such a way i get the data from hdr table and later i check the details table based on some combination is there any data exist or not then again i go back to hdr table i get the relavent data.



      SELECT * INTO CORRESPONDING FIELDS OF TABLE t_formdata
                 FROM  zbrf_benf_list    " Header table
                 WHERE fin_id      = iv_fincr_id
                 AND   loan_no     = iv_loan_id
                 AND   delete_flag = ' '
*                 AND   benf_status NE  'INAC'.
                 AND   benf_status IN  lt_benf_status.
      IF sy-subrc <> 0.
        wa_return-type       = 'E'.
        wa_return-id         = 'ZPPM'.
        wa_return-number     = '000'.
        wa_return-message_v1 = 'Data not found.'.
        PERFORM fill_return_tab TABLES t_return
                                USING  wa_return .
      ELSE.
        APPEND LINES OF t_formdata    TO lt_formdata.

      ENDIF.
      IF iv_brf_type = 'DA'.


* Get all DA_ID of primary loan
        PERFORM get_ilap_related_loans TABLES lt_rel_loans
                                       USING  iv_brf_type
                                              iv_fincr_id
                                              iv_loan_id.
* try to find distinct DA_ID of the primary loan
* Here DA_ID is internal id
        DELETE ADJACENT DUPLICATES FROM lt_rel_loans
                         COMPARING sa_internal_id.
        IF lt_rel_loans IS NOT INITIAL.
          SELECT * FROM zbrf_benf_list  
                   APPENDING CORRESPONDING FIELDS OF TABLE t_formdata
                FOR ALL ENTRIES IN lt_rel_loans
                        WHERE fin_id      = iv_fincr_id
                        AND   da_id       = lt_rel_loans-sa_internal_id
                        AND   delete_flag = ' '
*                        AND   benf_status NE  'INAC'
                        AND   benf_status IN  lt_benf_status.
        ENDIF.
        SORT t_formdata DESCENDING BY  brf_id .
      ELSE.   "This else will trigger because of type
        CLEAR lt_ilap_benf_det.
        REFRESH lt_ilap_benf_det.
* Get all DA_ID of primary loan

        SELECT * FROM zbrf_benf_det
              INTO TABLE lt_ilap_benf_det
          FOR ALL ENTRIES IN lt_formdata  
          WHERE brf_id  NE lt_formdata-brf_id  Here my condition fails. Except the entries which i have chosen in hdr  should "not be taken again
             AND ver_id NE lt_formdata-ver_id
            AND fin_id = iv_fincr_id
            AND loan_no = iv_loan_id.

        IF sy-subrc = 0.
          SELECT * FROM zbrf_benf_list
              APPENDING CORRESPONDING FIELDS OF TABLE t_formdata
            FOR ALL ENTRIES IN lt_ilap_benf_det
                WHERE brf_id = lt_ilap_benf_det-brf_id
                AND   ver_id = lt_ilap_benf_det-ver_id
                AND   fin_id = iv_fincr_id
                AND   loan_no = iv_loan_id
                AND   delete_flag = ' '
                AND   benf_status IN  lt_benf_status.

* There should not be any duplicates
        ENDIF.

      ENDIF.

11 REPLIES 11

former_member194669
Active Contributor

‎05-11-2010 8:25 AM

0 Kudos

Can you please arrange the code. its very difficult to read

a®

Former Member
In response to former_member194669

‎05-11-2010 8:30 AM

0 Kudos

HI thanks for reply,

I used this braces

but still i see the code as some text. It is the bug from SDN

Regards,

Lisa

Former Member
In response to former_member194669

‎05-11-2010 8:31 AM

0 Kudos

Here i am posting code again




      SELECT * INTO CORRESPONDING FIELDS OF TABLE t_formdata
                 FROM  zbrf_benf_list
                 WHERE fin_id      = iv_fincr_id
                 AND   loan_no     = iv_loan_id
                 AND   delete_flag = ' '
*                 AND   benf_status NE  'INAC'.
                 AND   benf_status IN  lt_benf_status.
      IF sy-subrc <> 0.
        wa_return-type       = 'E'.
        wa_return-id         = 'ZPPM'.
        wa_return-number     = '000'.
        wa_return-message_v1 = 'Data not found.'.
        PERFORM fill_return_tab TABLES t_return
                                USING  wa_return .
      ELSE.
        APPEND LINES OF t_formdata    TO lt_formdata.

      ENDIF.
      IF iv_brf_type = 'DA'.


* Get all DA_ID of primary loan
        PERFORM get_ilap_related_loans TABLES lt_rel_loans
                                       USING  iv_brf_type
                                              iv_fincr_id
                                              iv_loan_id.
* try to find distinct DA_ID of the primary loan
* Here DA_ID is internal id
        DELETE ADJACENT DUPLICATES FROM lt_rel_loans
                         COMPARING sa_internal_id.
        IF lt_rel_loans IS NOT INITIAL.
          SELECT * FROM zbrf_benf_list
                   APPENDING CORRESPONDING FIELDS OF TABLE t_formdata
                FOR ALL ENTRIES IN lt_rel_loans
                        WHERE fin_id      = iv_fincr_id
                        AND   da_id       = lt_rel_loans-sa_internal_id
                        AND   delete_flag = ' '
*                        AND   benf_status NE  'INAC'
                        AND   benf_status IN  lt_benf_status.
        ENDIF.
        SORT t_formdata DESCENDING BY  brf_id .
      ELSE.
        CLEAR lt_ilap_benf_det.
        REFRESH lt_ilap_benf_det.
* Get all DA_ID of primary loan

        SELECT * FROM zbrf_benf_det
              INTO TABLE lt_ilap_benf_det
          FOR ALL ENTRIES IN lt_formdata
          WHERE  brf_id  NE lt_formdata-brf_id
             AND ver_id NE lt_formdata-ver_id
            AND fin_id = iv_fincr_id
            AND loan_no = iv_loan_id.

        IF sy-subrc = 0.
          SELECT * FROM zbrf_benf_list
              APPENDING CORRESPONDING FIELDS OF TABLE t_formdata
            FOR ALL ENTRIES IN lt_ilap_benf_det
                WHERE brf_id = lt_ilap_benf_det-brf_id
                AND   ver_id = lt_ilap_benf_det-ver_id
                AND   fin_id = iv_fincr_id
                AND   loan_no = iv_loan_id
                AND   delete_flag = ' '
                AND   benf_status IN  lt_benf_status.

* There should not be any duplicates
        ENDIF.

Former Member
In response to former_member194669

‎05-11-2010 8:32 AM

0 Kudos

I posted again using the braces

but again it failed

Regards,

Lisa

former_member194669
Active Contributor
In response to former_member194669

‎05-11-2010 8:33 AM

0 Kudos

In we have character limitation of code. so please split the code into 2 and paste..

a®

Former Member
In response to former_member194669

‎05-11-2010 8:37 AM

0 Kudos

Part 1 code


      SELECT * INTO CORRESPONDING FIELDS OF TABLE t_formdata
                 FROM  zbrf_benf_list
                 WHERE fin_id      = iv_fincr_id
                 AND   loan_no     = iv_loan_id
                 AND   delete_flag = ' '
*                 AND   benf_status NE  'INAC'.
                 AND   benf_status IN  lt_benf_status.
      IF sy-subrc <> 0.
        wa_return-type       = 'E'.
        wa_return-id         = 'ZPPM'.
        wa_return-number     = '000'.
        wa_return-message_v1 = 'Data not found.'.
        PERFORM fill_return_tab TABLES t_return
                                USING  wa_return .
      ELSE.
        APPEND LINES OF t_formdata    TO lt_formdata.

      ENDIF.
      IF iv_brf_type = 'DA'.


* Get all DA_ID of primary loan
        PERFORM get_ilap_related_loans TABLES lt_rel_loans
                                       USING  iv_brf_type
                                              iv_fincr_id
                                              iv_loan_id.
* try to find distinct DA_ID of the primary loan
* Here DA_ID is internal id
        DELETE ADJACENT DUPLICATES FROM lt_rel_loans
                         COMPARING sa_internal_id.
        IF lt_rel_loans IS NOT INITIAL.
          SELECT * FROM zbrf_benf_list
                   APPENDING CORRESPONDING FIELDS OF TABLE t_formdata
                FOR ALL ENTRIES IN lt_rel_loans
                        WHERE fin_id      = iv_fincr_id
                        AND   da_id       = lt_rel_loans-sa_internal_id
                        AND   delete_flag = ' '
*                        AND   benf_status NE  'INAC'
                        AND   benf_status IN  lt_benf_status.
        ENDIF.

Former Member
In response to former_member194669

‎05-11-2010 8:38 AM

0 Kudos

part2 code



        SORT t_formdata DESCENDING BY  brf_id .
      ELSE.
        CLEAR lt_ilap_benf_det.
        REFRESH lt_ilap_benf_det.
* Get all DA_ID of primary loan

        SELECT * FROM zbrf_benf_det
              INTO TABLE lt_ilap_benf_det
          FOR ALL ENTRIES IN lt_formdata
          WHERE  brf_id  NE lt_formdata-brf_id
             AND ver_id NE lt_formdata-ver_id
            AND fin_id = iv_fincr_id
            AND loan_no = iv_loan_id.

        IF sy-subrc = 0.
          SELECT * FROM zbrf_benf_list
              APPENDING CORRESPONDING FIELDS OF TABLE t_formdata
            FOR ALL ENTRIES IN lt_ilap_benf_det
                WHERE brf_id = lt_ilap_benf_det-brf_id
                AND   ver_id = lt_ilap_benf_det-ver_id
                AND   fin_id = iv_fincr_id
                AND   loan_no = iv_loan_id
                AND   delete_flag = ' '
                AND   benf_status IN  lt_benf_status.

* There should not be any duplicates
        ENDIF.

Former Member
In response to former_member194669

‎05-11-2010 8:39 AM

0 Kudos

I have problem in this select. I am getting the same data 


        SELECT * FROM zbrf_benf_det
              INTO TABLE lt_ilap_benf_det
          FOR ALL ENTRIES IN lt_formdata
          WHERE  brf_id  NE lt_formdata-brf_id
             AND ver_id NE lt_formdata-ver_id
            AND fin_id = iv_fincr_id
            AND loan_no = iv_loan_id.

former_member194669
Active Contributor
In response to former_member194669

‎05-11-2010 8:44 AM

0 Kudos

Try to change this way. I think the IT_FORMDATA is coming as blank 


if NOT lt_formdata[] is initial. "<===
SELECT * FROM zbrf_benf_det
              INTO TABLE lt_ilap_benf_det
          FOR ALL ENTRIES IN lt_formdata
          WHERE  brf_id  NE lt_formdata-brf_id
             AND ver_id NE lt_formdata-ver_id
            AND fin_id = iv_fincr_id
            AND loan_no = iv_loan_id.
endif.    "<====

a®

Former Member
In response to former_member194669

‎05-11-2010 9:14 AM

0 Kudos

Hi,

I have checked the code it_formdata is not coming blank

LEts say t_formdata has 1,2 and 3

I am checking in my select rather than 1,2,3 is there are any records exist or not.

If exist i should display that data. But select is getting all record 1,2,3 again

Regards,

Lisa

Former Member
In response to former_member194669

‎05-11-2010 10:03 AM

0 Kudos

Hi Lisa,

Using NE with for all entries will not give proper results.

Let us see this logic with example:

Say suppose lt_formdata is having 3 records with value in brf_id column as 1,2&3.

Now if you can diagnose your select query for the first value in brf_id, your query will fetch records which are having brf_id

as 2 & 3 and same thing will repeat for other records of lt_formdata .

So remove that NE conditions in select query and fetch the data.

Once you get the data further filteration should happen at program level.

Regards,

Shafivullah Mohammad