Skip to Content
avatar image
Former Member

Nested SQL-Statement with dynamic "exists"

Hi experts

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

Example:

dynamic where = 'WERKS EQ MDWERKS AND LGORT EQ MDLGORT'.

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    Sep 21, 2010 at 01:48 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Thomas Zloch

      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

  • avatar image
    Former Member
    Sep 21, 2010 at 04:21 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded