topic Re: Multiply with in select query in Application Development and Automation Discussions

Multiply with in select query

Former Member — Sat, 18 Jul 2015 09:37:42 GMT

Hi,

Could anyone let me know how to achieve below functionality,

Select Node1, (Node2 * 10) into table lt_node from Ztable.

Note: Here Ztable has more than 30K records and I need to select all the records.

Regards,

Raghu.

Re: Multiply with in select query

Former Member — Sat, 18 Jul 2015 12:19:00 GMT

Hi Raghu,

You have to use native sql to achieve your requirement.

It is better you process the internal table as per your requirement and avoid native sql.

Re: Multiply with in select query

Former Member — Sat, 18 Jul 2015 12:30:07 GMT

Hello Raheem,

For of all Eid Mubarak !!!

Thanks for your reply. I have been trying native SQL but am getting dump saying 102 error. Will the native SQL depend on DB of my server, we use Oracle DB..

Regards

Raghu

Re: Multiply with in select query

former_member184158 — Sat, 18 Jul 2015 13:15:49 GMT

Hello Raghu, Raheem,

I wish you Eid Mubarak .

Could you post your code please.

Regards

Ibrahim

Re: Multiply with in select query

Former Member — Sat, 18 Jul 2015 16:44:23 GMT

Hi,

You can check this sample code for native Oracle Sql for Multiply

TYPES: BEGIN OF ty,
          id TYPE N,
          description TYPE C LENGTH 50,
          amount type i,
        END OF TY.

DATA: WA type TY,
       itab type STANDARD TABLE OF ty.

EXEC SQL.
   CONNECT TO 'MYCONNECTION'
ENDEXEC.

EXEC SQL.
   OPEN C FOR
   SELECT ID, DESCRIPTION, NVL(AMOUNT,0) * 10
   FROM TEST
ENDEXEC.

DO.
   EXEC SQL.
     FETCH NEXT C INTO :wa
   ENDEXEC.
   IF sy-subrc <> 0.
     EXIT.
   ENDIF.
   APPEND wa TO itab.
ENDDO.

EXEC SQL.
   CLOSE C
ENDEXEC.

EXEC SQL.
   DISCONNECT :'MYCONNECTION'
ENDEXEC.

LOOP AT ITAB INTO WA.
   WRITE: / WA-ID, WA-DESCRIPTION, WA-AMOUNT.
ENDLOOP.

Re: Multiply with in select query

former_member184158 — Sat, 18 Jul 2015 17:08:35 GMT

Hi Abdul,

could you please provide another example with loop , just to avoid native sql?

Re: Multiply with in select query

Former Member — Sat, 18 Jul 2015 17:15:54 GMT

hi Ibrahim,

I don't think there is any other method to multiply field.

In Open SQL it is not possible and therefore you have to process internal table only.

In Native SQL only it is possible as per above demo code.

If you find any other method then please share.

thanks.

Re: Multiply with in select query

Former Member — Mon, 20 Jul 2015 07:39:10 GMT

Could you please give me another example with actual SAP table and fields

Regards

Raghu

Re: Multiply with in select query

Former Member — Mon, 20 Jul 2015 14:03:12 GMT

Hi Raghu,

This is another example using SAP table Mara.

Only connection with the schema SAPSR3 is important.

TYPES: BEGIN OF ty,
          MATNR TYPE MATNR,
          BRGEW TYPE BRGEW,
        END OF TY.

DATA: WA type TY,
       itab type STANDARD TABLE OF ty.

EXEC SQL.
   CONNECT TO 'MYCONN2'
ENDEXEC.

EXEC SQL.
   OPEN C FOR
   SELECT MATNR, NVL(BRGEW,0) * 1000
   FROM MARA
   WHERE ROWNUM <= 20
ENDEXEC.

DO.
   EXEC SQL.
     FETCH NEXT C INTO :wa
   ENDEXEC.
   IF sy-subrc <> 0.
     EXIT.
   ENDIF.
   APPEND wa TO itab.
ENDDO.

EXEC SQL.
   CLOSE C
ENDEXEC.

EXEC SQL.
   DISCONNECT :'MYCONN2'
ENDEXEC.

LOOP AT ITAB INTO WA.
   WRITE: / WA-MATNR, WA-BRGEW.
ENDLOOP.