topic Re: Loop Range table to Build SQL Statement in Application Development and Automation Discussions

Loop Range table to Build SQL Statement

Former Member — Mon, 27 Feb 2006 23:13:11 GMT

I would like to loop Through a RANGES type table to build a SQL Statement something like this.

Data: SQLSTR type C.

Loop at LS_Select into WA_LS_select.

SQLSTR = SQLSTR + WA_LS_select-Fieldname in WA_LS_select.

endloop

Select * from TABLEX where ( SQLSTR )

Is this possible if so could someone please give me a little sample code. Additionally it is possible that in my ranges table I could have multiple items for the same field name..

Re: Loop Range table to Build SQL Statement

suresh_datti — Mon, 27 Feb 2006 23:18:22 GMT

Hi Hubert,

try changing the loop as follows..


assuming ls_select is your RANGES
Loop at LS_Select.
concatenate SQLSTR LS_select-low
 into SQLSTR.
endloop

Regards,

Suresh Datti

Re: Loop Range table to Build SQL Statement

Former Member — Mon, 27 Feb 2006 23:45:18 GMT

I did not understand your requirement but here is one suggestion - my assumption is you do not want to use select option directly in code.

Suppose I have S_MATNR as select optopn.

First get all the Materials for this S_MATNR from MARA table into another internal table.

Then you can loop at this table and select your code

LOOP AT I_MARA.

SELECT DATA FROM TABLE WHERE MATNR = I_MARA-MATNR.

ENDLOOP.

Else you can also use FOR ALL ENTRIES option.

Re: Loop Range table to Build SQL Statement

former_member186741 — Tue, 28 Feb 2006 01:25:52 GMT

aSSUMING THE TABLE AND FIELDNAME IS CONSTANT:

DATA l_sql TYPE string.

loop at r_range.

if sy-tabix = 1.

concatenate 'table-fieldname = ' r_range-low

into l_sql separated by space.

else.

concatenate l_sql 'or table-fieldname = ' r_range-low

into l_sql separated by space.

endif.

endloop.

Select * from TABLEX where (l_sql).

.........

If you want to use the HIGH and LOW of the range you could modify it slightly to use

concatenate 'table-fieldname between ' r_range-low

'and' r_range-high into l_sql separated by space.

Re: Loop Range table to Build SQL Statement

Former Member — Tue, 28 Feb 2006 03:53:01 GMT

This would be pretty difficult. You will have to handle things like NE, CP, EXCLUDE. Why not just use the range table?

Rob

Re: Loop Range table to Build SQL Statement

Former Member — Tue, 28 Feb 2006 12:40:53 GMT

Rob,

This is exactly what I would like to do is use the Range table. My table contains data like this.

fieldname~~-sign~~-option~~-low~~-high

zip-I-eq---30152

zip-I-bt~~-30111~~-30113

yeare-lt---2005

etc etc....

What I want to do is loop through this table to build a sql statement.

Is this possible?

Re: Loop Range table to Build SQL Statement

Former Member — Tue, 28 Feb 2006 12:51:15 GMT

Hi,

U can write a select like this.

select <your fields> from <table> into table <IT>

where

zip_field in zip (This is the range in your table)

and year in year(year in your range table)

Regards,

GSR.

Re: Loop Range table to Build SQL Statement

Former Member — Tue, 28 Feb 2006 12:53:43 GMT

Pls.look into the below code..may it useful for u

loop <rangestable> into <wa_range>

select <f1> <f2> from <db> into table <itab>

where <field> in <wa_range>.

clear <wa_range>.

endloop.

Re: Loop Range table to Build SQL Statement

Former Member — Tue, 28 Feb 2006 12:55:18 GMT

Hi Hubert

yes you can loop through this table then build the select statement.

loop at itab_range.

use conditional statements then hard code the required select statement for ex.

if itab_range-fieldname eq 'zip' and itab_range-option eq 'eq'.

select * from zemp where zip bt itab_range-low and itab_range-high.

endif.

regards

kishore

Re: Loop Range table to Build SQL Statement

Former Member — Tue, 28 Feb 2006 13:02:28 GMT

Eswar,

You seem to be the closest to what I want, but would that not create multiple select statements for each field?

Re: Loop Range table to Build SQL Statement

Former Member — Tue, 28 Feb 2006 13:07:20 GMT

hope in this case it will create multiple statements bcoz we will get only one row at a time to check in select statement.

or else u can create separate range for each field to check in a single select statement (Performance)

Re: Loop Range table to Build SQL Statement

Former Member — Tue, 28 Feb 2006 13:12:05 GMT

I was considering making a range table for each possible entry, but this could endup being a little over 80.

Then I would have to also include every on in the select statement. I was really hoping to avoid this since, most of the time I expect the user to only select 10 to 20 variables.

Is it possible to convert a range table into a string, maybe a function module or something? what does it do at run time?

Re: Loop Range table to Build SQL Statement

Former Member — Tue, 28 Feb 2006 15:10:31 GMT

Well, I was suggesting that you just use all of the range tables as is. I'm sure you can build the logic to do what you want, but it will be difficult and time consuming. In the end it probably will be just as long and no more efficient.

