topic Re: Defaulting variable in select query in Application Development and Automation Discussions

Defaulting variable in select query

Former Member — Mon, 18 Dec 2006 19:33:50 GMT

Hi Experts,

I am having some different requirement here like, while selecting data from a table, for some column need to deafult some constant value for all lines if the actual value in the DB table has no value. There are some aggregate results like MAX( [DISTINCT] col ), MIN( [DISTINCT] col )...COUNT( * ) (or count(*)) , But there is nothing as such to fill constant value in query time it self while selecting data.

Do help me if any one come across such a like.

Ex:

Normal code we write:

SELECT carrid connid cityfrom cityto

FROM spfli

INTO CORRESPONDING FIELDS OF wa.

WRITE: / wa-carrid, wa-connid, wa-cityfrom, wa-cityto.

ENDSELECT.

Required code need to write:

SELECT NVL(e.carrid,1000) connid cityfrom cityto

FROM spfli

INTO CORRESPONDING FIELDS OF wa.

WRITE: / wa-carrid, wa-connid, wa-cityfrom, wa-cityto.

ENDSELECT.

Thanks in advance.

Mohan.

Re: Defaulting variable in select query

Former Member — Tue, 19 Dec 2006 03:49:27 GMT

Hi Mohan,

I think you can select the required fields into Internal table and then assign the values needed by looping through the internal table. This will be helpful.

I think we cant assign the constant values as if so while selecting.

Try that once.

Regards,

-Priyanka.

Re: Defaulting variable in select query

Former Member — Tue, 19 Dec 2006 04:15:32 GMT

Hi ,

I dont think you can set the default values in the select statement , what you can do is once you have selected it check the value , if it is initial then set the default value to it.

Regards

Arun

Re: Defaulting variable in select query

Former Member — Tue, 19 Dec 2006 16:16:25 GMT

Hi Priyanka / Arun,

Good to see your reply and Thanks. Yes your are correct, I agree with your answer and that is the way even SAP says.

But here I am dealing with some over million records in my internal table and for doing this in normal way i.e. like manipulating data with ITAB will taking long time. I am planning cut-down the time that is required for ITAB.

Please do reply only if any thing finds related to this.

Thanks a lot for your reply.

Re: Defaulting variable in select query

Former Member — Tue, 19 Dec 2006 17:04:54 GMT

The select that you are looking for a Oracle database specific statement, which I don't think is possible in ABAP. You might have to select the data and then loop at the internal table and do the actions accordingly.

Regards,

Ravi

Note - Please mark all the helpful answers

Re: Defaulting variable in select query

Former Member — Wed, 20 Dec 2006 03:12:24 GMT

Hi Mohan

As per my knowledge, we do not have the provision of the function that you are looking in ABAP as we have only 5 aggregate functions. Below are the same for your reference.

1. MAX

2. MIN

3. AVG

4. SUM

5. COUNT

I can understand your concern on the performance, but i guess there is not other way to go. Since you need to modify only one field, you can try with a logic similar to the below one:

SELECT carrid connid cityfrom cityto 
       FROM spfli 
       INTO TABLE itab.
wa-carrid = '1000'.

MODIF itab FROM wa TRANSPORTING carrid WHERE carrid IS INITIAL.

I guess this should be even better than modifying in the SELECT itself...

Hope this helps...

Kind Regards

Eswar

Re: Defaulting variable in select query

Former Member — Wed, 20 Dec 2006 15:55:34 GMT

Hi Eswar,

Thanks for your reply and I am also expecting the same and doing it also the same now. Still our mind sets are almost moving in parallel, good man. May be try to search for something on this, because you looks always for some challenging tasks right.

Best Regards,

Mohan

Re: Defaulting variable in select query

Former Member — Wed, 20 Dec 2006 17:31:33 GMT

Many thanks for the pleasing words...

Regards

Eswar

Re: Defaulting variable in select query

Former Member — Thu, 21 Dec 2006 18:30:21 GMT

Hi,

Try using field-symbols to make the necessary Internal table modifications...

as fields symbols use common memory space..like Pointers in C and it should not cause much impact on ABAP source code performance.

loop at t_itab assigning <fs_itab>.

<fs_itab>-f1 = new_value1.

<fs_itab>-f2 = new_value2.

......

endloop.

cheers!!

Prabhu

Message was edited by:

prabhu soundara pandian