How Can I reconstruct the SQL

[Former Member]

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.

[Former Member]

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.

[former_member515797]

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.