product hierarchy

[Lata]

Hi,



I have PRODUCT hierarchy table. It has 3 levels in it. 1st level contains the head(main product)
ex: P1263 and the 2nd level contains its sub products
ex: P1263P1275, 3rd level contains many products under the subproduct ex: P1263P1275001276
P1263P1275001960

In the second level, its represented with a * after the prodcut number which means it has got subproducts under it
ex: P1263P1275*

And at 3rd level, its represented with * just before the product unique code
ex:*001276 which means its a unique product which is at 3rd level.

And these products are assigned to a customer generally at 2nd level which means even products under 3rd level are assigned to that particular customer. when I consider products at 2nd level, it has to cover 3rd level as well.

I want to join this table with another table on this product column but the products in that table has complete numbering (complete prodcut number at 3rd level).since I have got * marks at the beginning of the product in this table, I am not able to proceed on how to remove this * and still maintain uniqueness of the product and make a join.

Many Thanks!

Looking forward for some help from this forum!

[khalid]

Don't know why the * system is used to categorized products level? but you can manage your tables and normalized it with primary and foreign key relationship so that each table will have unique id as well as a common id (foreign key) for each record.

[Lata]

I cannot modify the tables as the tables source is from SAP system. I need to come up with a soultion to join the 2 tables based on product no. column, which is in different format in 2 different tables.

[rpeare]

Can you give an example of the data in each table? It's hard to give direction without some concept of how the table is constructed. For instance the part table could be something like

PrimaryKey PartNumber ParentPart
1          P1263
2          P1275*     P1263
3          *001276    P1275*

if you simply want to strip out the * when creating a part number you can use the left(fieldname, length of string).

So for example let's say your second string part was P1275* you would have left([PartNumber], 4) which would leave you with the string P1275. If your part numbers have variable lengths you could do something like
left([partnumber], len(partnumber)-1).

[Lata]

This is the product hierarchy from one of the tables :

P1263P3753*P1263P1397*P1263P3753*P1263P2635**001262*001512*002589*002690*002691*002692*002920

P1263P3753*These are second level data.
*001262
These are 3rd level data.

Copied below is product hierarchy data from another table :

P1263P1263P1275P1263P1275001276P1263P1275001960P1263P1278P1263P1278001279P1263P1278001280P1263P1278001289P1263P1278001291P1263P1278001292P1263P1397P1263P1397001400P1263P1397001406

Now I want to write a query to join the above 2 tables on this product column.

Truncating * out is not a correct solution I guess, but earlier I tried that aswell

[Lata]

Sorry I am unable to copy that field properly...

[rpeare]

Is there any variability in the length of the strings? or are they *always* 4, 9 or 7 (main, secondary, tertiary) characters long.

[rpeare]

P1263P3753*P1263P1397*P1263P3753*P1263P2635**001262*001512*002589*002690*002691*002692*002920

P1263P1263P1275P1263P1275001276P1263P1275001960P1263P1278P1263P1278001279P1263P1278001280P1263P1278001289P1263P1278001291P1263P1278001292P1263P1397P1263P1397001400P1263P1397001406

The first one breaks down into something like this

PrimPart SecPart TerPart
P1263
P1263    P3753   
P1263    P1397
P1263    P3753
P1263    P2635   001262
P1263    P2635   001512
P1263    P2635   002589
P1263    P2635   002690
P1263    P2635   002691
P1263    P2635   002692
P1263    P2635   002920

You said a * at the end of the secondary part of the part number indicates it has a tertiary part, but how do you tell based on the the string which tertiary part belongs to which secondary part? I just assumed because they all belonged to the one that they immediately followed in my example.

Your second string would break down to

PrimPart SecPart TerPart
P1263    
P1263    P1275
P1263    P1275   001276
P1263    P1275   001960
P1263    P1278
P1263    P1278   001279
P1263    P1278   001280
P1263    P1278   001289
P1263    P1278   001291
P1263    P1278   001292
P1263    P1397
P1263    P1397   001400
P1263    P1397   001406

]how would you intend to link the data in these two strings? The only thing they have in common is the primary part number P1263. Is that what you're interested in you want to collect all parts and sub parts from both tables in one collection?

[Lata]

Hi,

Please find attached 2 fields in 2 tables. I want to write a query or queries to join these 2 tables on this common field. The length of the products,subproducts and its subproducts will always be the same as shown in the table.

Hope my requirement is clear now...

Thanks in Advance.

[rpeare]

Ok, so you want to find some way to take the items in the PRODUCTHIER table and contcantentate them so they become a single product which you can then link to your CATEGORYID data.

ProductHier

P1002P4390*
P1263P2635*
*001262
*001512
*004230
*004394

This is an excerpt of the original data in the first section of your PRODUCTHIER text field. How do you determine which one of the third portion parts goes with which of teh second portion parts? Are you to assume that all the lines that begin with * apply to all of the lines that end with the * in the first part? Or do the parts with a * starting the string only apply to the last secondary part that is directly above them?

In your example do all the *00XXXX parts apply to secondary part P1263P2635* because it is the last secondary part before they tertiary parts begin?

EXAMPLE
P1002P4390
P1263P2635
P1263P2635001262
P1263P2635001512
P1263P2635004230
P1263P2635004394

or does every single tertiary part belong to every single secondary part as
well?

EXAMPLE
P1002P4390001262
P1002P4390001512
P1002P4390004230
P1002P4390004394
P1263P1397001262
P1263P1397001512
P1263P1397004230
P1263P1397004394
P1263P3753001262
P1263P3753001512
P1263P3753004230
P1263P3753004394

Without knowing how this text file as generated or the logic in creating it (if there was one) it would be pure guess work as to how to recreate the longer part ID's so that you could link the two datasets.

[Lata]

Hi rpeare

All the Tertiary *00XXXX parts apply to last secondary part that is just above them and not to the previous previous ones or the later ones.

The * in the tertiary part has to be truncated and replaced with secondary part that is just above them which makes it complete(complete number).

Many Thanks for looking into this.

[rpeare]

Here's an example. I used your input text file and assumed it was in the same directory as your database for the purposes of the example. Modify the code if your circumstances are different.

[rpeare]

I mean it, here's the attachment.

[Lata]

Thankyou very much for your help! its working for me ...