topic Re: please validate my select stmt. in Application Development and Automation Discussions

please validate my select stmt.

Former Member — Sat, 04 Oct 2008 04:40:32 GMT

Can I use for all entries IN

with offset ?

SELECT KUNNR

FROM kna1

INTO TABLE gt_kna1

FOR ALL ENTRIES IN gt_soldto

WHERE name2+7(10) = gt_bstnk_storeno_kunnr-store_no

OR j_3astcu = gt_bstnk_storeno_kunnr-store_no.

Please guide me ... I need to select where kna1-name2 ( from 7th postion onwards ) contains the internal table value

then select kunnr field from kna1.

Thanks,

Re: please validate my select stmt.

kiran_k8 — Sat, 04 Oct 2008 04:49:01 GMT

Sam,

Your For All entries is gt_soldto internal table but the where conditions are w.r.t to something else.

K.Kiran.

Re: please validate my select stmt.

Former Member — Sat, 04 Oct 2008 05:01:27 GMT

Really sorry

SELECT KUNNR

FROM kna1

INTO TABLE gt_kna1

FOR ALL ENTRIES IN gt_soldto

WHERE name2+7(10) = gt_soldto-store_no

OR j_3astcu = gt_soldto-store_no

Is my select stmt

Re: please validate my select stmt.

Former Member — Sat, 04 Oct 2008 05:19:44 GMT

HI,

This code may help u...


FIELD-SYMBOLS: <fs> like line of gt_soldto.

LOOP AT gt_soldto assigning <fs>.
CONCATENATE '%' <fs>-store_no into <fs>-store_no.
ENDLOOP

SELECT KUNNR
FROM kna1
INTO TABLE gt_kna1
FOR ALL ENTRIES IN gt_soldto
WHERE name2 LIKE gt_soldto-store_no
OR j_3astcu = gt_soldto-store_no
.

Edited by: Sukriti Saha on Oct 4, 2008 7:20 AM

Re: please validate my select stmt.

Subhankar — Sat, 04 Oct 2008 07:03:13 GMT

You can not offset the field name in the where condition(i.e left side of operator)

WHERE name2+7(10) = gt_bstnk-store_no.............> wrong

You can use offset right side of operator

like this way matnr = i_mara-matnr+0(18).

Re: please validate my select stmt.

Former Member — Sat, 04 Oct 2008 07:27:53 GMT

Use the following code -

In case cases you cannot use offset in select statement and hence use substr

SELECT KUNNR FROM kna1

INTO TABLE gt_kna1

FOR ALL ENTRIES IN gt_soldto

WHERE substr(name2,7,10) IN gt_bstnk_storeno_kunnr-store_no

OR j_3astcu = gt_bstnk_storeno_kunnr-store_no.

-Siddhi Daftary

Re: please validate my select stmt.

Former Member — Sat, 04 Oct 2008 17:37:10 GMT

Why not just press F1 on SELECT?? None of the other answers here are correct. Only one approaches correctness.

Rob

Re: please validate my select stmt.

Former Member — Sun, 05 Oct 2008 03:11:23 GMT

Yes Rob. (Yes There is no one in Performance tunning like you solve :-)....)

Yes None of the answer is correct. Your answer about correction is correct. THANK YOU!

I tried with F1 help ... it did not help much.

I saw in debugging some where ... the offset is allowed .

But I missed to copy that and to document for my reference recently. I hoped on SDN ....Still waiting for some as well as looking trying my self with F1 and all.

Did any body do this offset in SELECT.

Re: please validate my select stmt.

Former Member — Sun, 05 Oct 2008 14:36:06 GMT

After pressing F1 on SELECT, look at the portion on WHERE. (This isn't performance; it's syntax).

Rob

Re: please validate my select stmt.

Former Member — Sun, 05 Oct 2008 18:09:28 GMT

SAM,

ur internal table is FOR ALL ENTRIES IN is "gt_soldto" ...

the table in for all entries need to be compared with in the WHERE clause..

but you where not using the internal table "gt_soldto" in your where clause..

This leads to syntax error....

It has to lead to error .. COZ, There is no meaning in having a table in FOR ALL ENTRIES without using it in where clause.....

Make an additional comaprision between the fields of "KNA1" and "gt_soldto" .. that will resolve your prob...

Re: please validate my select stmt.

Former Member — Mon, 06 Oct 2008 20:44:46 GMT

Hmmm....

Sorry - this isn't as simple as I first thought.

The solution I was thinking of involved patterns (Which is in the HELP for SELECT). But since you are using FOR ALL ENTRIES and not SELECT-OPTIONS or a range table, that doesn't help much.

You can still use a pattern, but it will involve looping through the internal table, and changing it to a pattern and using that in the SELECT.

An alternative is native SQL, but I think the first is better.

Sorry for my confusion Mea culpa

Rob