How to combine query with concatenation

[ry1633]

Hi all,

In Framework Manager, is it possible to combine a query with concatenation.   I have three fields for species in my model; ID, SPECIES_CODE, and SPECIES_NAME.   I have them concatenated together for ease of use for my users since they like to see it that way;

[ID]  + '-' + [SPECIES_CODE] + ' '  + [SPECIES_NAME]

What I would also like to be able to do it is query for all the species codes that are aquamarine animals (codes like "FISH", "ALLIGATOR") and put them into their own concatenated string like above.   Is this possible?

[bdbits]

Not sure I follow... you mean on the report you want a single data item on each row for say all aquamarine animals that looks like "FISH, ALLIGATOR"? Is the fact that they are aquamarine indicated by something in the database?

[ry1633]

no, the aquamarine designation is a part of the external report requirements from my users.   What I'd like do I query for all those items and then present them in a concatenated string.   So the users can see them together as one item instead of having to use separate columns.

[bdbits]

What I would also like to be able to do it is query for all the species codes that are aquamarine animals (codes like "FISH", "ALLIGATOR") and put them into their own concatenated string like above.   Is this possible?

So what are you going to query to determine which are aquamarine? From some prompts? Sorry, I guess I am not following.

If they are data items, telling you how to go about it will depend on the representation in the database. If they are literals you want to put into a query subject that is straightforward. A lot here depends on where the information is coming from. At least in my mind.

[ry1633]

yeah I'm assuming it would be something like a CASE statement, where SPECIES_CODE would contain 'ALLIGATOR', 'FISH' etc,

but then I'm not sure how to pass the result of that into a concatenated string?