vendor record duplication in data extraction

[sreekuax]

Hi Friends ,

Doing an extraction of Supplier data based on Invoice data.
so using the condition ap_vend = vd_addr . But this is making me go crazy ! the data file is having duplicate values .

See the scenario below which is happening now.

ap_vend vd_addr
------------ -------------
1 1
1 2
1 3
2 4
3 5
4
4
5

where ap_vend = vd_addr , I am extracting data from vd_mstr as well as ad_mstr .
i.e vd_addr ad_name ad_line1 etc ... are column headers.

when it equals first time ap_vend(1) = vd_addr (1)
-> it gives a record values to vd_addr ad_name ad_line1 etc

when the program runs... n number of times ap_vend value repeats... n number of time duplicates are present in vd_mstr extract file..

i.e vd_addr ad_name ad_line1
1 XXXXXX XXX1
1 XXXXXX XXX1
1 XXXXXX XXX1 - > why because ap_vend has value '1' 3 times.

I need n->1 relation/extraction. i.e after this record
vd_addr ad_name ad_line1
1 XXXXXX XXX1

if again ap_vend =1 = vd_addr then ignore this and compare next...

i.e vd_addr ad_name ad_line1
1 XXXXXX XXX1
2 XXXXXX XXX2
- > should be the data file ...

How this can be achieved. Any ideas ?

Progress Version : 9.1D, MFG/Pro eB2 CHUI
Thanks

 

[Cringer]

Please post your current code that isn't working. Also Progress Version might be important.

 

[LarryD]

Unless you post the code you are using, I'm not sure anyone can help you. You should also use the [ code ] tags for the code. And which version of Progress?

EDIT: *SNAP* Cringer ! ;-)

 

[sreekuax]

Friends..
Progress Version : 9.1D, MFG/Pro eB2 CHUI

Here is the code...

assign 
  m_name = 'QADID'.

FOR EACH VO_MSTR NO-LOCK :
  FOR EACH AP_MSTR WHERE AP_REF = VO_REF
    AND AP_TYPE = 'VO'
    AND AP_DATE >= 01/01/10 AND AP_DATE <= TODAY NO-LOCK:
    FOR each VPO_DET where vpo_ref = vo_ref no-lock :
      for first vd_mstr where vd_addr = ap_vend no-lock :
      end.
      if available vd_mstr then 
      do :
        IF VD_TYPE = 'EMPL' THEN NEXT.
        FOR first AD_MSTR WHERE AD_ADDR = VD_ADDR NO-LOCK :
        end.
        if available ad_mstr then
          put STREAM xcim-stream unformatted
            VD_ADDR ";"
            m_name ";"
            '"' ad_line1 '"' ";"
            AD_STATE ";"
            AD_CTRY ";"
            AD_ZIP ";"
            AD_CITY ";"
            " " ";"
            AD_NAME ";"
            " "
            SKIP.
      end. /*if available vd_mstr*/
      if not available vd_mstr then 
      do :
        put STREAM xcim-stream unformatted
          " " ";"
          " " ";"
          " " ";"
          " " ";"
          " " ";"
          " " ";"
          " " ";"
          " " ";"
          " " ";"
          ""
          SKIP.
      end.

    END. /* End of FOR EACH ap_mstr */
  END. /* End of FOR EACH vpo_det */
END. /* End of FOR EACH vo_mstr */
OUTPUT STREAM xcim-stream CLOSE.

 

[sreekuax]

any idea friends ?

 

[W.Wulmsen]

Your problem is vpo_det. Why do you use this table. Only use it for retrieving voucher purchase order combinatons. (vpo)

Then it will work.

 

[sreekuax]

Your problem is vpo_det. Why do you use this table. Only use it for retrieving voucher purchase order combinatons. (vpo)

Then it will work.

I have removed that but still its not working.

 

[SergioC]

Hi, this will serve?

[FONT=courier new]FOR EACH VO_MSTR NO-LOCK,[/FONT]
[FONT=courier new]    EACH AP_MSTR [/FONT]
[FONT=courier new]   WHERE AP_REF = VO_REF[/FONT]
[FONT=courier new]      AND AP_TYPE = 'VO'[/FONT]
[FONT=courier new]       AND AP_DATE >= 01/01/10 [/FONT]
[FONT=courier new]        AND AP_DATE <= TODAY NO-LOCK,[/FONT]
[FONT=courier new]    EACH VPO_DET [/FONT]
[FONT=courier new]   WHERE vpo_ref = vo_ref NO-LOCK [/FONT]
[FONT=courier new]   BREAK BY field_must_be_unique :[/FONT]
[FONT=courier new]
[/FONT]
[FONT=courier new]   IF LAST-OF(field_must_be_unique)[/FONT]
[FONT=courier new]   THEN DO:[/FONT]
[FONT=courier new]      /* Process here */[/FONT]
[FONT=courier new]   END.[/FONT]
[FONT=courier new]
[/FONT]
[FONT=courier new]END.[/FONT]
[FONT=courier new][/FONT]

Regards.