topic Select order by in Application Development and Automation Discussions

Select order by

Former Member — Wed, 17 Jun 2009 13:35:11 GMT

Hello everyone,

I have an issue:

I Know the aggregate function ORDER BY and I know we can specify the columns we need to put in order and if they have to be ASCENDING or DESCENDING

But I'd like to create a "personal criterium", for example i have the rseg table and the field KSCHL, i want to order this field this way:

KSCHL = ''

KSCHL = FRA1'

KSCHL = 'FRB1'

KSCHL = 'ZK02'

KSCHL = 'ZK05'

KSCHL = 'ZFR2'

KSCHL = 'ZIIP'

KSCHL = 'ZII'

I mean if I put DESCENDING or ASCENDING it won't be like this, but i need these last two types to be in the final and the type '' first.

How can i achieve this?

Thanks in Advance!

Re: Select order by

Former Member — Wed, 17 Jun 2009 14:02:43 GMT

Hi,

By the time of SELECT query using the ORDER BY clause you can have only any of the below options.

1. Using the primary key fields.

2. Using the field names, i.e. FIELD1 ASCENDING FIELD2 DESCENDING etc.. kind of addition.

3. Defining the our own sort criteria.

For fulfilling your requirement, it is not possible by using any of the available options.

The only way is to read the internal table with the required key using the binary key option and then appending it into another internal table etc.. kind of stuff is required.

Regards,

Santhosh.

Re: Select order by

Former Member — Wed, 17 Jun 2009 14:37:55 GMT

Hello, thanks for your answer

I had an idea? Don't you think a subquery would work??

I'll try it here, if i succeed I post!

Thanks once again!

Re: Select order by

former_member212005 — Wed, 17 Jun 2009 14:42:46 GMT

I dont think so...that you can specify it in select statement....i.e. your own criteria..

However what you can do is...

Once you have the data in an internal table ....you need to loop it and transfer the data in another internal table which has one more field may be SORT_POSN...


e.g. select*
      from dtable into gt_table.

LOOP AT gt_table INTO gs_table.
IF gs_table-kschl = ''.
    gs_final-SORRT_POSN = '1'.
* Move all the other data from gs_Table into gs_final.
APPEND gs_final TO gt_final.
ENDIF.

IF gs_table-kschl = 'FRA1'.
    gs_final-SORRT_POSN = '2'.
* Move all the other data from gs_Table into gs_final.
APPEND gs_final TO gt_final.
ENDIF.

IF gs_table-kschl = 'FRB1'.
    gs_final-SORRT_POSN = '3'.
* Move all the other data from gs_Table into gs_final.
APPEND gs_final TO gt_final.
ENDIF.

* Similarly do it for all other sort criterian...
ENDLOOP.

Now you have all the data in GT_FINAL..

All you have to do now is, sort the internal table GT_FINAL BY SORT_POSN.

Hope it helps!

Re: Select order by

Former Member — Wed, 17 Jun 2009 14:54:58 GMT

Ok, thank you very much! I got your idea!

but I'm trying to save time because I'm dealing with too much data and this code will take a lot of time in the program...

Thanks anyway