topic Re: SQL querry in Application Development and Automation Discussions

SQL querry

Former Member — Thu, 22 Jan 2009 06:52:19 GMT

Moderator message: please use a more informative subject in future

Hi,

i want retrive data from mseg and mkpf table, base on material , customer and posting date.

above three fields are report input.

means

customer - abc

material - xyz

date - 10/12/2008 to 10/01/2009

material and customer fields in mseg table .

posting date fields in mkpf table.

code

SELECT * FROM MSEG INTO CORRESPONDING FIELDS OF TABLE IT_MSEG
WHERE MATNR IN MAT AND KUNNR IN CUST .
SELECT * FROM MKPF INTO CORRESPONDING FIELDS OF TABLE IT_MKPF
FOR ALL ENTRIES IN IT_MSEG
WHERE MBLNR = IT_MSEG-MBLNR AND BUDAT IN DATE .

but it not give between posting date record.

Thanks in Advance.

Regard

Sam.

Edited by: Matt on Jan 22, 2009 9:59 AM

Re: SQL querry

Former Member — Thu, 22 Jan 2009 06:57:22 GMT

Hi,

try like this,

SELECT * FROM MSEG INTO CORRESPONDING FIELDS OF TABLE IT_MSEG

WHERE MATNR IN MAT AND KUNNR IN CUST .

SELECT * FROM MKPF INTO CORRESPONDING FIELDS OF TABLE IT_MKPF

FOR ALL ENTRIES IN IT_MSEG

WHERE MBLNR = IT_MSEG-MBLNR

AND BUDAT IN DATE

AND MJAHR = IT_MSEG-MJAHR . "Added

Re: SQL querry

Former Member — Thu, 22 Jan 2009 07:00:03 GMT

HELLO

MSEG is a cluster table and the complexity of data in mseg table is very high,so before fetching the data from mseg please make sure that you are providing full primary key details in the where clause so that only the required results are fetched.

hope it helps.

regards

geeta gupta

Re: SQL querry

SuhaSaha — Thu, 22 Jan 2009 07:03:38 GMT

Hello Sam,

If you try this way, MSEG will have data for MBLNR not in the date range.

I will suggest use INNER JOINS on MKPF & MSEG.

BR,

Suhas

PS: I suggest this to get consistent data & nothing to do with performance.

Re: SQL querry

Former Member — Thu, 22 Jan 2009 07:04:56 GMT

HI Sam,

I would advise you to build a range for MJAHR also from date and use it in both MSEG and MKPF.

Also what other erroneous BUDAT are you getting in the output..can u provide some examples...

and yes MSEG is a transparent table...

so i will suggest you to fetch data using a join..

SELECT (field_list) FROM MKPF as a JOIN mseg as b ON a_{mblnr = b}mblnr and a_{mjahr = b}mjahr

WHERE ...all conditions...

Edited by: Ankesh Saharan on Jan 22, 2009 12:38 PM

Re: SQL querry

Former Member — Thu, 22 Jan 2009 07:06:06 GMT

Suhas,

Why in that case the restriction in WHERE clause for BUDAT IN DATE not work??

Re: SQL querry

SuhaSaha — Thu, 22 Jan 2009 07:12:29 GMT

Hello Ankesh,

Quite simple: MSEG has no restriction on BUDAT --> You get data based on the WHERE clause say 20 MBLNRs.

MKPF has BUDAT restriction --> You get 5 MBLNRs from the 20 in IT_MSEG which fall in the date reange.

Now you have Loop at IT_MKPF and delete from IT_MSEG. IF you use JOINS you donot have to worry about this.

Suhas

Re: SQL querry

Former Member — Thu, 22 Jan 2009 07:46:17 GMT

Hi Ankesh and all,

Thanks for answer.

problem Sloved.

Re: SQL querry

matt — Thu, 22 Jan 2009 08:59:53 GMT

Please use a more informative subject in future