I have a list of 13 part groups that I am trying to use an If/Then statement to tell me which ones have zero’s in them for the particular customer. What I am running into is it’ll read the ones until it finds a zero then display what’s it’s supposed but it doesn’t keep reading and finish of the list. Can an If/Then be made to do this, or no?
Example:
Demo=0 Trans=1 Services=0 Patriot=0
Should display: Demo Services Patriot Instead just it displays Demo.
Please provide the code so we can look at what you are trying to do ?
-Jose
case
when Demo1 = 0 then ‘Demo’
when Trans1 = 0 then ‘Trans’
when Service2 = 0 then ‘Services’
when Fours2 = 0 then ‘400’
when Patriot2 = 0 then ‘Patriot’
when KeyTags2 = 0 then ‘Key Tags’
when Decal2 = 0 then ‘Decal’
when Mirror2 = 0 then ‘Mirror’
when Slogan2 = 0 then ‘Slogan’
when Vehicle2 = 0 then ‘Vehicle’
when Consecu2 = 0 then ‘Consecu’
when Flour2 = 0 then ‘Flour’
when Sun2 = 0 then ‘Sun’
else ‘’
end
This is working as designed , a case statement once a condition is true the case statement is done and breaks out.
-Jose
That’s what I was thinking. So thoughts on how to achieve what I am looking for?
BAsically I want to turn this
Customer Demo1 Trans1 Service2 Fours2 Patriot2 KeyTags2 Decal Mirror2 Slogan2 Vehicle Consecu Flour2 Sun2
593 1 0 0 1 0 1 0 1 1 0 0 0 0
925 1 0 0 0 0 0 0 0 0 0 0 0 0
902 1 0 0 0 0 1 0 0 1 0 0 0 1
23 1 0 0 0 0 0 0 0 0 0 0 0 0
215 1 0 0 0 0 1 0 0 0 0 0 0 0
710 1 0 1 1 0 1 0 1 1 0 0 0 0
46 1 0 0 0 0 0 1 0 0 0 1 0 0
401 1 0 0 1 0 1 1 1 1 1 0 0 1
733 1 0 0 0 1 0 1 0 1 0 0 0 0
192 1 0 1 1 0 1 0 1 1 0 1 0 0
1520 0 0 0 0 0 0 1 0 0 0 0 0 0
524 1 0 0 0 0 0 0 0 0 0 0 0 0
1094 1 0 0 0 0 0 0 0 0 0 0 0 0
Into just the headings, Demo, Trans, fours, or any appropriate combination.
I’m assuming that this is SQL based on your syntax, but in C# this could be accomplished with the use of a foreach loop in case I am mistaken.
As for SQL, I know this is ugly, but you could break each of the cases into its own calculated field and then combine into a final calculated field.
EX) This would be one of the calculated fields, called DemoString:
CASE WHEN Demo1 = 0 then 'Demo’ ELSE ‘’ END
You would do this for each of the part groups. Then all the part strings would combine into the final calculated field called FinalString:
DemoString + TransString + ServiceString + …
This could also all be done in one calculated field:
CASE WHEN Demo1 = 0 then 'Demo’ ELSE ‘’ END + CASE WHEN Trans1 = 0 THEN ‘Trans’ ELSE ‘’ END + …
Yes,it is. It the SQL Editor in Epicor.
You are going to have to make a calculated field for each of those cases
-Jose
so change this (case when SubQuery2.Part_RefCategory = ‘Demo’ then ‘1’ else ‘0’ end) to (case when SubQuery2.Part_RefCategory = ‘Demo’ then ‘0’ else ‘’ end)
Then in a serpate calulated filed do something like demo1+trans1+, etc, correct?
One more error, says Couldn’t store <> in Calculated_Service Column. Expected type is Int32.
I’m thinking its all the + used in trying to connect the strings and those are not int data types.
That error would come from your calculated field being set to Int instead of String. I thought you were trying to go from 0s and 1s to the string format?
Got it to work! Thanks!