topic How Can I reconstruct the SQL in Application Development and Automation Discussions

How Can I reconstruct the SQL

Former Member — Thu, 15 May 2008 14:31:01 GMT

Hi Gurus,

We are using SAP DB + 46C on Unix machine, I used to run the following SQL command on SAP DB directly , how can I reconstruct the same SQL to work in ABAP report.

SELECT SRTFD, COUNT(*), MAX(SRTF2) FROM INDX

WHERE RELID = 'SD'

GROUP BY SRTFD

HAVING COUNT(*)=1

AND MAX(SRTF2) != 0.

Re: How Can I reconstruct the SQL

Former Member — Thu, 15 May 2008 14:54:06 GMT

use the command EXEC.

Displaying an Extract from the Table AVERI_CLNT:

DATA: BEGIN OF WA,

CLIENT(3), ARG1(3), ARG2(3),

END OF WA.

DATA F3(3).

F3 = ' 1 '.

EXEC SQL.

SELECT CLIENT, ARG1 INTO :WA FROM AVERI_CLNT

WHERE ARG2 = :F3

ENDEXEC.

WRITE: / WA-CLIENT, WA-ARG1.

Native SQL supports the directly-executable commands of your underlying database system. There are other special commands that you can use after the EXEC SQL statement for cursor handling, stored procedures (procedures stored in the database), and connections to other databases.

Re: How Can I reconstruct the SQL

former_member515797 — Fri, 16 May 2008 06:13:44 GMT

Hi,

Everything you're using in your query including the COUNT, MAX, GROUP BY and HAVING can also be used in ABAP Open SQL, so a few minor changes should be sufficient to convert the code to Open SQL. At least this is the case in our 4.7 system; every upgrade seems to have added added extra functionality to Open SQL so it is possible that not all are available in 4.6C, in which case you'll have to use EXEC SQL. Anyway, try this.

DATA: BEGIN OF ls_indx,

srtfd TYPE indx-srtfd,

xcount TYPE I,

max_srtf2 TYPE indx-srtf2,

END OF ls_indx,

lt_indx LIKE TABLE OF ls_indx.

SELECT SRTFD COUNT( * )

MAX( SRTF2 )

INTO TABLE lt_indx

FROM INDX

WHERE RELID = 'SD'

GROUP BY SRTFD

HAVING COUNT( * ) = 2

AND MAX( SRTF2 ) <> 0.

Reward if helpfull...

Cheers,

Ameen.