Need help to concatenate fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help to concatenate fields

Author

Topic

mnnarendra
Starting Member

1 Post

Posted - 2007-08-16 : 12:41:38

I need to create a SKU id which combines class (length 4), vendor (length 5), style (length 4), class (length 3) and size (length 4) from product table tbl_DIM_Product.
AND prefix with ‘0’ for the fields if the value is smaller than the suggested field length. I am trying to use replicate function and cannot seem to make this work.

I got stuck on creating the SKU using SQL Server.

SELECT t1.DateCreated, t2.Short_SKU,
replicate('0', 4 - datalength(t2.Class_Number)) + t2.Class_Number + ' ' +
replicate('0', 5 - datalength(t2.Vendor_Number)) + t2.Vendor_Number + ' ' +
replicate('0', 4 - datalength(t2.Style_Number)) + t2.Style_Number + ' ' +
replicate('0', 3 - datalength(t2.Color_Number)) + t2.Color_Number + ' ' +
replicate('0', 4 - datalength(t2.Size_Number)) + t2.Size_Number AS LongSKU,
t1.store_key, Sum(t1.Sale_Units) AS Promo_SumOfSale_Units,
t2.Permanent_Mrkdown_Flag, t1.Total_Discount_Amount, t2.Class_Number, t2.Vendor_Number,
t2.Style_Number, t2.Color_Number, t2.Size_Number
FROM (tbl_FACT_SUM_RawSalesWeek t1
INNER JOIN vw_DIM_Store t3 ON t1.store_key = t3.store_key)
INNER JOIN tbl_DIM_Product t2 ON t1.item_key = t2.item_key
GROUP BY t1.DateCreated, t2.Short_SKU, '0' & t2.Class_Number & '0' & t2.Vendor_Number & t2.Style_Number & t2.Color_Number & t2.Size_Number, t1.store_key, t2.Permanent_Mrkdown_Flag, t1.Total_Discount_Amount, t2.Class_Number, t2.Vendor_Number, t2.Style_Number, t2.Color_Number, t2.Size_Number
HAVING (((t1.DateCreated)>'6/12/2007') AND ((t2.Permanent_Mrkdown_Flag)='0') AND ((t1.Total_Discount_Amount)<0));

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-16 : 12:45:01

What is the error you get? Post some sample data and expected output..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-16 : 12:54:04

Try LEN instead of datalength,
from BOL
LEN
Returns the number of characters, instead of the number of bytes, of the given string expression, excluding trailing blanks

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-16 : 13:08:56

Also the sign "+" is used to concatenate string, not "&".

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-16 : 13:13:37

[code]SELECT t1.DateCreated,
t2.Short_SKU,
t1.store_key,
right('0000' + convert(varchar, t2.Class_Number), 4) + ' '
+ right('00000' + convert(varchar, t2.Vendor_Number), 5) + ' '
+ right('0000' + convert(varchar, t2.Style_Number), 4) + ' '
+ right('000' + convert(varchar, t2.Color_Number), 3) + ' '
+ right('0000' + convert(varchar, t2.Size_Number), 4) AS LongSKU,
Sum(t1.Sale_Units) AS Promo_SumOfSale_Units,
t2.Permanent_Mrkdown_Flag,
t1.Total_Discount_Amount,
t2.Class_Number,
t2.Vendor_Number,
t2.Style_Number,
t2.Color_Number,
t2.Size_Number
FROM tbl_FACT_SUM_RawSalesWeek AS t1
INNER JOIN vw_DIM_Store AS t3 ON t3.store_key = t1.store_key
INNER JOIN tbl_DIM_Product AS t2 ON t2.item_key = t1.item_key
WHERE t1.DateCreated >= '6/12/2007'
AND t2.Permanent_Mrkdown_Flag = '0'
AND t1.Total_Discount_Amount < 0
GROUP BY t1.DateCreated,
t2.Short_SKU,
t1.store_key,
t2.Permanent_Mrkdown_Flag,
t1.Total_Discount_Amount,
t2.Class_Number,
t2.Vendor_Number,
t2.Style_Number,
t2.Color_Number,
t2.Size_Number[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources