topic Re: Open SQL SELECT Like ORACLE SQL SELECT in Application Development and Automation Discussions

Open SQL SELECT Like ORACLE SQL SELECT

Former Member — Thu, 13 May 2010 06:57:58 GMT

Hi experts,

how can i use 1st SQL is listed below.

TABLES : T179, VBRK.

SELECT-OPTIONS : s_prdha FOR t179-prodh OBLIGATORY,

s_fkdat FOR vbrk-fkdat OBLIGATORY.

RANGES : R_MATNR FOR MARA-MATNR.

(1st SQL)***************************

SELECT 'I' 'EQ' matnr

INTO TABLE r_matnr FROM mara

WHERE prdha IN s_prdha.

IF sy-subrc = 0.

(2nd SQL)**************************

SELECT vbrk_{vbeln vbrk}fkdat SUM( vbrp~fkimg )

INTO TABLE itab1

FROM vbrk AS vbrk

INNER JOIN vbrp AS vbrp ON vbrk_{vbeln = vbrp}vbeln

WHERE matnr IN r_matnr

AND fkdat IN s_fkdat.

ENDIF.

Re: Open SQL SELECT Like ORACLE SQL SELECT

SuhaSaha — Thu, 13 May 2010 07:13:42 GMT

I don't think you construct this SELECT statement in Open SQL.

Alternatively you can try:

R_MATNR-SIGN = `I`.
R_MATNR-OPTION = `EQ`.

SELECT MATNR FROM MARA
INTO R_MATNR-LOW WHERE prdha IN s_prdha.

APPEND R_MATNR.

ENDSELECT.

Else:


DATA:
IT_MATNR TYPE STANDARD TABLE OF MATNR,
WA_MATNR TYPE MATNR.

SELECT MATNR INTO TABLE IT_MATNR WHERE prdha IN s_prdha.

CHECK SY-SUBRC = 0.

LOOP AT IT_MATNR INTO WA_MATNR.
  R_MATNR-SIGN = `I`.
  R_MATNR-OPTION = `EQ`.
  R_MATNR-LOW = WA_MATNR.
  APPEND R_MATNR.
ENDLOOP.

Hope this helps.

BR,

Suhas

Re: Open SQL SELECT Like ORACLE SQL SELECT

Former Member — Thu, 13 May 2010 07:24:47 GMT

Dear Suhas,

I don't want to use select - endselect statement or a extra loop.

I know about the same, is there any other way???

thanks for reply

Re: Open SQL SELECT Like ORACLE SQL SELECT

Former Member — Thu, 13 May 2010 08:05:44 GMT

Open SQL does not support using literals in the selection field list as you've tried in your first SQL statement. Also, your second SQL statement is wrong, since you need to do a [group by|http://help.sap.com/abapdocu_70/en/ABAPGROUPBY_CLAUSE.htm] if you use an aggregate function like sum along with non-aggregated fields.

Now in theory you could of course attempt to get around the Open SQL limitation by grabbing some arbitrary one-character and two character field (e.g. mara-lvorm and mara-zeivr) and select those along with the material number in your range table. After the select you could overwrite the dummy information with 'I' and 'EQ' using the [modify .. transporting|http://help.sap.com/abapdocu_70/en/ABAPMODIFY_ITAB_SINGLE.htm#!ABAP_ONE_ADD@1@] statement. But that's just for sake of completeness, you really don't want to do this.

Here's what I'd try: You did already a join on vbrk and vbrp, so why not join more than two tables?! Now, instead of using the tables that you've specified, I'd revert to the SD index table vrpma, to allow efficient lookup's of billing documents by material number (and we're even lucky, because the fkdat field is also present, so we can even skip <em>vbrk</em>). Here's what I'd use:


select VRPMA~VBELN VRPMA~FKDAT sum( VBRP~FKIMG ) into table ITAB1
       from MARA inner join VRPMA on
            VRPMA~MATNR = MARA~MATNR
       inner join VBRP on
            VBRP~VBELN = VRPMA~VBELN and
            VBRP~POSNR = VRPMA~POSNR
       where MARA~PRDHA  in R_PRDHA and
             VRPMA~FKDAT in R_FKDAT
       group by VRPMA~VBELN VRPMA~FKDAT.

Please note that this is untested and you should actually validate that the results are correct. Note that under some circumstances the secondary index tables like vrpma might not be correct (one cause could be for example poorly coded exits), but that should rarely be the case.

By using the secondary index table and joining them all you allow the database to pick the best access path and ideally you actually end up with the best one...

Cheers, harald

Re: Open SQL SELECT Like ORACLE SQL SELECT

Former Member — Thu, 13 May 2010 11:10:45 GMT

Harald provide very useful details about message.