ANSWER BASED ON WRONG PREMISE | Scroll down for update
I believe what you're asking is how to write your Where clause to include two possible customer values while all others remain the same right?
In that case, you group your predicates in parenthesis like so
*Erroneous Code Redacted
Obviously this isn't the same as your code, but I just wrote this to demonstrate the multiple customer conditions inside parenthesis producing a predicate that includes multiple conditions.
Edit: Now with your code
*Erroneous Code Redacted
Also, case statements comparing string literals are pretty expensive operations. Rather, load the values in a table and then join to it, then simply evaluate if that joined or not (with the Is Not Null Operator as shown above).
Also, you were using Coalesce([Expression], ' '), why add a space to the end of each code?
Alright... so I read through the thread and have a better understanding of what you're needing.
First off, since I took the time to fix it, here's better code:
*Erroneous Code Redacted
To answer this question:
how do i get the following out put [...]
Very simply, you can't.
Each row you're evaluating has a single UserConditionCode that is equal to '01', '04', 'PP' etc. That statement is evaluating each row, so if it only contains a single value, it can't possibly satisfy both conditions. i.e. a single cell cannot equal both '01' and '94'.
What are you trying to determine? Perhaps you're evaluating the wrong column in one of the expressions?
Edit 4 billion and actual answer
So I just realized what you meant by "EX- 28,01"
If you mean that data is stored in cells like that... you have a very poor architecture. Now, I don't say that to be an ***, because you should take that serious. Storing data in comma delimited 'groups' will make things VERY hard as the database grows. If you're storing multiple values in a single cell... well you CAN get data, but it's silly.
First, to get that;
Code:
-- Set Variables
Declare @ConditionCodes Table (
[UserCode] VarChar(2) Not Null);
Insert Into @ConditionCodes ([UserCode])
Select '28'
Union All Select '04'
Union All Select '94'
Union All Select '51'
Union All Select 'PP';
Declare @SecondStartingPos Int;
Set @SecondStartingPos = 2;
Declare @CodeLength Int;
Set @CodeLength = 2;
-- Execute Query
Begin
Select
'50000005000' As [PARTN]
,'0001' As [LGORT]
,[WarehouseSku] As [MATNR]
,[LotReferenceTwo] As [CHARG]
,Sum([QTYONHAND]) As [MENGE]
,Case
When [Codes].[UserCode] Is Not Null
Then 'S'
Else ''
End As [INSMK]
,Case
When (
SubString([UserConditionCode], 0, @CodeLength) = '01'
Or
SubString([UserConditionCode], @SecondStartingPos, @CodeLength) = '01'
)
Then 'X'
Else ''
End As [DZUSTD]
,'ICS' As [ERFME]
From
[InventoryAllView]
Left Join @ConditionCodes As [Codes]
On SubString([InventoryAllView].[UserConditionCode], 0, @CodeLength) = [Codes].[UserCode]
Or
SubString([InventoryAllView].[UserConditionCode], @SecondStartingPos, @CodeLength) = [Codes].[UserCode]
Where
[CustomerName] = $P{CustomerName}
And
[WarehouseName] = $P{WarehouseName}
And
[FacilityName] = $P{FacilityName}
Group By
[LotReferenceTwo]
,[CUSTOMERNAME]
,[WAREHOUSESKU]
,[USERCONDITIONCODE]
End
Now... about that design
You should completely remove that column and maintain a 1-to-many table for the associated codes. For example, you should have a table for "InventoryUserCodes". In that, you maintain simply the ID of the [InventoryAllView].[YourPrimaryKey] and the Code itself. To become even higher normal form, you would want to create a UserCode reference table and store that Id in your Codes table, and reference their string value.
That makes the query look a little more like this...
Code:
-- Set Variables
Declare @ConditionCodes Table (
[UserCode] VarChar(2) Not Null);
Insert Into @ConditionCodes ([UserCode])
Select '28'
Union All Select '04'
Union All Select '94'
Union All Select '51'
Union All Select 'PP';
-- Execute Query
Begin
Select
'50000005000' As [PARTN]
,'0001' As [LGORT]
,[WarehouseSku] As [MATNR]
,[LotReferenceTwo] As [CHARG]
,Sum([QTYONHAND]) As [MENGE]
,Max(
Case
When [Codes].[UserCode] Is Not Null
Then 'S'
Else ''
) As [INSMK]
,Max(
Case
When [UserConditionCodeReference].[CodeStringValue] = '01'
Then 'X'
Else ''
) End As [DZUSTD]
,'ICS' As [ERFME]
From
[InventoryAllView]
Inner Join [UserConditionCode] As [UserCodes]
On [InventoryAllView].[InventoryAllViewId] = [UserCodes].[InventoryAllViewId]
Inner Join [UserConditionCodeReference]
On [UserCodes].[UserConditionCodeReferenceId] = [UserConditionCodeReference].[UserConditionCodeReferenceId]
Left Join @ConditionCodes As [Codes]
On [Codes].[UserCode] = [UserConditionCodeReference].[CodeStringValue]
Where
[CustomerName] = $P{CustomerName}
And
[WarehouseName] = $P{WarehouseName}
And
[FacilityName] = $P{FacilityName}
Group By
[LotReferenceTwo]
,[CUSTOMERNAME]
,[WAREHOUSESKU]
,[USERCONDITIONCODE]
End
That is a far less expensive query.
Also, looks like you're using a view? You should try to get the code for that view and draw directly from the tables. Views can also add cost to your queries.
