Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    46

    Unanswered: 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?

    Code:
    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.plantNumber_fk,0) = 0
    	THEN 'Corporate'
    WHEN u.UserClassID_fk BETWEEN 4 AND 8 AND COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNumber_fk,0) <> 0
    	THEN 'PLANT'+ CONVERT(varchar, COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNumber_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.
    Last edited by lito; 08-01-06 at 19:28.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Not really. This is a little more concise, as it avoids duplicating comparisons:
    Code:
    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.plantNumber_fk,0) = 0 THEN 'Corporate'
    		ELSE 'PLANT'+ CONVERT(varchar, COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNumber_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 CPGkey
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •