topic Re: General SINGLE STATEMENT QUERY in Application Development and Automation Discussions

General SINGLE STATEMENT QUERY

Former Member — Thu, 29 Jun 2006 18:43:43 GMT

I want to read the unit of measure from marc table.

The whole program was hard coded for one plant ,now they added few more materials under different plant.

My orginal statement was like this

select single ausme into itab-ausme

from marc

where matnr = itab-matnr

and werks = 'ABC'.

NOW IAM CHANGING IT TO.

select single ausme into itab-ausme

from marc

where matnr = itab-matnr

and werks = 'ABC'

OR WERKS = 'DEF'.

can anybody tell me how does it work

I thought it would first check plant ABC ,IF ITS NOT FOUND IN PLANT ABC THEN IT WOULD CHECK pLANT DEF.

lET ME KNOW WHETHER IAM RIGHT OR NOT?

Re: General SINGLE STATEMENT QUERY

RichHeilman — Thu, 29 Jun 2006 18:47:32 GMT

No, because you are not using parenthesis

Make sure to use parenthesis. In your statement you are saying that I want record where MATNR = the_material and where plant is = 'ABC' OR forget about the material and where the plant = 'DEF'. Adding parenthesis, will help out your AND/OR logic.



select single ausme into itab-ausme
from marc
where matnr = itab-matnr
and ( werks = 'ABC'
OR WERKS = 'DEF' ).

Regards,

Rich Heilman

Re: General SINGLE STATEMENT QUERY

Former Member — Thu, 29 Jun 2006 18:47:52 GMT

Change it this way.


select single ausme into itab-ausme
  from marc
 where matnr = itab-matnr
   and ( werks = 'ABC'OR 
         WERKS = 'DEF' ).

Re: General SINGLE STATEMENT QUERY

Former Member — Thu, 29 Jun 2006 19:08:01 GMT

The best way would be to put it into a select-option and default it at the initialization event. That way, if (when) they want to add more plants, the user just changes the selection screen.

Rob

Re: General SINGLE STATEMENT QUERY

Former Member — Thu, 29 Jun 2006 19:11:16 GMT

or use,

select single ausme into itab-ausme

from marc

where matnr = itab-matnr

and werks in ( 'ABC' OR 'DEF' ).

but how you will know the retrieved AUSME value is for what plant ??? so its better to use PLANT(WERKS) also in the select condition.

select single werks

ausme

into (itab-werks,

itab-ausme)

from marc

where matnr = itab-matnr

and werks in ( 'ABC' OR 'DEF' ).

YOu also hardcoding the PLANT values in the selection screen.instead you can use a Select-option displayed on the selection-screen & use that select options in the where clause like

where WERKS IN S_WERKS.

regards

srikanth

Message was edited by: Srikanth Kidambi

Re: General SINGLE STATEMENT QUERY

sridhar_k1 — Thu, 29 Jun 2006 19:14:24 GMT

MARC might have entrys for one plant or both plants.

Select statement does not check for entry in plant DEF if not found for ABC, but selection stops after selecting first record. it might be ABC or DEF.

If primary key exists in the database (Usually do), it'll be sorted and you get ABC if both plants have records in the table.

Regards

Sridhar

Re: General SINGLE STATEMENT QUERY

Former Member — Thu, 29 Jun 2006 19:50:21 GMT

Thxs Guys for your valuable input and time