topic Re: select query in Application Development and Automation Discussions

select query

Former Member — Thu, 31 Jul 2008 13:51:33 GMT

Hi All,

I have written the selct query as follows.

SELECT MIN( plpo~vornr ) INTO l_default_vornr

FROM mapl INNER JOIN plas

ON mapl_{plnty = plas}plnty

AND mapl_{plnnr = plas}plnnr

INNER JOIN plpo

ON plas_{plnty = plpo}plnty

AND plas_{plnnr = plpo}plnnr

AND plas_{plnkn = plpo}plnkn

WHERE mapl~matnr = '1234.123.12345'

AND mapl~werks = 'SS01'

AND mapl~plnty = 'N'

AND mapl~loekz EQ space

AND plas~plnfl EQ '0'

AND plas~loekz EQ space

AND plpo~loekz EQ space.

the problem is i m not able to get any value into l_default_vornr althought i have recors matching that criteria in the database.

Re: select query

Former Member — Thu, 31 Jul 2008 13:54:45 GMT

ST,

make sure first

WHERE mapl~matnr = '1234.123.12345'"strange value of matnr
AND mapl~werks = 'SS01'
AND mapl~plnty = 'N'
AND mapl~loekz EQ space
AND plas~plnfl EQ '0'
AND plas~loekz EQ space
AND plpo~loekz EQ space.

again wether it contains data or not .

Re: select query

Former Member — Thu, 31 Jul 2008 13:55:06 GMT

hi,

Check out this way ...Matnr will not have decimals in between .. i.e, it has to be '123412312345'


data : v_matnr type matnr value '123412312345',
         v_werks type werks value 'SS01',
         v_plnty  type plnty value 'N'.

SELECT MIN( plpo~vornr ) INTO l_default_vornr
FROM mapl INNER JOIN plas
ON mapl~plnty = plas~plnty
AND mapl~plnnr = plas~plnnr
INNER JOIN plpo
ON plas~plnty = plpo~plnty
AND plas~plnnr = plpo~plnnr
AND plas~plnkn = plpo~plnkn
WHERE mapl~matnr = v_matnr
AND mapl~werks = v_werks
AND mapl~plnty = v_plnty
AND mapl~loekz EQ space
AND plas~plnfl EQ '0'
AND plas~loekz EQ space
AND plpo~loekz EQ space.

Re: select query

Former Member — Thu, 31 Jul 2008 18:33:42 GMT

I m sure there is data in the data base. after the run of the select query the value of sy-subrc is 0 but still there is no value in the variable.

Re: select query

Former Member — Thu, 31 Jul 2008 19:04:34 GMT

As u r trying to find the min value of vornr ...may be it is maintained as blank for this record. Pl. check.

SELECT MIN( plpo~vornr ) INTO l_default_vornr

FROM mapl INNER JOIN plas

ON mapl_{plnty = plas}plnty

AND mapl_{plnnr = plas}plnnr

INNER JOIN plpo

ON plas_{plnty = plpo}plnty

AND plas_{plnnr = plpo}plnnr

AND plas_{plnkn = plpo}plnkn

WHERE mapl~matnr = '1234.123.12345'

AND mapl~werks = 'SS01'

AND mapl~plnty = 'N'

AND mapl~loekz EQ space

AND plas~plnfl EQ '0'

AND plas~loekz EQ space

AND plpo~loekz EQ space.

Re: select query

Former Member — Thu, 31 Jul 2008 19:10:06 GMT

If you're getting sy-subrc = 0, then maybe plpo-vornr = space. Have you gone through this in se16 and determined what the correct answer is? I notice you're not looking at the header table plko, so you're not checking validity dates as well, maybe there's an old task list with vornr = space?