Using If/Then


(William) #1

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.


(Jose C Gomez) #2

Please provide the code so we can look at what you are trying to do ?

-Jose


(William) #3

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


(Jose C Gomez) #4

This is working as designed , a case statement once a condition is true the case statement is done and breaks out.

-Jose


(William) #5

That’s what I was thinking. So thoughts on how to achieve what I am looking for?


(William) #6

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.


(Mitchell Rainsford) #7

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 + …


(William) #8

Yes,it is. It the SQL Editor in Epicor.


(Jose C Gomez) #9

You are going to have to make a calculated field for each of those cases

-Jose


(William) #10

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?


(William) #11

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.


(Mitchell Rainsford) #12

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?


(William) #13

Got it to work! Thanks!