hide null values with coalesce statement

[ry1633]

Hi all,

I have query subject concatenation with a violation code part, section, and three sub-sections.    I need the coalesce statement for the null values, because if I don't use it Cognos will chop out a lot of codes that should be in there.  But my users want to hide the sub-sections when they are null.   Below is my current query subject expression; then below that is an example of what the users want to see:

[Database Layer].[CODE_TABLE].[PART]
|| '.'  ||
[Database Layer].[CODE_TABLE].[SECTION]
|| '(' ||
coalesce([Database Layer].[CODE_TABLE].[SUBSECTION_1], ' ' )
|| ')(' ||
coalesce([Database Layer].[CODE_TABLE].[SUBSECTION_2], 0 )
|| ')(' ||
coalesce([Database Layer].[CODE_TABLE].[SUBSECTION_3],  ' ' )
|| ')'


Examples of what my user wants to see:

102.5(c)(1)( )  -- This example would be 102.5(c)(1)

113.450(h)(2)(i) -- This one would be 113.450(h)(2)(i)

[cognostechie]

Use a Case statement to check if the value is null and if it is then display '' otherwise display the value

[ry1633]

Would it be something like this?      I'm afraid if I don't use the coalesce then I won't get good data to show up.

[Database Layer].[CODE_TABLE].[PART]
|| '.'  ||
CASE WHEN [Database Layer].[CODE_TABLE].[SECTION] IS NULL then ''
ELSE [Database Layer].[CODE_TABLE].[SECTION]
|| '(' ||
CASE WHEN [Database Layer].[CODE_TABLE].[SUBSECTION_1] IS NULL then ''
ELSE
coalesce([Database Layer].[CODE_TABLE].[SUBSECTION_1], ' ' )
|| ')

[ry1633]

These five fields comprise a certain code.   I'd like to have a full concatenation of the five (5) fields:  Part, Section, Subsection1, Subsection2, and Subsection3.    Of those five fields,  four can have null values - Section, Subsection1, Subsection2, and Subsection3.    I'd like to be able to hide the null value if applicable,  and do it all within the same concatenation.

[cognostechie]

[Database Layer].[CODE_TABLE].[PART]
||
Case
    When [Database Layer].[CODE_TABLE].[SECTION] is null
    Then ''
    Else ' (' ||  [Database Layer].[CODE_TABLE].[SECTION]  || ' )'
End
||
Case
    When [Database Layer].[CODE_TABLE].[SUBSECTION_1]   is null
    Then ''
    Else ' (' ||  [Database Layer].[CODE_TABLE].[SUBSECTION_1]  || ' )'
End
||
Case
    When [Database Layer].[CODE_TABLE].[SUBSECTION_2]   is null
    Then ''
    Else ' (' ||  [Database Layer].[CODE_TABLE].[SUBSECTION_2]  || ' )'
End
||
Case
    When [Database Layer].[CODE_TABLE].[SUBSECTION_3]   is null
    Then ''
    Else ' (' ||  [Database Layer].[CODE_TABLE].[SUBSECTION_3]  || ' )'
End

[ry1633]

thanks I will give that a shot today.    I was having a hard time figuring out the syntax - sometimes Cognos is really temperamental like that.

[ry1633]

When I go to test that expression it throws this error 'UDA-EE-0094 The operation "add" is invalid for the following combination of data types: "quadword" and "varchar"'

But it doesn't say which piece or line is the offending one.

[ry1633]

Looking at the datatypes for each:  Part, Section, and Sub-Section2 are Numbers, and Sub-Sections1/3 are VarChars.    How should I handle them?   

I'd probably have to CAST them as something.  Then I wonder if I should bring them into the Logic Layer, CAST them each individually and then build the concatenation that way?   Unless there might be a better way?

[bdbits]

I would cast the numbers to varchar, but yes you'll have to cast them.

If this expression will be used regularly, I would persist it into the database during ETL so it does not have to be evaluated every time. If that is not an option, I would normally put it in the business layer. The database layer should be little more than "SELECT *" statements.

[ry1633]

I'll talk to my dba and see what he thinks.  What I can do is bring the fields into the business layer, cast them there, and build the concatenation off of those.

[cognostechie]

Yes, you can cast the fields that are numbers into varchars and leave the fields that are varchars as is. bdbits was talking about doing this in ETL which would be done by the ETL developers, probably not the DBA. However, the table design also seems to be flawed as Section and subsection2 are numbers and subsection1/3 are varchars. Are you looking at the field type in FM or the table? If it is like that in the table then somebody designed it based on the data of those fields which is not the right thing to do as the data of subsections might include a character later on even if it is not so right now. I think all those should be varchars in the table.

[bdbits]

It could be his DBA is the ETL 'developer'.

There is a school of data warehousing thought that says everything in dimensions should be defined as strings (varchars or whatever) other than of course the surrogate keys they also believe should always be there. The only exception would be the date dimension (with all its role playing glory). Fact tables are only ever foreign keys and the numbers (facts) themselves. I think there is some validity to the idea, but we do not always have the luxury of academic purity in our daily realities, especially if we do not have the final say. And sometimes, it is just easier on us all to break this train of thought.

[cognostechie]

Yes, I have heard of that but then Mr. Kimball also says that it is perfectly fine to have measures in the dimension if that is the only place they belong (ex: Creditlimit in a Customer dimension and Weight or Area in a Product dimension) These measures need to be accumulated in the report and they do not belong in the Fact table.