topic Re: Effective Single Query... in Application Development and Automation Discussions

Effective Single Query...

Former Member — Tue, 06 May 2008 20:08:39 GMT

Hi All,

I have a need to combine multiple queries into single and declare it as cursor. This would minimize the complexities in defing cursor for a BI Extractor. These queries are on the same table but with deifferent selection condition. COuld anyone combine and give a single query and how it can be done?

1. SELECT PERNR USRID FROM PA0105 WHERE SUBTY = '0001'.

2. SELECT PLANS FROM PA0001 WHERE PERNR = PERNR from Step 1.

3. SELECT SOBID FROM HRP1001 WHERE OTYPE = 'S' AND

PLVAR = '01' AND

RSIGN = 'A' AND

RELAT = '003' AND

SCLAS = 'O' AND

OBJID = PLANS from step 2.

4. SELECT SOBID FROM HRP1001 WHERE OTYPE = 'S' AND

PLVAR = '01' AND

RSIGN = 'A' AND

RELAT = '003' AND

SCLAS = 'O' AND

ONJID = SOBID from step 3.

5. SELECT SOBID FROM HRP1001 WHERE OTYPE = 'O' AND

PLVAR = '01' AND

RSIGN = 'B' AND

RELAT = '012' AND

SCLAS = 'S' AND

OBJID = SOBID from Step 4.

Thanks in advance.

Alex.

Re: Effective Single Query...

Jelena_Perfiljeva — Thu, 08 May 2008 21:06:14 GMT

It looks like everyone is too busy to write the queries for you (hey, people get paid for this! ), but let me clue you in.

The first 2 guys can be combined with a JOIN. The other 3 could be added via so called subquery. Look up ABAP Help on SELECT command and there should be an entry for subqueries. Here is an example from ABAP Help just to illustrate the idea:

SELECT SINGLE city latitude longitude 
         INTO (city, lati, longi) 
         FROM sgeocity 
         WHERE city IN ( SELECT cityfrom 
                                FROM spfli 
                                WHERE carrid = carr_id AND 
                                      connid = conn_id ).

If everything does not have to be in one very big and complex SELECT, you might also want to use an internal table and FOR ALL ENTRIES. Make sure to check if the table is not empty before doing FOR ALL ENTRIES.

Once again, you'll find all the information in ABAP Help.

Re: Effective Single Query...

Former Member — Thu, 15 May 2008 16:30:40 GMT

Due to performance issues, Sub query usage is not recommanded when it can be done with join. But anyway I solved this issue and closing this question. Thanks for your replies.