simplify query

[peac3]

Hi guys,

Please have a look at the codes below:

select std.intern_instrument_rating, std.guarantor_pd, std.mitigant_lgd, std.object_key_value, std.lot_id, 
std.object_origin, fg.facility_grade_type,std.char_cust_element3, fg.object_key_value,std.mitigant_exposure_class,
case when ms.secc_type = 'Internal : Basic Scale <= 500k' then 1 
            when ms.secc_type = 'Internal : Expanded Scale >500k' then 2
            else 'error' end as grade_conv,
std.intern_instrument_rating_type             
from maintable std
join
t_risk_object_facility_grade fg
on std.object_key_value=fg.object_key_value
and std.object_origin = fg.object_origin
and std.intern_instrument_rating_type = fg.facility_grade_type
join
etl_staging_db..s_master_scale_backup ms
on ms.secc = std.intern_instrument_rating
and case when ms.secc_type = 'Internal : Basic Scale <= 500k' then 1 
            when ms.secc_type = 'Internal : Expanded Scale >500k' then 2
            else 'error' end = std.intern_instrument_rating_type
and (ms.lgd <> std.mitigant_lgd or ms.pd <> std.guarantor_pd)
order by 1

there are 2 cases statements in this select which looks amateur query ,
one is in select and one is in join,
is there a way to change case statement become variable or something.

My short question would be how to simplify this.

Thanks in advance,

 

[markros]

Your expressions in the case statement are of the different type, which is the problem in itself.

Take a look at this blog:

http://blogs.lessthandot.com/index....erver/sql-server-case-when-data-type-problems

PluralSight Learning Library

 

[peac3]

Hi markros,

The case statement are NOT different data type,
1 and 2 are the same data type.


Anyway, forget about data type.
this query is working fine, I have tested it.

Do you know a way to simplify this query?

 

[markros]

Look closer at your case statement. You have 1, 2 and 'Error' in the ELSE case.

The simplification you can use is to put m table as derived table and create calculated field, then join with derived table based on this new field.


PluralSight Learning Library