topic SELECT Statement in Application Development and Automation Discussions

SELECT Statement

Former Member — Tue, 04 Jul 2006 12:40:24 GMT

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 select_option = '1'

)

Customer Statements

(

rq0name = wa_rq0name

AND rqcretime GE wa_start

AND rqcretime LE wa_end

AND select_option = '3'

)

Reminders

(

rq0name = wa_rq0name

AND rq2name LIKE 'DUN%'

AND rqcretime GE wa_start

AND rqcretime LE wa_end

AND select_option = '3'

From the above you can see that the select_option

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??

Re: SELECT Statement

Former Member — Tue, 04 Jul 2006 12:44:03 GMT

u have to pass the field of that selec_option.

select screen

select-options: s_budat for sy-datum.

select * from vbrk into corresponding fields of table xvbrk

where fkdat in s_budat.

Regards

Prabhu

Re: SELECT Statement

Former Member — Tue, 04 Jul 2006 12:44:18 GMT

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

Re: SELECT Statement

RichHeilman — Tue, 04 Jul 2006 12:44:20 GMT

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

Re: SELECT Statement

Former Member — Tue, 04 Jul 2006 12:45:08 GMT

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 fieldname = select_option

)

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

Re: SELECT Statement

Former Member — Tue, 04 Jul 2006 12:45:35 GMT

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

Re: SELECT Statement

Former Member — Tue, 04 Jul 2006 12:45:47 GMT

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 RQDEST. ie your statment will be

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

rgds,

TM.

Re: SELECT Statement

Former Member — Tue, 04 Jul 2006 12:46:38 GMT

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

Re: SELECT Statement

RichHeilman — Tue, 04 Jul 2006 12:51:47 GMT

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

Re: SELECT Statement

Former Member — Tue, 04 Jul 2006 13:05:27 GMT

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