Query subject - concatenation not showing data

[ry1633]

I have a concatenated query subject in my model that isn't showing data.  It's called 'Prod-Serial', it is a concat of a Product Code and a Serial, and it is not showing data correctly all the time.  There is data in both columns,  but in Cognos the concatenated field either is blank, or just shows Product Code with no serial like  'AAAA-  '     There is real data in each individual column, and the concat doesn't throw any errors.

[ry1633]

Here's an example.   you can see it did grab the serials at least 6 of them.  But it doesn't work for the empty serials.  It should also concat everything even the ones no serial, like '16216-'  and '4801-'   Do I need to aggregate them somehow; like Count or Count Distinct?  Or is there something I should do?   The query subject is just a straight concat:


[view name].[Product ID] || '-' || [view name].[Serial Number]

[Lynn]

Are these two items in the same row on the same view or is there some type of join going on? I'm confused about your thought of aggregating them since these seem like attributes (not facts) and therefore not something you'd aggregate. If you look at the generated SQL you might get some clues about what is going on.

Are these values numeric or strings? If they are numeric maybe there is some implicit conversion going on that causes a problem, in which case explicitly casting to varchar might help.

Is the serial number a null or a blank? You can use a coalesce function on the serial number to see if it is null and causing a problem.

[view name].[Product ID] || '-' || coalesce( [view name].[Serial Number], 'NONE' )

[ry1633]

Maybe I don't need to aggregate them, I probably misspoke.

The serial number column is mostly nulls with only a few entries in it.

[ry1633]

Here's the results of the coalesce like you suggest:

[ry1633]

By jove, I think I just might have figured it out in spite of myself.  :)    I wrote it like this:   [view].[Product ID] || '-' || coalesce([view].[Serial Number], '  ') and it looks like this which I think is what I want.

[Lynn]

Excellent news! Nulls are funny critters. A null is considered unknown so combining it with anything else results in unknown. Replacing the unknown with a value, via the coalesce function, allows the end result to be determined.

[ry1633]

I'd never encounter the coalesce function before - thanks for the help.  I will have to read up on that one.

[Lynn]

It picks up the first non-null argument from the provided parameters. An easy way to substitute null for something else which is more compact than an "if" or "case" statement.

If you do any modelling for multi-fact queries you often find the two fact queries are combined with a full outer join and use of the coalesce functions. This is called a stitch query and is covered in the FM user guide. Might provide additional context with specific examples beyond the basic DBMS documentation of the function.

[the6campbells]

check your data to ensure you are using non null values

null || non-null = null
non-null || null = null
null || null = null
non-null || non-null = <value>

keep in mind, even if your DBA claims they enforce non-null values via column constraints that an outer join returns null values

A left outer join B will project null values for any column of B that your query returns and similar re right and full outer

coerce non-null value using coaleace()