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

[Former Member]

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.

[former_member194669]

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

a®

[Former Member]

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]

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]

I posted again using the braces

but again it failed

Regards,

Lisa

[former_member194669]

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

a®

[Former Member]

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]

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]

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]

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]

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]

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