topic Re: select and count in Application Development and Automation Discussions

select and count

Former Member — Wed, 05 Aug 2020 09:32:11 GMT

Hello everyone,

I need codding to process VBAK table and get information for KNUMVs wchich appears more than 1.
KNUMV is not key field here and im processing data to KONV where KNUMV is a key field, and I need check it before processing to not get any errors about duplicates.

Anyone can help?

Re: select and count

GK817 — Wed, 05 Aug 2020 09:39:33 GMT

Hi Beata,

Distinct Column:

SELECT COUNT( DISTINCT carrid )  FROM spfli  WHERE cityto = 'NEW YORK'  INTO @DATA(count).

Distinct Row:

SELECT DISTINCT cityto
  FROM spfli
  WHERE carrid = 'LH' AND
  cityfrom = 'FRANKFURT'
  INTO TABLE @DATA(destinations).

Gaurav

Re: select and count

VeselinaPeykova — Wed, 05 Aug 2020 09:48:01 GMT

Would you please share an example where you have more than one entry in VBAK for a specific VBAK-KNUMV?

Re: select and count

former_member1716 — Wed, 05 Aug 2020 09:54:47 GMT

plmalekbe,

I don't think that is necessary because every sales order in VBAK table will have unique Condition record numbers.

If you still need to check you can use below code, after the delete statement if you still have any entries left in table GT_VBAK then those are the KNUMV entries which are assigned to multiple VBELN values.

SELECT knumv,
       COUNT( vbeln ) AS count
FROM vbak
INTO TABLE @DATA(gt_vbak)
GROUP BY knumv.
IF sy-subrc EQ 0.
  DELETE gt_vbak WHERE count EQ 1.
ENDIF.

Regards!

Re: select and count

DoanManhQuynh — Thu, 06 Aug 2020 00:15:50 GMT

so why don't inner join those table?

Re: select and count

Former Member — Thu, 06 Aug 2020 13:01:10 GMT

Hi,

Thank you all for help, Satish your codding its very nice,

I will use it for sure. You may be right with VABK because I was checking it for many tables and this is true that VABAK was fine, but for example in VFKONV i found duplicates in KNUMV and I had to change approchach. Thanks again!

Example from se16h:

przechwytywanie.png