If statement for multiple fields

[koolskins]

Informix 10 on an Informix Database. I have a vendor table with ten columns that are used to denote a special classification for the vendor. Each vendor record will have a "Y" in one of these columns with the rest being null. This is in lieu of having one column with ten potential values, but don't go there. I need to run a report where these ten columns are grouped into five distinct categories. My statement looks like this:

if {vn_sadbus_765.large}="Y" then "LARGE" else
if {vn_sadbus_765.small}="Y" then "SMALL" else
if {vn_sadbus_765.wmn}="Y" then "WOMAN OWNED" else
if {vn_sadbus_765.sdvo}="Y" then "SMALL DISADVANTAGED" else "OTHER"

When I run the report, I only get LARGE to show and everything else is falling into a null group. I assume the nulls in the columns are screwing me up and I'm only getting LARGE to print because it is first column being tested. What is the simplest way to deal with this?

Thanks.
RW

 

[synapsevampire]

If it's nulls, try:

if not(isnull({vn_sadbus_765.large}))
and
{vn_sadbus_765.large}="Y" then "LARGE"
else
if not(isnull({vn_sadbus_765.small}))
and
{vn_sadbus_765.small}="Y" then "SMALL"
else
... you get the idea ...

-k

 

[koolskins]

Yep, that was it. Thanks.

Rw

 

[koolskins]

A small problem has cropped up with this solution. Apparently, many of the vendors have more than one classification checked, but the report is not reporting the same vendor under the multiple categories. Any ideas on why this might be happening? Thanks.

 

[Turkbear]

Hi,
The formula will assign the 'last' value found ..

To be sure of getting all of them, you will need to test for every combination and create the correct decode for each..

Or,
Try concatenating to 1 string variable ( something like):

@WhatKind
StringVar Classification := "";
if not(isnull({vn_sadbus_765.large}))
and
{vn_sadbus_765.large}="Y" then Classification = Classification +  "LARGE " 
else
if not(isnull({vn_sadbus_765.small}))
and
{vn_sadbus_765.small}="Y" then  Classification = Classification +  "SMALL " 
else 
if not(isnull({vn_sadbus_765.wmn}))
and
{vn_sadbus_765.wmn}="Y" then Classification = Classification +  "WOMAN OWNED " else
if not(isnull({vn_sadbus_765.sdvo}))
and
if {vn_sadbus_765.sdvo}="Y" then Classification = Classification + "SMALL DISADVANTAGED " 
else "OTHER"

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[koolskins]

Thanks for the input. I'm trying the string variable but am getting the error, THE KEYWORD 'THEN' IS MISSING with the cursor being placed on the last line (blank line under 'else 'OTHER').

 

[Turkbear]

Hi,
There may have been a typo in my posting ( may be more, check carefully - especially the Parens):

@WhatKind
StringVar Classification := "";

(if not(isnull({vn_sadbus_765.large}))
and
{vn_sadbus_765.large}="Y")
 then 
 Classification = Classification +  "LARGE " 
else
(if not(isnull({vn_sadbus_765.small}))
and
{vn_sadbus_765.small}="Y")
 then 
 Classification = Classification +  "SMALL " 
else 
(if not(isnull({vn_sadbus_765.wmn}))
and
{vn_sadbus_765.wmn}="Y")
 then
 Classification = Classification +  "WOMAN OWNED " 
else
(if not(isnull({vn_sadbus_765.sdvo}))
and
if {vn_sadbus_765.sdvo}="Y")
 then
 Classification = Classification + "SMALL DISADVANTAGED " 
else
 "OTHER"
//May need this as well
Classification

Hope it works..It will probably need some tweeking to be sure that Classification ( the variable) is returned, not just True or False.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Since a record can only appear in one group, you will not get records to appear in multiple groups. You could change your group structure to reflect the possible combinations of records, but then you would need to write a formula like:

(if isnull({vn_sadbus_765.large}) then "" else "Large") +" "+
(if isnull({vn_sadbus_765.small}) then "" else "Small") + " "+
(if isnull({vn_sadbus_765.wmn}) then "" else "Woman-Owned") +" "+ //etc.

-LB

 

[koolskins]

Thanks guys. I ended up temporarily going another route where I made a runnint total to add the costs for each of the categories where the sadbus catetory = "Y". Of course, I had to to remove the SADBUS category grouping which then killed the chart, but I'm working on a view to do the accumulation at the database level which I think will ultimately be the best solution.