April 8, 2015 at 2:13 pm
This is the data source for an ASP.net dropdown list. I need to have a hyphen "-" in the first record of the dropdown list.
When I added this complex ORDER BY, I got the error "The multi-part identifier "dbo.Items.ItemNumber" could not be bound".
How can I keep the ORDER BY and still have the first record be a "-"
_____________________________________________________________
ALTER PROC [dbo].[procGetItemNumbersForProject]
@ProjectID int
AS
SELECT '-' AS [Value]
UNION
SELECT dbo.Items.ItemNumber
FROM dbo.Items INNER JOIN
dbo.Components ON dbo.Items.ItemID = dbo.Components.ItemID
WHERE (ProjectID = @ProjectID)
AND (ItemNumber IS NOT NULL) AND (RTRIM(ItemNumber) <> '')
ORDER BY --ItemNumber
Replace (dbo.Items.ItemNumber,
( SELECT (Left( SubString (dbo.Items.[ItemNumber], PatIndex( '%[0-9]%' , dbo.Items.[ItemNumber]), 10 ),
PatIndex( '%[^0-9]%' , SubString (dbo.Items.[ItemNumber], PatIndex( '%[0-9]%' , dbo.Items.[ItemNumber]), 10 ) + 'X' )- 1 ))), -- string to find
(SELECT Format( cast (Left(SubString ( dbo.Items.[ItemNumber] , PatIndex ('%[0-9]%' , dbo.Items.[ItemNumber]), 10 ),
PatIndex( '%[^0-9]%' , SubString (dbo.Items.[ItemNumber], PatIndex( '%[0-9]%' , dbo.Items.[ItemNumber]), 10 ) + 'X' )- 1 ) as int ), '00000' ))) -- replacement for found
April 8, 2015 at 4:30 pm
TBIG (4/8/2015)
This is the data source for an ASP.net dropdown list. I need to have a hyphen "-" in the first record of the dropdown list.When I added this complex ORDER BY, I got the error "The multi-part identifier "dbo.Items.ItemNumber" could not be bound".
How can I keep the ORDER BY and still have the first record be a "-"
_____________________________________________________________
ALTER PROC [dbo].[procGetItemNumbersForProject]
@ProjectID int
AS
SELECT '-' AS [Value]
UNION
SELECT dbo.Items.ItemNumber
FROM dbo.Items INNER JOIN
dbo.Components ON dbo.Items.ItemID = dbo.Components.ItemID
WHERE (ProjectID = @ProjectID)
AND (ItemNumber IS NOT NULL) AND (RTRIM(ItemNumber) <> '')
ORDER BY --ItemNumber
Replace (dbo.Items.ItemNumber,
( SELECT (Left( SubString (dbo.Items.[ItemNumber], PatIndex( '%[0-9]%' , dbo.Items.[ItemNumber]), 10 ),
PatIndex( '%[^0-9]%' , SubString (dbo.Items.[ItemNumber], PatIndex( '%[0-9]%' , dbo.Items.[ItemNumber]), 10 ) + 'X' )- 1 ))), -- string to find
(SELECT Format( cast (Left(SubString ( dbo.Items.[ItemNumber] , PatIndex ('%[0-9]%' , dbo.Items.[ItemNumber]), 10 ),
PatIndex( '%[^0-9]%' , SubString (dbo.Items.[ItemNumber], PatIndex( '%[0-9]%' , dbo.Items.[ItemNumber]), 10 ) + 'X' )- 1 ) as int ), '00000' ))) -- replacement for found
you could use a cte or derived table. you would need to ensure that '-' is always at the top of your order based on the types possible values returned by itemnumber by changing your order by clause accordingly.
ALTER PROC [dbo].[procGetItemNumbersForProject]
@ProjectID int
AS
with cte as
(
SELECT '-' AS ItemNumber
UNION
SELECT dbo.Items.ItemNumber
FROM dbo.Items INNER JOIN
dbo.Components ON dbo.Items.ItemID = dbo.Components.ItemID
WHERE (ProjectID = @ProjectID)
AND (ItemNumber IS NOT NULL) AND (RTRIM(ItemNumber)<>'')
)
SELECT ItemNumber from cte a
ORDER BY --ItemNumber
Replace (a.ItemNumber,
( SELECT (Left( SubString (a.[ItemNumber], PatIndex( '%[0-9]%' , a.[ItemNumber]), 10 ),
PatIndex( '%[^0-9]%' , SubString (a.[ItemNumber], PatIndex( '%[0-9]%' , a.[ItemNumber]), 10 ) + 'X' )- 1 ))), -- string to find
(SELECT Format( cast (Left(SubString ( a.[ItemNumber] , PatIndex ('%[0-9]%' , a.[ItemNumber]), 10 ),
PatIndex( '%[^0-9]%' , SubString (a.[ItemNumber], PatIndex( '%[0-9]%' , a.[ItemNumber]), 10 ) + 'X' )- 1 ) as int ), '00000' ))) -- replacement for found
April 9, 2015 at 8:59 am
Thank so much, peacesells
Wonderful solution 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply