topic Dynamic SQL - Long query in Application Development and Automation Discussions

Dynamic SQL - Long query

Former Member — Wed, 03 Mar 2010 06:45:50 GMT

Hi there,

I need to build a query at run time. The problem is I have two long JOIN conditions, as follows:


LEFT JOIN mbew ON mbew~matnr = mara~matnr AND mbew~bwkey = mseg~werks AND mbew~bwtar = mseg~bwtar
LEFT JOIN zmm_mseg_ext AS ext ON ext~mblnr = mkpf~mblnr AND ext~mjahr = mkpf~mjahr AND ext~zeile = mseg~zeile

I am using a table of EDPLINE structure but it has only 72 characters per line. Above joins are around 100 chars each. Trying to extend the line to 100 characters or use string as line type does not help. Either way I get

CX_SY_DYNAMIC_OSQL_SYNTAX exception.

I have also tried to split the statements into multiple lines i.e.:


[line1] LEFT JOIN mbew ON mbew~matnr = mara~matnr 
[line2]  AND mbew~bwkey = mseg~werks AND mbew~bwtar = mseg~bwtar

Still no go. How can I solve the issue ?

Thanks in advance,

Bart

Re: Dynamic SQL - Long query

andreas_mann3 — Wed, 03 Mar 2010 08:45:47 GMT

please post your complete coding with respect to select and join

Re: Dynamic SQL - Long query

rainer_hbenthal — Wed, 03 Mar 2010 09:02:05 GMT

You can use string variables to put the whole code in it. Its not necessary to split the SQL in lines. Mos DBMS limit the SQL to 32lbyte, that will for sure fit into a string variable.

Re: Dynamic SQL - Long query

Former Member — Wed, 03 Mar 2010 09:24:48 GMT

SELECT goes more less like this:


SELECT
      mkpf~mjahr mkpf~mblnr
      mseg~zeile mseg~lifnr mseg~wempf mseg~kunnr
      mseg~werks mseg~lgort
      mara~matnr mara~matkl
      marc~steuc
      ekko~ekorg ekko~ekgrp
      mbew~eklas
      t023t~wgbez
      lfa1~vbund AS spdos
      kna1~vbund AS spkli
FROM 
mkpf JOIN mseg ON mkpf~mblnr = mseg~mblnr AND mkpf~mjahr = mseg~mjahr
JOIN mara ON mseg~matnr = mara~matnr
JOIN marc ON mseg~matnr = marc~matnr AND mseg~werks = marc~werks
LEFT JOIN t023t ON t023t~matkl = mara~matkl AND t023t~spras = 'L'
LEFT JOIN zmm_mseg_ext AS ext ON ext~mblnr = mkpf~mblnr AND ext~mjahr = mkpf~mjahr AND ext~zeile = mseg~zeile
LEFT JOIN ekko ON ekko~ebeln = mseg~ebeln
LEFT JOIN mbew ON mbew~matnr = mara~matnr AND mbew~bwkey = mseg~werks AND mbew~bwtar = mseg~bwtar
LEFT JOIN lfa1 ON mseg~lifnr = lfa1~lifnr
LEFT JOIN kna1 ON mseg~kunnr = kna1~kunnr
WHERE
mkpf~budat IN o_budat
AND mseg~matnr IN o_matnr
AND marc~steuc IN o_steuc
AND mara~matkl IN o_matkl
AND mseg~lifnr IN o_lifnr
AND mseg~wempf IN o_wempf
AND mseg~kunnr IN o_kunnr
INTO CORRESPONDING FIELDS OF wa_entry.

Re: Dynamic SQL - Long query

rainer_hbenthal — Wed, 03 Mar 2010 09:34:04 GMT

No problem to put all of this into string variables.

Re: Dynamic SQL - Long query

SuhaSaha — Wed, 03 Mar 2010 09:36:48 GMT

Hello Bart,

There is definitely something wrong with the dynamic SELECT stmt you are creating. As suggested by Rainer try declaring the entire thing as TYPE STRING & proceed.

Anyways FYI you can refer to this SAP documentation on the different exceptions in Open SQL: [http://help.sap.com/abapdocu_70/en/ABENOPENSQL_EXCEPTIONS.htm|http://help.sap.com/abapdocu_70/en/ABENOPENSQL_EXCEPTIONS.htm]

BR,

Suhas

Re: Dynamic SQL - Long query

Former Member — Wed, 03 Mar 2010 09:37:19 GMT

Hello,

Hehe, yes, I was just trying to edit my post, but You responded and I couldn't do it anymore

It was a mistake in my query, It's true, string is sufficient to store the query, case closed.

Thanks,

Bart