topic Re: select problem in Application Development and Automation Discussions

select problem

Former Member — Tue, 12 Sep 2006 10:14:03 GMT

Hello

I have a screen with two text fields. And i am using the value of this two fields in my query. Now if no value is provided by the user, the value is set to '*' in PAI i.e i want to display all its fields. How should i write the query.. Btw following query doesnt work. i dont want to use case or if.

select * from <table> where field1 = *

Re: select problem

anversha_s — Tue, 12 Sep 2006 10:18:22 GMT

is it a report program ?

if so u have to use select options.

in select options u can restrict to one field in screen by giving NO intervels.

the query will be like this,

select * from <table> where field1 in p_para1 and field2 in p_para2.

if no valules there , all the data is selected

Re: select problem

Former Member — Tue, 12 Sep 2006 10:18:53 GMT

Hello Puru,

Built a range table and in the select query use IN RANGE TABLE.

Hope this will solve ur problem.

reward if helps.

Vasanth

Re: select problem

Former Member — Tue, 12 Sep 2006 10:21:27 GMT

There are two ways to resolve it...

1. Define range for the particular field...and in your PAI keep the low value blank and use the below sql.

SELECT * from table where field1 in r_field1.

2. U keep it as '*' or '%' and use the like statement.

SELECT * from table where field1 like '*'

Re: select problem

Former Member — Tue, 12 Sep 2006 10:22:00 GMT

Define a range:

Ranges: r_field for <table>.

if not field1 is initial.

r_field-sign = 'I'.

r_field-option = 'EQ'.

r_field-option-low = field1.

append r_field.

endif.

if not field2 is initial.

r_field-sign = 'I'.

r_field-option = 'EQ'.

r_field-option-low = field2.

append r_field.

endif.

if field1 is initial and field2 is initial.

r_field-sign = 'I'.

r_field-option = 'EQ'.

append r_field.

endif.

select * from <table> where field1 in r_field.

Regards,

Prakash.

Re: select problem

Former Member — Tue, 12 Sep 2006 10:22:35 GMT

hi,

You can make use of a dynamic where condition in select.

types : begin of t_mara,
         matnr like mara-matnr,
        end of t_mara.

data: it_mara type table of t_mara with header line.

data: v_where type string.

parameters : p_matnr like mara-matnr.

if not p_matnr is initial.
  v_where = 'matnr = p_matnr '.
else.
  v_where = ' '.
endif.
select matnr from mara into table it_mara where (v_where).
if sy-subrc = 0.
  write :/ ' got it '.
endif.

Regards,

Sailaja.

Re: select problem

Former Member — Tue, 12 Sep 2006 10:49:46 GMT

Hi,

Use FM NAMETAB_GET to get key and non key fields an

build dynamic table

CALL FUNCTION 'NAMETAB_GET'

EXPORTING

LANGU = SY-LANGU

TABNAME = DBTABPRT-TABNAME

TABLES

NAMETAB = NT

EXCEPTIONS

NO_TEXTS_FOUND = 01

TABLES_HAS_NO_FIELDS = 02

TABLE_NOT_ACTIV = 03.

DATA: LV_LEN TYPE I,

LV_OFF TYPE I,

TNAME LIKE DBTABPRT-TABNAME.

Field symbols

FIELD-SYMBOLS <FS>.

REFRESH:WTAB,

NT1.

CLEAR: GV_COUNT,

WTAB,

NT1.

IF DBTABPRT-OPTYPE = 'U'.

TNAME = DBTABPRT-TABNAME.

LOOP AT NT WHERE KEYFLAG = 'X'.

LV_LEN = NT-DDLEN.

LV_OFF = NT-OFFSET.

GV_COUNT = GV_COUNT + 1.

Assign value to field symbol

ASSIGN DBTABPRT-VKEY+LV_OFF(LV_LEN) TO <FS>.

One primary index

IF GV_COUNT = 1.

CONCATENATE NT-FIELDNAME ' = '''<FS>'''' '.'

INTO WTAB .

APPEND WTAB.

CLEAR WTAB.

ENDIF.

More than One primary index

IF GV_COUNT > 1.

CONCATENATE 'AN' 'D' INTO WTAB.

APPEND WTAB.

CLEAR WTAB.

CONCATENATE NT-FIELDNAME ' = '''<FS>'''' '.'

INTO WTAB.

APPEND WTAB.

CLEAR WTAB.

ENDIF.

ENDLOOP.

SELECT * FROM (TNAME)

INTO TABLE

NT1 WHERE (WTAB).

endselect.

ENDIF.

Regards

Amole