SQL Problem (Sort by reference location)

[Former Member]

Dear Expert,,

I have a SQL question which i wonder if it could be handled in 1 SQL statement.

Lets Say in T001W Table in field REGIO i have different Entries for Region A, B, C, D, E, F, G

I have a reference data which sort REGIO = E first, then the rest is dont care.

Currently i managed to do it in 2 queries, 2 internal tables and use DELETE XXXX WHERE NE = 'E" and DELETE XXXX WHERE EQ 'E'.

How could i achieve it in SQL?

Thanks

Chris

[former_member209685]

Hi,

if the reference data is in a database table then you can join on that table and sort by the reference field. If the reference data is not in the DB then I'm pretty sure it's not possible.

Something like this:

SELECT * FROM T001W 
     AS t INTO LT_T001W JOIN ref_table AS r ON t~REGIO=r~REGIO 
ORDER BY r~sort_field.

Regards

    Adi

[Former Member]

Thanks Adi, but unfortunately that ref field is from internal table not from Transparent Table.

Thanks for your sharing btw.

[philipdavy]

Hi Christopher,

Can you be more clear about your requirement?>

Regards,

Philip.

[former_member209685]

What I'm assuming is that he has an internal table that looks similar to the following:

IT_REF_TABLE:

REGIO	ORDER
A	5
B	2
C	4
D	3
E	1

now he wants to select from T001W and have the returned records ordered by the ORDER field in the ref table.

Cheers

   Adi

[Former Member]

Hi Branchi,

                 If I have understood your question correctly. When you need segregate the data and need to fill two internal tables there will always be a need for two queries. You cant achieve with single query.

Hope this helps!

Happy Coding,

Santhosh Yadav

[Former Member]

Thanks for the discussion

To clarify my requirement:

Lets say i have a requirement to display some report by site

Some site have different region says:

Site 1   Region A

Site 2   Region B

Site 3   Region C

Site 4   Region A

User want to view the report based on the their region first,

So they need to sort like this (if the user is in Region A):

Site 1 Region A

Site 4 Region A

Site 2 Region B

Site 3 Region C

But that is dynamic since the user may sit in a different region.

For example, if use is in Region C.

Site 3 Region C

Site 1 Region A

Site 2 Region B

Site 4 Region A

What i care is the first section of result which may its current location, the rest of those i do not need to care the sorting.

Just brainstorming ,would it be possible to create a dynamic variable in SQL query and sort by that dynamic index?

BR - Chris

[Former Member]

Hi Branchi,

                 Now I get your Requirement. Just Brainstorming ! Your requirement in simple words is the DYNAMIC WHERE condition in SELECT query. We have had lot of discussions here in the forum lately.

Search the forum for dynamic select queries in ABAP.

also refer http://scn.sap.com/thread/3175783

However here is the sample code,

NOTICE THE itab IN BRACKETS IN WHERE ie; (itab)

REPORT  ZTEST.

DATA: cond(72) TYPE c,
            itab LIKE TABLE OF cond.

DATA wa TYPE spfli-cityfrom.

PARAMETERS: source(10) TYPE c, dest(10) TYPE c.

CONCATENATE 'CITYFROM = ''' source '''' INTO cond.
APPEND cond TO itab.

CONCATENATE 'OR CITYFROM = ''' dest '''' INTO cond.
APPEND cond TO itab.

CONCATENATE 'OR CITYFROM = ''' 'BAYERN' '''' INTO cond.
APPEND cond TO itab.

LOOP AT itab INTO cond.
WRITE cond.
ENDLOOP.

SKIP.

SELECT  cityfrom
INTO  wa
FROM  spfli
WHERE (itab).
WRITE / wa.
ENDSELECT.

Hope that helps!

Happy Coding,

Santhosh Yadav

[former_member209685]

The WHERE is not the problem the ORDER is the problem. He wants to select all regions but have the region of the user sorted first.

[Former Member]

Hi Chris ,

No need to worry about shorting show restricted data in your grid .

selection screen criteria divide in to part first to show all data and other to show data based on single region.

Regards

vivek

[Former Member]

Hi Adi,

          If grouping or sort order is the issue then what about the GROUP BY and ORDER BY clause in SELECT statement?

Refer Specifying a Sort Order (SAP Library - ABAP Programming (BC-ABA))

Regards,

Santhosh yadav

[nabheetscn]

Hi Chris

I dont think you can do this by group by since a lower/middle/higher values needs to be at first place. An alternative can be done as mentioned below.

You need only one select and two ITAB1

ITAB2 = ITAB1

delete itab1 where region ne 'User region'.

delete itab2 where region eq 'user region.

append lines line itab2 to itab1.

sort of similar approach but one selct.

Nabheet

[Former Member]

Thanks Nabheet.

I am currently using this approach to have my result.

Thanks all of experts to join the discussion