topic Manipulate Data in SQL Statement in Application Development and Automation Discussions

Manipulate Data in SQL Statement

Former Member — Wed, 11 May 2011 12:42:50 GMT

Hello there!

I want to manipulate Data in a SQL Statement. For instance:

SELECT
   name
   age
FROM pers INTO CORROSPONDING FIELDS OF it_ppl WHERE company = '01'.

The outcome would be something like


Hans   25
Sam    23
Marie  28
Julia  23

So what if I want to add an column behind the selected data, where the data output should not be variable from the database but a konstant, fix value?

I tried it like that:

SELECT
   name
   age
   'Fix value' AS value
FROM pers INTO CORROSPONDING FIELDS OF it_ppl WHERE company = '01'.

Outcome should be like

Hans   25  Fix value
Sam    23  Fix value
Marie  28  Fix value
Julia  23  Fix value

The big question is: a) Is this possible with Open SQL and if so b) how?!

Thanks in advance,

kind regards,

Christof!

Re: Manipulate Data in SQL Statement

kesavadas_thekkillath — Wed, 11 May 2011 13:16:47 GMT

Its possible through Native SQL. I have done some small modification to the standard SAP help program . Please check it.


types: BEGIN OF ty,
        connid   TYPE spfli-connid,
        cityfrom TYPE spfli-cityfrom,
        cityto   TYPE spfli-cityto,
        text type char03,  "<---
      END OF ty.

DATA c1 TYPE spfli-carrid VALUE 'LH'.

data:it type table of ty,wa type ty.

EXEC SQL PERFORMING loop_output.
  SELECT connid, cityfrom, cityto, 'abc' as text  "<---
  INTO   :wa
  FROM   spfli
  WHERE  carrid = :c1
ENDEXEC.

break-point. "Check the values in IT here

FORM loop_output.
  append wa to it. "<---
ENDFORM.

Re: Manipulate Data in SQL Statement

matt — Wed, 11 May 2011 13:17:24 GMT

Have you tried it? That would seem to me to be most pragmatic way of finding out.