Maximo - Return multiple values based on another field using List Where Clause in Domain

Question

I know I can't return multiple values with the Case statement but this is the best way I can explain what I want to accomplish. I am trying to write a statement where I will return different values based on what is entered in another field. I have something like this currently:

SELECT animal WHERE CASE WHEN :textbox is not null THEN (SELECT animal from animalsTable where animalType = :textbox ELSE (SELECT plant from plantsTable where plantType = 'edible')

So basically, I want to be able to list all the animals that correspond to what the user types in the textbox, but if they do not enter anything in the textbox, then I want to show them all plants that are edible instead. I almost always going to return multiple values for each value they enter.

For example, if the user types 'dog' then i will return 'dog' and 'wolf'. So this causes a problem since the case statement is boolean. How can I get around this?

Thanks.

score 0 · Answer 1 · answered Jan 12 '15 at 21:10

0

You could check the textbox value for each table and union the results:

select animal from animalsTable where animalType = :textbox 
and :textbox is not null
union all
select plant from plantsTable where plantType = 'edible' 
and :textbox is null

answered Jan 12 '15 at 21:10

Gerrat

28,863
9
73
101

that example itself works for me when i tested it but in maximo, i am already in a `select top 1000 where` statement and getting the error _An expression of non-boolean type specified where condition expected_. This is basically the same issue that i ran across with the using a `case` statement i believe – Jarnar591 Jan 13 '15 at 13:20
@Jarnar591: It's not the same issue. The syntax in your question is invalid. Mine is valid. Your issue now likely has to do with how you're specifying: `select top 1000`. I'm not familiar with Maximo. Is that even valid? You might want to specify your query using an `order by` and `fetch first N rows` as in [this example](http://stackoverflow.com/questions/2850423/how-to-do-sql-select-top-n-in-as400) – Gerrat Jan 13 '15 at 15:02
i actually got this working now. the code you provided is fine but i realized that if the textbox is empty, then i am returned an empty string and not a null value. using = or <> '' ended up working. Thank you for your answer though! – Jarnar591 Jan 13 '15 at 15:07
Maximo supports DB2, SQL Server, and Oracle. `select top 1000` is syntax accepted by SQL server. – Sun Jan 23 '15 at 17:32

score 0 · Accepted Answer · answered Dec 12 '16 at 17:35

You can create an ALN Domain that contains all your lookup value list. In this case, both animals and plants. Then create a table domain that references ALN domain based on the key field you want to filter by. You'll need store this key in the Description field as a single value, or multiple values separated by a space or comma.

For us, we used a custom field (subcategory) that displayed a limited lookup from the ALN Domain using a Table domain filtering on the asset department number. The Asset Department Number is listed in the ALN Domain description.

ALN domain contains your plant and animal values. Description of ALN domain contains your key field vale.

If the Asset Department is empty, then entire list shows up.

The list where clause looks something like this:

domainid='CBRSUBCAT' and description like '%' || (select eq5 from asset where assetnum = :assetnum) || '%'

I used a like so we could enter multiple departments for one subcategory separated by comma. For you, you could use description = (equals) if you wanted.

Maximo - Return multiple values based on another field using List Where Clause in Domain

2 Answers2