2012年3月20日星期二

Any way to make this shorter?

I have a T-SQL query that is used to pull up some data for once-a-day export, just out of curiosity more then anything, is there a way to make this shorter?

SELECT DISTINCT
u.userId,
u.lastName,
u.firstName,
u.address1,
u.address2,
u.city,
u.state,
u.zip,
CASE WHEN u.UserClassID_fk BETWEEN 1 AND 3 AND COALESCE(u.RetailerNumber_fk,0)= 0
THEN 'Corporate'
WHEN u.UserClassID_fk BETWEEN 1 AND 3 AND COALESCE(u.RetailerNumber_fk,0)<> 0
THEN 'RETAILER'+ CONVERT(varchar, COALESCE(u.RetailerNumber_fk,0))
WHEN u.UserClassID_fk BETWEEN 4 AND 8 AND COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0) = 0
THEN 'Corporate'
WHEN u.UserClassID_fk BETWEEN 4 AND 8 AND COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0) <> 0
THEN 'PLANT'+ CONVERT(varchar, COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0))
WHEN u.UserClassID_fk BETWEEN 9 AND 14 AND COALESCE(p.regionNumber_fk,rg.regionNumber,0) = 0
THEN 'Corporate'
WHEN u.UserClassID_fk BETWEEN 9 AND 14 AND COALESCE(p.regionNumber_fk,rg.regionNumber,0) <> 0
THEN 'REGION'+ CONVERT(varchar, COALESCE(p.regionNumber_fk,rg.regionNumber,0))
END CPGkey
FROM [...] JOIN [...]

I'm hoping there is something in a way of...
If userClass Between 1 and 3 Then
IF COALESCE(u.RetailerNumber_fk,0)= 0 Then 'Corporate'
ELSE 'RETAILER'+ CONVERT(varchar, COALESCE(u.RetailerNumber_fk,0))
End IF as CPGKey,

Thanks in advace.Not really. This is a little more concise, as it avoids duplicating comparisons:CASE WHEN u.UserClassID_fk BETWEEN 1 AND 3 THEN
CASE WHEN COALESCE(u.RetailerNumber_fk,0)= 0 THEN 'Corporate'
ELSE 'RETAILER'+ CONVERT(varchar, COALESCE(u.RetailerNumber_fk,0)) END
WHEN u.UserClassID_fk BETWEEN 4 AND 8 THEN
CASE WHEN COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0) = 0 THEN 'Corporate'
ELSE 'PLANT'+ CONVERT(varchar, COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0)) END
WHEN u.UserClassID_fk BETWEEN 9 AND 14 THEN
CASE WHEN COALESCE(p.regionNumber_fk,rg.regionNumber,0) = 0 THEN 'Corporate'
ELSE 'REGION'+ CONVERT(varchar, COALESCE(p.regionNumber_fk,rg.regionNumber,0)) END
END CPGkeysql

没有评论:

发表评论