Select Statement

[Former Member]

Hi All,

I am working on a report and I have written the following code:

SELECT tstc~pgmna

tstc~tcode

tstct~sprsl

tstct~ttext

trdir~secu

INTO TABLE it_test

FROM ( tstc INNER JOIN tstct ON tstct_{tcode = tstc}tcode

INNER JOIN trdir ON trdir_{name = tstc}pgmna )

WHERE pgmna LIKE 'Z%' OR name LIKE 'Y%'.

I made one change in this .... as the where condition initially it was checking the program name with what user enters but noe I have changed and made it select all the custom program name.... but somehow the performance of the program is bad now ....can you please tell me if there is anything I can do with the code to make the performance better.

Thanks,

Rajeev

[Former Member]

Hi, try this

RANGES: gr_pgmna FOR tstc-pgmna.

gr_pgmna-sign = 'I'.

gr_pgmna-option = 'CP'.

gr_pgmna-low = 'Z*'.

APPEND gr_pgmna.

gr_pgmna-low = 'Y*'.

APPEND gr_pgmna.

SELECT tstc~pgmna

tstc~tcode

tstct~sprsl

tstct~ttext

trdir~secu

INTO TABLE it_test

FROM ( tstc INNER JOIN tstct ON tstct_{tcode = tstc}tcode

INNER JOIN trdir ON trdir_{name = tstc}pgmna )

WHERE pgmna IN gr_pgmna

%_hints oracle 'INDEX ("TSTC" "TSTC~001")'.

I hope that help .. =D

[Former Member]

Hi David,

I used the following code as suggested by you:

gr_pgmna-sign = 'I'.

gr_pgmna-option = 'CP'.

gr_pgmna-low = 'Z%'.

APPEND gr_pgmna.

gr_pgmna-low = 'Y%'.

APPEND gr_pgmna.

SELECT tstc~pgmna

tstc~tcode

tstct~sprsl

tstct~ttext

trdir~secu

INTO TABLE it_tcodelisting

FROM ( tstc INNER JOIN tstct ON tstct_{tcode = tstc}tcode

INNER JOIN trdir ON trdir_{name = tstc}pgmna )

WHERE pgmna IN gr_pgmna.

but it didn't fetch a single record... can you please tell wht's missing !!!

Thanks,

Rajeev

[ThomasZloch]

Now that you switched to ranges, the wildcard is '*', not '%'.

Thomas

[Former Member]

hi,

Initially I used '*" only but id didn't work... but '%' is working !!!

[Former Member]

>

> hi, 
> 
> Initially I used '*" only but id didn't work... but '%' is working !!!

No it's not. Try Thomas's suggestion.

Rob

[Former Member]

Hi Rob,

I don't know why this is happening... but as soon as I replace "%" with '*' I am losing all my data.

[Former Member]

Give this a try:

REPORT ztest LINE-SIZE 80 MESSAGE-ID 00.

TABLES: tstc, tstct, trdir.

DATA: BEGIN OF it_tcodelisting OCCURS 0,
        pgmna LIKE tstc-pgmna,
        tcode LIKE tstc-tcode,
        sprsl LIKE tstct-sprsl,
        ttext LIKE tstct-ttext,
        secu  LIKE trdir-secu,
      END   OF it_tcodelisting.

RANGES gr_pgmna FOR tstc-pgmna.

gr_pgmna-sign = 'I'.
gr_pgmna-option = 'CP'.
gr_pgmna-low = 'Z*'.
APPEND gr_pgmna.

gr_pgmna-low = 'Y*'.
APPEND gr_pgmna.

SELECT tstc~pgmna
tstc~tcode
tstct~sprsl
tstct~ttext
trdir~secu
INTO TABLE it_tcodelisting
FROM ( tstc INNER JOIN tstct ON tstct~tcode = tstc~tcode
INNER JOIN trdir ON trdir~name = tstc~pgmna )
WHERE pgmna IN gr_pgmna.

It works for me.

And please start using code tags.

Rob

Edited by: Rob Burbank on Feb 10, 2009 11:21 AM

[Former Member]

Thanks for the reply ROb.... this time it worked but the time it's taking is still the same

[Former Member]

To speed it up, you will have to add the language either to the JOIN condition or the WHERE:

SELECT tstc~pgmna tstc~tcode tstct~sprsl tstct~ttext trdir~secu
  INTO TABLE it_tcodelisting
  FROM ( tstc INNER JOIN tstct ON tstct~tcode = tstc~tcode
              INNER JOIN trdir ON trdir~name = tstc~pgmna )
  WHERE pgmna IN gr_pgmna
    AND sprsl = sy-langu.

If you can determine all the languages used in your system, you can build a range table for them.

Rob

[Former Member]

Thanks Rob it worked

[Former Member]

Rajeev,

Try deleting non z program records after the select has extracted the data.Something like this

SELECT tstc~pgmna

tstc~tcode

tstct~sprsl

tstct~ttext

trdir~secu

INTO TABLE it_test

FROM ( tstc INNER JOIN tstct ON tstct_{tcode = tstc}tcode

INNER JOIN trdir ON trdir_{name = tstc}pgmna ).

delete it_test where not ( prgname+0(1) na 'ZY')

[Former Member]

Thanks for the reply Sam.... but my internal table is fetching only custom programs and not the non cust ones

[Former Member]

Thats what I want , fetch all programs and then delete all those are not wanted.

[Former Member]

Hi Sam,

Thanks for the reply... I tried using your code but it didn't help... I mean it's still having the same running time !!! can you please suggest something else... when I tried to do the run tima alanysis usisng SE30 .. I could see that it's hitting database 98%..

any suggestion..

Thanks,

Rajeev

[Former Member]

Why do you have:

WHERE pgmna LIKE 'Z%' OR name LIKE 'Y%'.

You are testing two different fields, one of which is not the primary key.

Rob

[Former Member]

I am sorry Rob that was a typo...

thanks,

Rajeev

[Former Member]

The table TSTC has a index named 001

[Former Member]

Thanks for the reply David... I used this index statement also but still nothing is coming up as output

[Former Member]

I tried to debug the program David and found that it's skipping the whole select statement