03-24-2010 2:02 PM
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 af1 = bf1
inner join c on af2 = cf2......
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
03-24-2010 2:32 PM
Hi
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
03-24-2010 2:42 PM
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.
-