February 3, 2004 at 9:37 am
Hello,
I am building a bill of material where based on the level each part is in the bill of material I want to add a number of leading spaces to the output. I can't seem to be able to add the number of spaces as the output comes out without the leading spaces. The REPLICATE function works with the hypen but not the space. HELP!!!
SELECT REPLICATE("-",(levelNo-1)*5) + customer_number
FROM #tree, pd_parts_master
WHERE #tree.id = pd_parts_master.entry_id
ORDER BY part_sequence
The above code produces this:
A 251 620 01 87
-----A 251 620 01 86
----------A 251 610 01 76
----------A 251 610 02 76
----------A 251 610 04 10
-----A 251 625 01 86
-----A 251 626 40 16
Changing to SELECT REPLICATE(" ",(levelNo-1)*5) + customer_number produces this:
A 251 620 01 87
A 251 620 01 86
A 251 610 01 76
A 251 610 02 76
A 251 610 04 10
A 251 625 01 86
A 251 626 40 16
Changing to SELECT SPACE((levelNo-1)*5) + customer_number produces this:
A 251 620 01 87
A 251 620 01 86
A 251 610 01 76
A 251 610 02 76
A 251 610 04 10
A 251 625 01 86
A 251 626 40 16
Thank you very much for your assistance.
Robert Giannone
February 3, 2004 at 10:26 am
This works for me:
create table MyTest
( levelno int
, custno varchar(20)
, part_seq int
)
go
insert MyTest select 1, 'A 251 620 01 87', 1
insert MyTest select 2, 'A 251 620 01 87', 2
insert MyTest select 3, 'A 251 610 01 76', 3
insert MyTest select 3, 'A 251 610 02 76', 4
insert MyTest select 3, 'A 251 610 04 10', 5
insert MyTest select 2, 'A 251 625 01 86', 6
insert MyTest select 2, 'A 251 626 40 16', 7
go
SELECT REPLICATE('-',(levelno - 1) * 5) + custno 'customer'
FROM Mytest
ORDER BY part_seq
SELECT REPLICATE(' ',(levelno - 1) * 5) + custno 'customer'
FROM Mytest
ORDER BY part_seq
go
drop table MyTest
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply