slect query for multiple fields on selection screen

[Former Member]

Dear Gurus,

I'm new to ABAP and i'm writing a select query to retrieve entries from a Z table ZPAMOS. The selection screen looks like the below and all the fields are part of the Z table except last one.

ZHAP1 and ZHAP2 are parameters. (P_ZHAP1 and P_ZHAP2)

ZHAP3 and ZHAP4 are select options. (S_ZHAP3 and S_ZHAP4)

ZHAP6 and ZHAP7 are check boxes (which will have X or Space in table values) (C_ZHAP6 and C_ZHAP7)

ZHAP9 is the count of the records to be retrieved from the table (user can enter how many records he wants to retrieve from table).

I need to write a select statement which will get data based out of the selection criteria.

Please let me know how can I write single select statement or best way to write the select statement.

Of course, best option will get rewards

Thank you,

Kishore.

[former_member219762]

Hi kishore,

Select < fields to retrieve > from ztable  where

ztable-<zhap1 field> = zhap1 and

ztable-<zhap2 field> = zhap2 and

ztable-<zhap3 field>  in zhap3 and

ztable-<zhap5 field> in zhap5 and

ztable-<zhap6field> = zhap6 and

ztable-<zhap7 = zhap7 up to zhap9 rows

Regards,

Sreenivas.

[former_member209120]

Hi Sreenivasa Rao Yamu,

Your Code Should be like this

Select < fields to retrieve > from ztable  where

into table it_ztable upto to zhap9 rows

ztable-<zhap1 field> = zhap1 and

ztable-<zhap2 field> = zhap2 and

ztable-<zhap3 field>  in zhap3 and

ztable-<zhap5 field> in zhap5 and

ztable-<zhap6field> = zhap6 and

ztable-<zhap7 = zhap7. up to zhap9 rows

[Former Member]

SELECT* FROM ZPAMOS

             INTO TABLE itab

            WHERE field1  = zhap1 AND

                          field2  = zhap2 AND

                          field3 IN zhap3 AND

                          field5 IN zhap5 AND

                          field6  =  zhap6 AND

                          field7  = zhap7

             UP TO zaph9 ROWS.

[former_member209120]

Hi Farid Hasan,

Your code shows error like this

[Former Member]

Hi Kishore,

SELECT  field1 field2 ...... (if all fields from zpamos table is required then SELECT * )

     INTO TABLE it_tab

          FROM zpamos

               WHERE  zhap1 = p_zhap1

                    AND   zhap2 = p_zhap2

                    AND  zhap3 in s_zhap3

                    AND  zhap4 in s_zhap4

                    AND  zhap6 = c_zhap6

                    AND  zhap7 = c_zhap7

                    UPTO zhap9 ROWS.

declare the internal table it_tab of type zpamos if all the fields are to be selected.

hope this helps

[former_member209120]

Hi Anju E.V

Your Code gives error like this

it should be like this

[former_member209120]

Hi Kishore B

Try like this

TYPES : BEGIN OF ty_zpamos,
         field1 TYPE zpamos-filed1,
         field2 TYPE zpamos-filed2,
         field3 TYPE zpamos-filed3,
         END OF ty_zpamos.

DATA : it_zpamos TYPE TABLE OF ty_zpamos.

SELECT filed1
        field2
        field3
        FROM zpamos
        INTO TABLE it_zpamos UP TO zhap9 ROWS
        WHERE zhap1 = p_zhap1
        AND   zhap2 = p_zhap2
        AND   zhap3 IN s_zhap3
        AND   zhap4 IN s_zhap4
        AND   zhap5 = c_zhap5
        AND   zhap6 = c_zhap6.

[Former Member]

Thanks alot for the answers. I have another issue in this. I user doesn't enter  ZHAP9 and ZHAP1 my program should pick all the records for which the criteria is not mentioned. In a case if there are 5 records in table and If User passes ZHAP1 = A, then it should fetch only one record. If he doesn't pass any thing it should retrieve all the records.

Is there a possibility in restricting with one single query? any one can suggest if it is possible with Field symbols?

Thanks,

Kishore

[Former Member]

Dear Kishore

if ZAHP6 is not initial.

SELECT * FROM ZPAMOS

              INTO TABLE itab UP TO zaph9 ROWS

              WHERE field1  = zhap1 AND

                          field2  = zhap2 AND

                          field3 IN zhap3 AND

                          field5 IN zhap5 AND

                          field6  =  zhap6  AND

                          field7  = zhap7

             .

  else.

SELECT * FROM ZPAMOS

              INTO TABLE itab

              WHERE field1  = zhap1 AND

                          field2  = zhap2 AND

                          field3 IN zhap3 AND

                          field5 IN zhap5 AND

                                                 field6  =  zhap6  AND

                          field7  = zhap7

endif.

[nabheetscn]

Change your parameters also to select option with no-extension no intervals option. then use in clause everywhere. For number of records you can do as mentioned above.

[Former Member]

Thanks alot for the idea. In case I donot want to restrict this in the select query but want to check when I'm processing internal table, how can i do that? Sorry to ask some basic questions but I'm eager to reduce the lines of code on select queries.

[nabheetscn]

its not a good idea but you can use loop with to option but it can be an issue if to value is blank