rob

Re: Loop Range table to Build SQL Statement

former_member186741 — Mon, 06 Mar 2006 02:42:45 GMT

I have coded this and it works, but I have to wonder why you would ever want to do it this way given that the sql can just refer directly to the ranges (or select-options in this example).

I had a program which had a few select options within it's sql, I changed it to dynamically generate references to the select options instead of hard-coding the 'in' clauses in the sql:

DATA l_sql TYPE string.

SELECT-OPTIONS S_bLART for bSID-bLART.

SELECT-OPTIONS s_BRAN1 for KNA1-BRAN1.

SELECT-OPTIONS s_REGIO for KNA1-REGIO.

define append_sql.

if l_sql is initial.

concatenate &1 'in' &2 into l_sql separated by space.

else.

concatenate L_SQL 'AND' &1 'IN' &2 into l_sql separated by space.

ENDIF.

END-OF-DEFINITION.

APPEND_SQL 'BSID~BLART' 'S_BLART'.

APPEND_SQL 'KNA1~BRAN1' 'S_BRAN1'.

APPEND_SQL 'KNA1~REGIO' 'S_REGIO'.

SELECT bsid_{bukrs bsid}kunnr bsid~belnr

kna1_{regio kna1}bran1 kna1~name1

FROM bsid

JOIN vbrk ON vbrk_{vbeln = bsid}vbeln

JOIN knvv ON knvv_{kunnr = vbrk}kunag

AND knvv_{vkorg = vbrk}vkorg

AND knvv_{vtweg = vbrk}vtweg

AND knvv_{spart = vbrk}spart

JOIN kna1 ON kna1_{kunnr = knvv}kunnr

INTO CORRESPONDING FIELDS OF TABLE T_BSID

WHERE bsid~bukrs = p_bukrs

AND bsid~kunnr IN s_kunnr

AND bsid~budat <= w_key_date

AND bsid~blart IN s_blart "now in l_sql
AND kna1~bran1 IN s_bran1 "
AND kna1~regio IN s_regio "

AND (l_sql)

Re: Loop Range table to Build SQL Statement

Former Member — Mon, 06 Mar 2006 03:20:07 GMT

Hi Hubert,

You must build your SQL statement dynamically like this...

<b>DATA sqlstr(100).</b>

LOOP AT ls_select INTO wa_ls_select.
 IF sqlstr IS INITIAL.
   IF wa_ls_select-option EQ 'eq' AND wa_ls_select-sign EQ 'I'.
     CONCATENATE wa_ls_select-fieldname 'EQ' wa_ls_select-low
        INTO sqlstr
        SEPARATED BY space.
   ENDIF.
  IF wa_ls_select-option EQ 'eq' AND wa_ls_select-sign EQ 'e'.
     CONCATENATE wa_ls_select-fieldname 'NE' wa_ls_select-low
        INTO sqlstr
        SEPARATED BY space.
   ENDIF.
  IF wa_ls_select-option EQ 'bt' AND wa_ls_select-sign EQ 'I'.
     CONCATENATE wa_ls_select-fieldname 'BETWEEN' wa_ls_select-low
    'AND' wa_ls_select-high
     INTO sqlstr
     SEPARATED BY space.
   ENDIF.
   IF wa_ls_select-option EQ 'lt' AND wa_ls_select-sign EQ 'I'.
     CONCATENATE wa_ls_select-fieldname 'LE' wa_ls_select-low
        INTO sqlstr
        SEPARATED BY space.
   ENDIF.
   IF wa_ls_select-option EQ 'lt' AND wa_ls_select-sign EQ 'e'.
     CONCATENATE wa_ls_select-fieldname 'LT' wa_ls_select-low
        INTO sqlstr
        SEPARATED BY space.
   ENDIF.
   IF wa_ls_select-option EQ 'gt' AND wa_ls_select-sign EQ 'I'.
     CONCATENATE wa_ls_select-fieldname 'GE' wa_ls_select-low
        INTO sqlstr
        SEPARATED BY space.
   ENDIF.
   IF wa_ls_select-option EQ 'gt' AND wa_ls_select-sign EQ 'e'.
     CONCATENATE wa_ls_select-fieldname 'GT' wa_ls_select-low
        INTO sqlstr
        SEPARATED BY space.
   ENDIF.
