Assign sequential number (001, 002, 003) for each unique name field?

[#1]

Hi,

   I have a name field  COM_NM  .To each distinct COM_NM we need to assign sequential numbers --001,002,.......till the count of distinct COM_NM.

Can anybody give me exp:

Thanks

[#2]

CONNECT TO PD17DSP (XUserId is VQFfFBBKfaQeGXZOFTcGXUdNOTVCGWFNELYIXUdNODLA, XPassword is fQXfIBVOTDUEHaZNSbQMFYEGGG);



Source:

SQL SELECT "Company_Name_ID",

CONVERT(VARCHAR,"Company_ID")+'_C' BPEXT,

"Company_Name_Type_ID",

"Company_NM",

      FROM sdbCBO.dbo."EC_Company_Name";





Target:

LOAD BPEXT,

     PARTNER,

     ALPOSNR,

     ALNAME,

     ECISNameType,

         ECISName,

    
FROM

[..\QVD\BP_C.dbo.tvBKK21_PreVal_RptSel.qvd]

(qvd);



Left join (Target)

Load * resident Source;

drop table Source;



Left JOin



TEST:

Load DISTINCT

num(autonumber(ECISName,'sALPOSNR'),'000') as sALPOSNR

Resident Target

order by ECISName;

Drop Table TEST;

[swuehl]

Looks like you are doing the cross join already in the script, since the last join is missing a key.

What about

CONNECT TO PD17DSP (XUserId is VQFfFBBKfaQeGXZOFTcGXUdNOTVCGWFNELYIXUdNODLA, XPassword isfQXfIBVOTDUEHaZNSbQMFYEGGG);



Source:

SQL SELECT "Company_Name_ID",

CONVERT(VARCHAR,"Company_ID")+'_C' BPEXT,

"Company_Name_Type_ID",

"Company_NM",

      FROM sdbCBO.dbo."EC_Company_Name";





Target:

LOAD BPEXT,

    PARTNER,

    ALPOSNR,

    ALNAME,

    ECISNameType,

        ECISName,

        num(autonumber(ECISName,'sALPOSNR'),'000') as sALPOSNR

   
FROM

[..\QVD\BP_C.dbo.tvBKK21_PreVal_RptSel.qvd]

(qvd);



Left join (Target)

Load * resident Source;

drop table Source;

[sasiparupudi1]

Hi

Have you tried my solution? Or Are you still having a problem with issue?

Sasi

[#5]

Hi

I am dealing with other issue so didn't try this

Will surely let you know after trying

Thanks & Regards

[sasiparupudi1]

Have you come right with your problem yet?

[#7]

we are still working on the rule  for this once got it will update again.

Thank u.

Pls stay tuned....to Help me

To ALL

Thanks