SELECT Statement

[Former Member]

Hello everyone,

I am trying to select some data from a database table

depending upon the option the user chooses from the

selection screen. There are three possiblities:

e.g.

SELECT *

INTO CORRESPONDING FIELDS OF TABLE tsp01_tab

UP TO 20 ROWS

FROM tsp01

WHERE

• Credit Notes / Invoices

(

rq0name = wa_rq0name

AND rq1name = wa_rq1name

AND rq2name = wa_rq2name

AND rqcretime GE wa_start

AND rqcretime LE wa_end

AND <b>select_option</b> = '1'

)

OR

*

• Customer Statements

(

rq0name = wa_rq0name

AND rqcretime GE wa_start

AND rqcretime LE wa_end

AND <b>select_option</b> = '3'

)

OR

*

• Reminders

(

rq0name = wa_rq0name

AND rq2name LIKE 'DUN%'

AND rqcretime GE wa_start

AND rqcretime LE wa_end

AND <b>select_option</b> = '3'

).

From the above you can see that the <b>select_option</b>

is the screen parameter. However, I can't activate the

code because the checker says 'Field select_option unknown'.

Does anybody know how I can get around this??

[Former Member]

u have to pass the field of that selec_option.

select screen

select-options: s_budat for sy-datum.

<b>select * from vbrk into corresponding fields of table xvbrk

where fkdat in s_budat.</b>

Regards

Prabhu

[Former Member]

Instead of writing such a complex select u cud write seperate selects based on select_option as below:

IF select_option = 1.

1st select in to table ITAB.

Endif.

IF select_option = 2.

2nd select appending records in table ITAB.

ENDIF

IF select_option = 2.

3rd select appending records in table ITAB.

Endif.

-Kiran

[RichHeilman]

Since select_option is not in your table, you can not use it like that. You must break your select statement into 3 separate select statements and wrapper with an IF statement.

if select_option  = 1.

SELECT *
INTO CORRESPONDING FIELDS OF TABLE tsp01_tab
UP TO 20 ROWS
FROM tsp01
WHERE
* Credit Notes / Invoices
(
rq0name = wa_rq0name
AND rq1name = wa_rq1name
AND rq2name = wa_rq2name
AND rqcretime GE wa_start
AND rqcretime LE wa_end.


endif.


etc.
etc.

Or you may want to explore a dynamic WHERE clause based on your SELECT_OPTION field.

Regards,

Rich Heilman

[RichHeilman]

Here is an example of using a dynamic WHERE clause.

report zrich_0001.

parameters: selopt type c.
data: tsp01_tab type table of tsp01 with header line.
data: where type table of string with header line.


case selopt  .
  when '1'.
    clear where. refresh where.
    where = 'rq0name = wa_rq0name'. append where.
    where = 'and rq1name = wa_rq1name'. append where.
    where = 'and rq2name = wa_rq2name'. append where.
    where = 'and rqcretime ge wa_start'.   append where.
    where = 'and rqcretime le wa_end'.   append where.
  when '2'.
    clear where. refresh where.
    where = 'rq0name = wa_rq0name'. append where.
    where = 'and rqcretime ge wa_start'.   append where.
    where = 'and rqcretime le wa_end'.   append where.

  when '3'.
   clear where. refresh where.
    where = 'rq0name = wa_rq0name'. append where.
    where = 'and rq2name = wa_rq2name'. append where.
    where = 'and rqcretime ge wa_start'.   append where.
    where = 'and rqcretime le wa_end'.   append where.

endcase.


select * into corresponding fields of table tsp01_tab
      up to 20 rows
           from tsp01
                where (where).

Regards,

Rich Heilman

[Former Member]

Thanks Guys for all your replies.

Rich, many thanks for the suggestion I was looking

for. I had done something in ORACLE but didn't know

how to do it in ABAP.

Thanks again

Andy

[Former Member]

You cannot specify the name of a select option in the where clause of a select statement. The compiler assumes that to be a field name and checks for the same in the table. Hence the error.

Ideally, your code should be:

SELECT *

INTO CORRESPONDING FIELDS OF TABLE tsp01_tab

UP TO 20 ROWS

FROM tsp01

WHERE

• Credit Notes / Invoices

(

rq0name = wa_rq0name

AND rq1name = wa_rq1name

AND rq2name = wa_rq2name

AND rqcretime GE wa_start

AND rqcretime LE wa_end

AND <b>fieldname = select_option</b>

)

Are you looking to execute the select statement based on the parameter value? YOu should try this:

IF select_option = '1'.

SELECT......

ELSEIF select_option = '2'.

....

ENDIF.

Hope this helps.

Sudha

[Former Member]

Hi,

You should declare and use the select-options like this,

select-options: s_date for sy-datum.

select * from "XXX' in to .... where date in S_DATE.

Regs,

Venkat Ramanan

[Former Member]

Andy,

In Spool table there is no field called select_option. I guess you are validating againist the Output device. if so the field is <b>RQDEST</b>. ie your statment will be

.....AND rqcretime LE wa_end
AND RQDEST = '1'

rgds,

TM.

[former_member181962]

if select_option =1.

SELECT *

INTO CORRESPONDING FIELDS OF TABLE tsp01_tab

UP TO 20 ROWS

FROM tsp01

WHERE

• Credit Notes / Invoices

rq0name = wa_rq0name

AND rq1name = wa_rq1name

AND rq2name = wa_rq2name

AND rqcretime GE wa_start

AND rqcretime LE wa_end

.

endif.

if select_option = '2'.

SELECT *

INTO CORRESPONDING FIELDS OF TABLE tsp01_tab

UP TO 20 ROWS

FROM tsp01

WHERE

*

• Customer Statements

rq0name = wa_rq0name

AND rqcretime GE wa_start

AND rqcretime LE wa_end

.

if select_optios = '3'.

SELECT *

INTO CORRESPONDING FIELDS OF TABLE tsp01_tab

UP TO 20 ROWS

FROM tsp01

WHERE

• Reminders

rq0name = wa_rq0name

AND rq2name LIKE 'DUN%'

AND rqcretime GE wa_start

AND rqcretime LE wa_end

.

endif.

Make the above changes.

Regards,

Ravi