I have problem in my code.I have two issues

[Former Member]

1. i am supposed to get those values of the material number

vbpa-matnr

which does not contain material group between 300 -and 399

I have written the query like this

SELECT
       vbak~vbeln
       vbak~erdat
       vbak~vkorg
        ABRVW
        augru
        posnr
        matnr
        vbap~vkaus
        vbap~spart
        vbap~netwr
        vbap~waerk
        kwmeng
        kondm
        mvgr1
        mvgr2
        mvgr3
        mvgr4
        mvgr5
  FROM vbak AS vbak INNER JOIN vbap AS vbap
  ON vbak~vbeln EQ vbap~vbeln
  INTO corresponding fields of TABLE t_vbak
  WHERE vbak~vbeln EQ s_vbeln and vbap~matkl not in (300,399).

but Materials with Material group(VBAP-MATKL) between 000000300 and 000000399 are still appearing on the report ? eg on 4558 ? 10000231

Really need your earnest help to modify this......

2. SELECT
        VBAK~VBELN
        VBAK~VKORG
        VBAK~ERDAT
        VBAK~AUGRU
        vbkd~bstkd
        vbkd~bstdk
        tvaut~bezei
FROM VBAK as vbak join vbkd as vbkd
on vbak~vbeln eq vbkd~vbeln
and vbak~posnr eq vbkd~vbeln
left join tvaut as tvaut
on vbak~augru eq tvaut~augru
INTO CORRESPONDING FIELDS OF TABLE I_HEADER
WHERE VBAK~VBELN EQ S_VBELN.

here I am the BSTDK i.e the PO date is comming wrong

How to get it

Need immidiate help thnks

[Former Member]

Ok, your first query is this

SELECT

vbak~vbeln

vbak~erdat

vbak~vkorg

ABRVW

augru

posnr

matnr

vbap~vkaus

vbap~spart

vbap~netwr

vbap~waerk

kwmeng

kondm

mvgr1

mvgr2

mvgr3

mvgr4

mvgr5

FROM vbak AS vbak INNER JOIN vbap AS vbap

ON vbak_{vbeln EQ vbap}vbeln

INTO corresponding fields of TABLE t_vbak

WHERE vbak_{vbeln EQ s_vbeln and vbap}matkl le '000000300' and vbap~matkl ge '000000399'.

I couldn't understand what's your problem with the second query, can you rephrase the question?

[Former Member]

Hey thanks for this...

In my second query

There are different BSTDK for one sales order number.....

but in the view sales order

VA03 when I am watching them

the date which is coming in this out put is different

[Former Member]

SELECT

VBAK~VBELN

VBAK~VKORG

VBAK~ERDAT

VBAK~AUGRU

vbkd~bstkd

vbkd~bstdk

tvaut~bezei

FROM VBAK as vbak join vbkd as vbkd

on vbak_{vbeln eq vbkd}vbeln

and vbak_{posnr eq vbkd}vbeln

left join tvaut as tvaut

on vbak_{augru eq tvaut}augru

INTO CORRESPONDING FIELDS OF TABLE I_HEADER

WHERE VBAK~VBELN EQ S_VBELN.

In my version (R3 4.6C) VBAK doesn't have a posnr field, and I would find it weird, because it is the header.

Erase the comparison in bold letters and see what you get.

MAybe is the other way around? (vbak-vbeln eq vbkd-posnr)

[Former Member]

Maybe I am missing something here...

But how will the condition

vbap_{matkl le '000000300' and vbap}matkl ge '000000399' ever evaluate to true?

Anything that is less than 300 can not be greater than 399 and vice-versa.

I tried using the following condition instead,

vbap_{matkl le '000000300' or vbap}matkl ge '000000399'

This made the system hang or go into the wait state..

Looking forward to your suggestion..

Thanks.

[Former Member]

You are right.

Is better to use ranges, use Rich's example.

What about the other query?

[Former Member]

YOU SHOULD USE RANGES

IT WILL SOLVE IT

THNKX

BHANU

[RichHeilman]

For your first issue, if S_VBELN is a select-option, you should use the IN operator in the SELECT WHERE clause. And for the MATKL, what you have there is simply exluding material groups 300 and 399, not everything between. You should use a range for that.

ranges: r_matkl for vbap-maktl.

r_matkl-sign = 'I'.
r_matkl-option = 'BT'.
r_matkl-low = '300'.
r_matkl-high = '399'.
append r_matkl.

.......

WHERE vbak~vbeln IN s_vbeln 
       and vbap~matkl NOT IN r_matkl.

Regards,

Rich Heilman