topic Re: Select Statement in Application Development and Automation Discussions

Select Statement

Former Member — Mon, 09 Feb 2009 20:24:02 GMT

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

Re: Select Statement

Former Member — Mon, 09 Feb 2009 20:32:15 GMT

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

Re: Select Statement

Former Member — Mon, 09 Feb 2009 20:40:17 GMT

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')

Re: Select Statement

Former Member — Mon, 09 Feb 2009 20:42:19 GMT

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

Re: Select Statement

Former Member — Mon, 09 Feb 2009 20:51:59 GMT

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

Re: Select Statement

Former Member — Mon, 09 Feb 2009 22:13:41 GMT

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

Re: Select Statement

Former Member — Tue, 10 Feb 2009 15:16:34 GMT

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

Re: Select Statement

Former Member — Tue, 10 Feb 2009 15:16:55 GMT

I am sorry Rob that was a typo...

thanks,

Rajeev

Re: Select Statement

Former Member — Tue, 10 Feb 2009 15:18:32 GMT

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

Re: Select Statement

ThomasZloch — Tue, 10 Feb 2009 15:22:18 GMT

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

Thomas

Re: Select Statement

Former Member — Tue, 10 Feb 2009 15:22:56 GMT

The table TSTC has a index named 001

Re: Select Statement

Former Member — Tue, 10 Feb 2009 15:26:22 GMT

hi,

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

Re: Select Statement

Former Member — Tue, 10 Feb 2009 15:28:18 GMT

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

Re: Select Statement

Former Member — Tue, 10 Feb 2009 15:32:29 GMT

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

Re: Select Statement

Former Member — Tue, 10 Feb 2009 15:34:06 GMT


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

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

Rob

Re: Select Statement

Former Member — Tue, 10 Feb 2009 15:44:17 GMT

Hi Rob,

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

Re: Select Statement

Former Member — Tue, 10 Feb 2009 16:20:49 GMT

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

Re: Select Statement

Former Member — Tue, 10 Feb 2009 16:37:47 GMT

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

Re: Select Statement

Former Member — Tue, 10 Feb 2009 16:58:29 GMT

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

Re: Select Statement

Former Member — Tue, 10 Feb 2009 17:12:24 GMT

Thanks Rob it worked