topic Re: Select statement not working properly what is the problem in below code in Application Development and Automation Discussions

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

Former Member — Tue, 11 May 2010 07:19:31 GMT

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.

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

former_member194669 — Tue, 11 May 2010 07:25:03 GMT

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

a®

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

Former Member — Tue, 11 May 2010 07:30:05 GMT

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

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

Former Member — Tue, 11 May 2010 07:31:09 GMT

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.

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

Former Member — Tue, 11 May 2010 07:32:47 GMT

I posted again using the braces

but again it failed

Regards,

Lisa

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

former_member194669 — Tue, 11 May 2010 07:33:51 GMT

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

a®

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

Former Member — Tue, 11 May 2010 07:37:46 GMT

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.

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

Former Member — Tue, 11 May 2010 07:38:37 GMT

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.

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

Former Member — Tue, 11 May 2010 07:39:55 GMT

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.

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

former_member194669 — Tue, 11 May 2010 07:44:39 GMT

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®

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

Former Member — Tue, 11 May 2010 08:14:04 GMT

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

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

Former Member — Tue, 11 May 2010 09:03:14 GMT

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