topic Re: Select query in Application Development and Automation Discussions

Select query

Former Member — Fri, 07 Jun 2013 06:37:45 GMT

Hi,

I wrote select statement my condition exclude the values to pick the records from s506 .

But getting fails pls help me how to pass and pick based on Kunnr..

SELECT SPTAG KUNNR VTWEG SPART MATNR VKBUR PRODH AEMENGE UMMENGE FROM S506
     INTO CORRESPONDING FIELDS OF TABLE IT_PLANT FOR ALL ENTRIES IN IT_KNUMH WHERE MATNR = IT_KNUMH-SMATN
               AND MATNR = IT_KNUMH-MATWA
               AND SPTAG BETWEEN MONTH2 AND MONTH4
               AND KUNNR NOT IN ('0000600002','0000600003','0000600004','0000600045','0000600012','0000600038','0000606155')
               AND PRODH NOT IN S_PRODH
               AND VKBUR NOT IN ('3150','4150','8150','8450','3704','3104','4104','6111','8104')
               AND KDGRP NE 11.

Thanks ,

Gokul.

Re: Select query

ThomasZloch — Fri, 07 Jun 2013 08:02:28 GMT

WHERE MATNR = IT_KNUMH-SMATN

AND MATNR = IT_KNUMH-MATWA

looks strange, should this be an OR-condition? If yes, don't forget the brackets.

Otherwise this will be difficult to tell as we cannot peek inside your table contents. Try simulating your query with SE16 and see if this finds any data.

By the way, this hardcoding of values is really ugly, you have to change your code everytime these values need to be adjusted. Try solving this in a more elegant way.

Thomas

Re: Select query

Former Member — Fri, 07 Jun 2013 09:31:49 GMT

hi Gopi,

don't use for all entries in this case because your using

CORRESPONDING FIELDS in for all entries performance was not good and use READ TABLE in this case and don't use hot code

in where condition use like this

( MATNR = IT_KNUMH-SMATN AND MATNR = IT_KNUMH-MATWA )

Re: Select query

Former Member — Fri, 07 Jun 2013 11:28:24 GMT

Hi Gokul,

You can delete those customers from IT_KNUMH table and write your logic. And try to avoid using into corresponding fields as it effects the performance.

Re: Select query

ThomasZloch — Fri, 07 Jun 2013 12:25:01 GMT

Re: Select query

Former Member — Tue, 11 Jun 2013 10:38:18 GMT

Hi Gokul,

your select query is wrong at giving conditions.

( WHERE MATNR = IT_KNUMH-SMATN

AND MATNR = IT_KNUMH-MATWA)

we cannot use AND operation here, instead use OR with brackets.

Re: Select query

ravi_lanjewar — Wed, 12 Jun 2013 09:18:44 GMT

Hehehe,

It's greate work.

Note the following step.

1) please check data in driver table IT_KNUMH where value of SMATN and MATWA are same.

because you mention in condition

IT_KNUMH-SMATN AND MATNR = IT_KNUMH-MATWA

in your SQL

There is not any record which both value are not same then it will not give any record in return.

and comment out the rest of condition only execute this sql

if it is giving the record then problem with other condition.

2) Now uncomment the next condtion and execute the following condition only

SELECT SPTAG KUNNR VTWEG SPART MATNR VKBUR PRODH AEMENGE UMMENGE

FROM S506
INTO CORRESPONDING FIELDS OF TABLE IT_PLANT

      FOR ALL ENTRIES IN IT_KNUMH WHERE MATNR = IT_KNUMH-SMATN
               AND MATNR = IT_KNUMH-MATWA
               AND SPTAG BETWEEN MONTH2 AND MONTH4

3) Similary check subsequnt condition while uncomment each condition and execute and observe the result.

After all you will identify due to which condition you are not getting result.