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

Any one tell export Sql Database in access hole object

hi All
I want to export my mssqlserver 2000 database with all relation ship and all
default value and all data save in sqlserver Database (I want to export hole
data base object ) in MS Access database is it possible
if it is possible from mssqlserver please tell me if it is possible from any
other third party tool then please tell me
thanks in advanced
from
khurram
I am not aware of tool that does a complete transfer of all objects from SQL
Server to MS Access. You can certainly use SQL Server DTS to move data to
Access. You might want to post this to an Access group as well.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"khurram alam" <khurram.alam@.eintelligencesoft.com> wrote in message
news:O1N0qQnZEHA.3988@.tk2msftngp13.phx.gbl...
hi All
I want to export my mssqlserver 2000 database with all relation ship and all
default value and all data save in sqlserver Database (I want to export hole
data base object ) in MS Access database is it possible
if it is possible from mssqlserver please tell me if it is possible from any
other third party tool then please tell me
thanks in advanced
from
khurram
|||Hi,
You can either use Upsizing wizard or SQL DTS to upgrade objects/data from
Access to SQL server.
The details about the tools and steps are detailed in the below
knowledgebase article.
http://support.microsoft.com/default...b;en-us;237980
Thanks
Hari
MCDBA"khurram alam" <khurram.alam@.eintelligencesoft.com> wrote in message
news:O1N0qQnZEHA.3988@.tk2msftngp13.phx.gbl...
> hi All
> I want to export my mssqlserver 2000 database with all relation ship and
all
> default value and all data save in sqlserver Database (I want to export
hole
> data base object ) in MS Access database is it possible
> if it is possible from mssqlserver please tell me if it is possible from
any
> other third party tool then please tell me
> thanks in advanced
> from
> khurram
>