topic Re: Nested SQL-Statement with dynamic "exists" in Application Development and Automation Discussions

Nested SQL-Statement with dynamic "exists"

Private_Member_19084 — Tue, 21 Sep 2010 13:42:26 GMT

Hi experts

I need a multi sql-statement with an dynamic "exists"-command on the the second level.

Example:

dynamic where = 'WERKS EQ MD_{WERKS AND LGORT EQ MD}LGORT'.

select *

from mara as ma

where exists ( select *

from mard as md

where matnr eq ma~matnr

and exists ( select *

from t001l

where (dynamic_where) ).

When I do it on this way, i get a dump called SAPSQL_INVALID_TABLENAME, regarding that the system don't knows the table 'MD'.

But when I do it on the way below it runs.

select *

from mara as ma

where exists ( select *

from mard as md

where matnr eq ma~matnr

and exists ( select *

from t001l

where werks eq md~werks

and lgort eq md~lgort ).

Can someone please help me, to create a dynamic where-statement on t001l instead of the constant-statement.

Regards

Christian

Re: Nested SQL-Statement with dynamic "exists"

ThomasZloch — Tue, 21 Sep 2010 13:48:13 GMT

Dynamic WHERE-conditions in subqueries are not supported at least until Rel. 7.00, see ABAP documentation. I don't know whether new enhancement packages will add this feature.

Have you looked into rewriting your query to use joins instead of subqueries?

Thomas

Re: Nested SQL-Statement with dynamic "exists"

Private_Member_19084 — Tue, 21 Sep 2010 14:34:45 GMT

Hi Thomas

First, thank you for your fast and very helpful answer.

I've tried an InnerJoin, but with this I can't make a dynamic "join-statement".

Re: Nested SQL-Statement with dynamic "exists"

Former Member — Tue, 21 Sep 2010 14:37:09 GMT

You can have a dynamic where condition using internal table.

select x y z from table t where(itab).

Check in SDN, u'll find a lot of posts.

G@urav

Re: Nested SQL-Statement with dynamic "exists"

kesavadas_thekkillath — Tue, 21 Sep 2010 14:39:54 GMT

What do you mean by a dynamic Join statement. I can see only a dynamic where clause in your statement which can be build using a join statement.

Re: Nested SQL-Statement with dynamic "exists"

ThomasZloch — Tue, 21 Sep 2010 15:10:10 GMT

I think you can do it using the dynamic syntax

SELECT ... FROM (dbtab_syntax) INTO ...

dbtab_syntax can contain the entire JOIN, ON and WHERE syntax, but it must be a valid expression, or you will produce short dumps unless caught via TRY / ENDTRY.

Thomas

Re: Nested SQL-Statement with dynamic "exists"

Former Member — Tue, 21 Sep 2010 16:21:20 GMT

I did a dynamic join recently,

check the sample code if it helps

     CONDENSE gs_struct-val_tab NO-GAPS.
      CONDENSE gs_struct-val_field NO-GAPS.

      CONCATENATE gs_struct-val_tab '~' gs_struct-val_field INTO fld_where.

      CONCATENATE fld_where ' EQ ' l_com  <valor> l_com INTO strwhere RESPECTING BLANKS.

      " validacion con constante?
      IF gs_struct-val_cons IS NOT INITIAL.
        CONCATENATE strwhere 'AND' gs_struct-val_cons INTO strwhere SEPARATED BY space.
      ENDIF.

      IF <valor> IS INITIAL AND gs_struct-notnull EQ 'X'.
        PERFORM set_light USING 'E' CHANGING <light>.

        CONCATENATE 'El valor del campo' gs_struct-fieldname 'es nulo'
        INTO <message> SEPARATED BY space.
      ELSE.
*&--------------------------------------------------------------------------------------*
*&      el valor no es nulo, valida sobre tabla-campo
*&--------------------------------------------------------------------------------------*
        IF gs_struct-val_tab IS NOT INITIAL AND gs_struct-val_field IS NOT INITIAL.
          CONCATENATE gs_struct-val_tab '~'gs_struct-val_field
          INTO strsel.

          CONCATENATE gs_struct-val_tab 'AS' gs_struct-val_tab
          INTO strfrom SEPARATED BY space.

          IF gs_struct-val_join IS NOT INITIAL AND gs_struct-val_on IS NOT INITIAL.
            CONCATENATE strfrom 'INNER JOIN' gs_struct-val_join 'AS' gs_struct-val_join 'ON' gs_struct-val_on
              INTO strfrom SEPARATED BY space.
          ENDIF.

          TRY .
              SELECT SINGLE (strsel)
              INTO <dummy>
              FROM (strfrom)"(gs_struct-val_tab)
              WHERE (strwhere).
            CATCH cx_sy_dynamic_osql_semantics.
              CLEAR ok_code.
              MESSAGE e001(00) WITH 'Error en generación de consulta'.
          ENDTRY.

          IF sy-subrc NE 0.
            PERFORM set_light USING 'E' CHANGING <light>.
            CONCATENATE 'Valor no encontrado en' gs_struct-val_tab '-'
                                                 gs_struct-val_field
                                                 INTO <message> SEPARATED BY space.
          ENDIF.
        ENDIF.

Re: Nested SQL-Statement with dynamic "exists"

Private_Member_19084 — Wed, 22 Sep 2010 05:56:39 GMT

Hi Thomas

Thank you again for your great answer.

Now, this solved my problem.

I've done it wrong, I thought the dynamic-statement has to be in the 'ON'.

Now I've made a dynamic 'FROM'-statement and it runs great.

Thx a lot for your help.

Chrsitian