topic Re: query help in Application Development and Automation Discussions

query help

Former Member — Fri, 16 Feb 2007 23:04:35 GMT

hi folks,

I am working ona query to get the unique cost center record populated into a internal table for a particular perosnnel area (WERKS) I used CSKS table to retieve the cost centers supervised by a particular person. But the person is responsible for multiple personnel areas. But I want the list of cost centers that for a particular personnel area.

I used the code

select * from csks where kokrs = 'ABC ' and BUKRS = '0100'

and GSBER = '0100' and Name2 = v_name.

But this gave all the cost centers the person was holding including the records outside the required personnel areas that led me to extra records.

there is field WERKS here but is not populated.

hence I tried to join this with pa0001 to restrict the cost center records to a particular area.

select distinct csks~kostl into v_kostl from csks inner join

pa0001 on csks_{bukrs = pa0001}bukrs

and csks_{gsber = pa0001}gsber

where csks~bukrs = '0100'

and csks~gsber ='0100'

and csks~Name2 = v_name

and pa0001~werks = 'BA01'.

The query is not returning the right records. Is there a different table that I can use to validate or am I missing something here?

Thanks,

Re: query help

Former Member — Mon, 19 Feb 2007 11:57:25 GMT

Hi,

I think there is a small issue with your join statement.

it joins the two tables on following criterion

csks_{bukrs = pa0001}bukrs

and csks_{gsber = pa0001}gsber

you should add one more condition

as csks_{name2 = pa0001}sname

this will reduce the reocrds for particular user.

and after this your selecttion criterion of "name2 = v_name" and

pa0001~werks = 'BA01'

will give you the desired result.

Hope this helps you..

Award reward points to helpful answers )

Re: query help

Former Member — Mon, 19 Feb 2007 12:16:52 GMT

Hi,

If at all you need the Unique Cost center of an Employee it is present in PA0001 infotype. Why to use CSKS table or why to join it with PA0001 infotype.

All the required fields like KOKRS, GSBER, WERKS,SNAME,BUKRS ETC ARE PRESENT IN pa0001. So if you write a Select on PA0001 alone will give you the necessary info.

Regards,

Anji

Re: query help

Former Member — Mon, 19 Feb 2007 12:20:30 GMT

Hi,

You can pass KOSTL and DATE to teh query, as these are fields it will bring u the correct records if u pass all teh key field values.

Re: query help

Former Member — Wed, 21 Feb 2007 14:57:15 GMT

Thanks a lot for everyone's reply, sorry responding a bit late.

Here is why I need CSKS- it has the list of cost centers and person responsible for those cost centers from csks-name2 which is the requirement for my report. Hence I cannot just go for PA0001 - where Pa0001-sname is not equal to csks-name2.

The reason to go after PA0001 was to get the personnel area which is not avaiable in CSKS. Because for my report I need list of cost centers for that department(based on the personnel area) and it can so happen that a single person can be responsible for several cost centers across different personnel areas which is what is now happening.

it is giving me a list of cost centers outside that. Anwya I figured it out.

I shall award the points here.

Thanks,