topic Re: Dynamic From statement in select query and/or outer join not working in Application Development and Automation Discussions

Dynamic From statement in select query and/or outer join not working

Former Member — Wed, 24 Mar 2010 14:02:02 GMT

Dear Experts, I have a select query where the select columns are dynamic, the where condition is also dynamic. It is of the below format:

Select (dynamic columns) INTO <wa>

FROM a inner join b on a_{f1 = b}f1

inner join c on a_{f2 = c}f2......

WHERE (dynamic conditios)

ORDER BY ( dynamic sort condition).

Now I have to include some tables (dynamically depending on the user input) in the inner join statement which will give description for the selected fields. And these database tables may or may no be empty. So in this case, my select query will not return any data if these tables are empty. And I dont want that.

I tried using outer join for the extra tables but it gave me a runtime error. I also tried forming the inner join statement dynamically but it was not supporting.

Kindly give me pointers.

Thanks

Re: Dynamic From statement in select query and/or outer join not working

Former Member — Wed, 24 Mar 2010 14:32:39 GMT

For where condition use this


DATA: BEGIN OF it_selopt OCCURS 0,
        field      TYPE string,
        value      TYPE string,
      END OF it_selopt,
      wa_selopt LIKE LINE OF it_selopt.
 
DATA cond_syntax TYPE string.
 
                ............
 
it_selopt-field = 'MATNR'.
it_selopt-value = ''Hello''.
APPEND it_selopt.
 
 
LOOP AT it_selopt.
  wa_selopt = it_selopt.
 
* First condition
  AT FIRST.
       CONCATENATE wa_selopt-field '=' wa_selopt-value
         INTO cond_syntax SEPARATED BY space.
    CONTINUE.  "
  ENDAT.
* Other conditions  
       CONCATENATE cond_syntax 'AND' wa_selopt-field '=' wa_selopt-value
            INTO cond_syntax SEPARATED BY space.
ENDLOOP.
 
 
TRY. 
    SELECT SINGLE * 
           FROM eban 
           WHERE (cond_syntax). 
 
  CATCH cx_sy_dynamic_osql_error. 
    MESSAGE `Wrong WHERE condition!` TYPE 'I'. 
ENDTRY.

Marco

Re: Dynamic From statement in select query and/or outer join not working

Former Member — Wed, 24 Mar 2010 14:42:49 GMT

Hey thanks for the reply, but the problem is not solved.

I am already using ( fileds, value) like table in my where condition and the select statement was working properly.

the problem is that now I have to include some tables in the join statement which can be empty and so i want to use Outer join.

But I am getting a runtime error as below:

Error analysis

An exception occurred that is explained in detail below.

The exception, which is assigned to class 'CX_SY_DYNAMIC_OSQL_SYNTAX', was not

caught in

procedure "ZATSCSNG_RFC_READ_TABLE" "(FUNCTION)", nor was it propagated by a

RAISING clause.

Since the caller of the procedure could not have anticipated that the

exception would occur, the current program is terminated.

The reason for the exception is:

The running ABAP program wanted to execute a SELECT statement whose

WHERE condition was (partly) specified dynamically. The part that is

specified in an internal table at runtime is compared to a field of the

right table of an LEFT OUTER JOIN. Such comparisons are not supported by

all database systems and are therefore not allowed.