October 12, 2012 at 9:15 am
I am creating a manufacturing report that will allow users to run it by part number, range of part numbers, or all part numbers. However, I am running into issues trying to get it to run all part numbers.
I have set all three parameters (@FromPN, @ToPN, @Part) to allow null values, and even defaulted the @Part parameter to null. Still no luck! Any ideas as to what I am missing? Here is some code snippet.
Declare @FromPN char(30),@ToPN char(30)
Set @FromPN=FromPartNumber
Set @ToPN=ToPartNumber
select *
from
(
CODE
)x
where (x.PARTNUMBER between @FromPN and @ToPN and @FromPN is not null and @ToPN is not null)
or (x.PARTNUMBER=@Part and @FromPN is null and @ToPN is null)
October 12, 2012 at 9:55 am
bpowers (10/12/2012)
I am creating a manufacturing report that will allow users to run it by part number, range of part numbers, or all part numbers. However, I am running into issues trying to get it to run all part numbers.I have set all three parameters (@FromPN, @ToPN, @Part) to allow null values, and even defaulted the @Part parameter to null. Still no luck! Any ideas as to what I am missing? Here is some code snippet.
Declare @FromPN char(30),@ToPN char(30)
Set @FromPN=FromPartNumber
Set @ToPN=ToPartNumber
select *
from
(
CODE
)x
where (x.PARTNUMBER between @FromPN and @ToPN and @FromPN is not null and @ToPN is not null)
or (x.PARTNUMBER=@Part and @FromPN is null and @ToPN is null)
I don't know what your part numbers look like, but I'm assuming -99999 would be the minimum/smallest part number and 99999 is the max/largest part number. It looks like your part numbers are char(), so replace these with the smallest and largest possible values for your part number pattern.
Something like:
WHERE COALESCE(@Part, x.PartNumber) = x.PartNumber
AND x.PartNumber BETWEEN COALESCE(@FromPN, -99999) AND COALESCE(@ToPN, 99999)
I've not tested, but it should give you the idea you're after.
HTH,
Rob
October 12, 2012 at 11:09 am
The part numbers include numeric, alphanumeric, and sometimes special characters (-, /, etc...). I receive an conversion fail error when using COALESCE.
October 12, 2012 at 2:03 pm
I figured it out. Added the Null in the Select list and split out my Where clause.
Declare @FromPN char(30),@ToPN char(30)
Set @FromPN=FromPartNumber
Set @ToPN=ToPartNumber
select *
,null as PARTNUMBER
from
(
CODE
)x
where x.PARTNUMBER=@Part
or (x.PARTNUMBER between @FromPN and @ToPN)
or (@FromPN is null and @ToPN is null)
order by x.PARTNUMBER
October 12, 2012 at 2:31 pm
bpowers (10/12/2012)
The part numbers include numeric, alphanumeric, and sometimes special characters (-, /, etc...). I receive an conversion fail error when using COALESCE.
COALESCE merely returns back the first non-Null item in the list. You must have two different datatypes in the COALESCE(). For example:
DECLARE @a char(10) = 'Test';
DECLARE @b-2 int = 3;
DECLARE @C varchar(30) = NULL;
SELECT COALESCE(@a, @b-2, @C);
I'm glad you got what you needed figured out.
Rob
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply