Expression in set analysis with null condition and multiple if else is condition.

[Anand1521]

Hi All, 

I am using straight table chart to display a column.

I want show this column by checking few condition.

Example :

In my Case I have One Territory_Id against this territory there are two sales persons, one Field person and another one his reporting person.

I want to put one condition that if the Feild person is there in the data then i dont want to show his reporting manager in the dimension.If the field person is not there then only it shoud show his reporting manager name.

In SQL

begin 

Select person_name from employees into variable v1

where territory_id =  123

and flag =  'Field  Person'

if v1 is null

then 

begin

Select person_name from employees into variable v2

where territory_id =  123

and flag =  'Field  Person Reporting';

end;

If v1 is not null then return V1

else return v2;

End if;

end ;

[vinieme12]

Use the Coalesce() function 

Coalesce returns the first non-null value

Select   territory_id    

,Coalesce ( FieldPerson ,  FieldPersonReporting ) as PersonName    <<-- will return FieldPErsonReporting if fieldPerson is null

FROM (

Select     territory_id    

, max( Case When flag =  'Field  Person' Then person_name)  as FieldPerson

, max( Case When flag =  'Field  Person Reporting' Then person_name)  as FieldPersonReporting

from employees 

where territory_id =  123 )

[Anand1521]

Hi , Vineeth Thanks for the reply.

I am  trying below statement on dimension column still it is showing both the FLSP and RSM. 

=Coalesce((aggr(ONLY({<Flag_RM={'FLSP'}>}RESOURCE_NAME),RESOURCE_NAME)),
(aggr(ONLY({<Flag_RM={'RSM'}>}RESOURCE_NAME),RESOURCE_NAME))
)

Thanks,

Anand