Conditional Formula

[Shtinky]

DB2/Crysal 8.5 SP3

Hello,
I am working on a delivery receipt report for a transportation company. I am having a little bit of a problem with a Hazardous materials description that I'm trying to create. Each and every good that is defined as Hazardous by the United Nations is given a unique "UN" number (UN3270 for example). Each UN number has a bunch of different descriptions and classes, depending on the type of hazardous material.

Here is my formula:
{UNCODES.SHIPPING_NAME} + "," + " " + {UNCODES.CLASS} + "." + {UNCODES.SUBSIDIARY_CLASS} + "," + " " +{UNCODES.UN_NUMBER} + "," + " " + "PG" + " " +{UNCODES.PACKING_GROUP}

Here is the result:
NITROCELLULOSE MEMBRANE FILTERS, with not more than 12.6 percent nitrogen, by dry mass, 4.1, UN3270, PG II

But, there's a bit of a problem and I'm not quite sure how to deal with it. Not all UN Numbers have the same level of description. A good example is that not all UN codes have a {UNCODES.PACKING_GROUP} number. I am trying to figure out a way to make a particular piece of this string dissapear when a particular field is null. Does that make sense?

So if I have a UN number that does not have a {UNCODES.PACKING_GROUP} number, the result looks like this:

AEROSOLS, containing compressed oxygen, 2.2, UN1950A, PG

In this case, since {UNCODES.PACKING_GROUP} is null, I'd like the "PG" portion of the string to suppress. Is this sort of thing possible?

Instead, I'd like it to read like this:
AEROSOLS, containing compressed oxygen, 2.2, UN1950A

If anyone has any suggestions, I would appreciate hearing from you. Thank you for taking the time to read my post.

The shtinkster

 

[synapsevampire]

Sure, try:

if isnull({UNCODES.PACKING_GROUP})
or
trim({UNCODES.PACKING_GROUP}) = "" then
{UNCODES.SHIPPING_NAME} + "," + " " + {UNCODES.CLASS} + "." + {UNCODES.SUBSIDIARY_CLASS} + "," + " " +{UNCODES.UN_NUMBER}
else
{UNCODES.SHIPPING_NAME} + "," + " " + {UNCODES.CLASS} + "." + {UNCODES.SUBSIDIARY_CLASS} + "," + " " +{UNCODES.UN_NUMBER} + "," + " " + "PG" + " " +{UNCODES.PACKING_GROUP}

-k

 

[Shtinky]

That worked great, thanks!