ELSE.
   CONCATENATE sqlstr 'AND' INTO sqlstr SEPARATED BY space.
   IF wa_ls_select-option EQ 'eq' AND wa_ls_select-sign EQ 'I'.
     CONCATENATE wa_ls_select-fieldname 'EQ' wa_ls_select-low
        INTO sqlstr
        SEPARATED BY space.
   ENDIF.
  IF wa_ls_select-option EQ 'eq' AND wa_ls_select-sign EQ 'e'.
     CONCATENATE wa_ls_select-fieldname 'NE' wa_ls_select-low
        INTO sqlstr
        SEPARATED BY space.
   ENDIF.
  IF wa_ls_select-option EQ 'bt' AND wa_ls_select-sign EQ 'I'.
     CONCATENATE wa_ls_select-fieldname 'BETWEEN' wa_ls_select-low
    'AND' wa_ls_select-high
     INTO sqlstr
     SEPARATED BY space.
   ENDIF.
   IF wa_ls_select-option EQ 'lt' AND wa_ls_select-sign EQ 'I'.
     CONCATENATE wa_ls_select-fieldname 'LE' wa_ls_select-low
        INTO sqlstr
        SEPARATED BY space.
   ENDIF.
   IF wa_ls_select-option EQ 'lt' AND wa_ls_select-sign EQ 'e'.
     CONCATENATE wa_ls_select-fieldname 'LT' wa_ls_select-low
        INTO sqlstr
        SEPARATED BY space.
   ENDIF.
   IF wa_ls_select-option EQ 'gt' AND wa_ls_select-sign EQ 'I'.
     CONCATENATE wa_ls_select-fieldname 'GE' wa_ls_select-low
        INTO sqlstr
        SEPARATED BY space.
   ENDIF.
   IF wa_ls_select-option EQ 'gt' AND wa_ls_select-sign EQ 'e'.
     CONCATENATE wa_ls_select-fieldname 'GT' wa_ls_select-low
        INTO sqlstr
        SEPARATED BY space.
   ENDIF.
 ENDIF.
ENDLOOP.


Select * from TABLEX where ( SQLSTR ).

Re: Loop Range table to Build SQL Statement

former_member186741 — Mon, 06 Mar 2006 03:36:58 GMT

Hi Wenceslaus, what do you mean by "MUST" build the sql like this?

Re: Loop Range table to Build SQL Statement

Former Member — Mon, 06 Mar 2006 03:42:36 GMT

Hi Neil,

Since the requirement was to build sql from a table where he had not used RANGES:

fieldname~~-sign~~-option~~-low~~-high

zip-I-eq---30152

zip-I-bt~~-30111~~-30113

yeare-lt---2005

Then I think the possible way for this is that he could parse the contents of the table and build SQL query. Still there could be alternative ways of doing this...

Regards,

Wenceslaus.

Re: Loop Range table to Build SQL Statement

former_member186741 — Mon, 06 Mar 2006 04:27:43 GMT

maybe I've misunderstood, but Hubert specifically said he wants to use a RANGES type table.

Re: Loop Range table to Build SQL Statement

Former Member — Mon, 06 Mar 2006 05:32:51 GMT

Hi,

Here is the code if it for ranges table.

REPORT  ZEN_COV.

TABLES: mara.

SELECT-OPTIONS: ls_sel FOR mara-matnr.

DATA: t TYPE i.

DATA datstr(2000).
DATA tmpstr(2000).

LOOP AT ls_sel.
  IF datstr IS NOT INITIAL.
    CONCATENATE datstr 'AND' INTO datstr SEPARATED BY space.
    tmpstr = datstr.
  ENDIF.

  IF ls_sel-option EQ 'EQ' AND ls_sel-sign EQ 'I'.
    CONCATENATE tmpstr 'mara~matnr' 'EQ' ls_sel-low
    INTO datstr SEPARATED BY space.
  ENDIF.
  IF ls_sel-option EQ 'BT' AND ls_sel-sign EQ 'I'.
    CONCATENATE tmpstr 'mara~matnr' 'BETWEEN' ls_sel-low
    'AND' ls_sel-high INTO datstr SEPARATED BY space.
  ENDIF.
  IF ls_sel-option EQ 'EQ' AND ls_sel-sign EQ 'E'.
    CONCATENATE tmpstr 'mara~matnr' 'NE' ls_sel-low
    INTO datstr SEPARATED BY space.
  ENDIF.
  IF ls_sel-option EQ 'LT' AND ls_sel-sign EQ 'I'.
    CONCATENATE 'mara~matnr' 'LE' ls_sel-low
    INTO datstr SEPARATED BY space.
  ENDIF.
  IF ls_sel-option EQ 'LT' AND ls_sel-sign EQ 'E'.
    CONCATENATE tmpstr 'mara~matnr' 'LT' ls_sel-low
    INTO datstr SEPARATED BY space.
  ENDIF.
  IF ls_sel-option EQ 'GT' AND ls_sel-sign EQ 'I'.
    CONCATENATE tmpstr 'mara~matnr' 'GE' ls_sel-low
    INTO datstr SEPARATED BY space.
  ENDIF.
  IF ls_sel-option EQ 'GT' AND ls_sel-sign EQ 'E'.
    CONCATENATE tmpstr 'mara~matnr' 'GT' ls_sel-low
    INTO datstr SEPARATED BY space.
  ENDIF.

ENDLOOP.

SELECT * FROM mara WHERE (datstr).
ENDSELECT.

regards

austin

Re: Loop Range table to Build SQL Statement

Former Member — Mon, 06 Mar 2006 14:54:46 GMT

Ok. I finaly solved it myself.

All the ideas above were great but, I was really looking for something that was a litte more standard SAP, rather than custom built from scratch.

SAP has a function module that you can pass a range table too, and it basically gves you back the where statement really cool.

-- Take a look at FREE_SELECTIONS_RANGE_2_WHERE

Thanks again for all your help...