case end 别名

[qrealiny]

SELECT a.*, b.usergroupname_3 AS usergroupname_, a.usernm_3 AS usernm_,
CASE
WHEN a.usertype = '1'
THEN (SELECT companyshortnm_3 FROM companymst WHERE companymst.companycd =a.companycd)
WHEN a.usertype = '2'
THEN (SELECT customershortnm_3 FROM customermst WHERE customermst.customercd =a.companycd)
END AS companyshortnm_
FROM usermst a LEFT JOIN usergroupmst b ON a.usergroupid = b.usergroupid
WHERE a.companycd IS NOT NULL
AND a.usertype = '1'
AND a.companycd LIKE '%SHDLDSJ%'
--AND a.companyshortnm_ like '%支所4%' 此行为什么说无效的标识符呢，我在end 的后面不是指定别名为companyshortnm_了么

难道不可以这么用么

[dawugui]

改为companyshortnm_ like '%支所4%'

[pt1314917]

select * from
(SELECT  a.*, b.usergroupname_3 AS usergroupname_, a.usernm_3 AS usernm_, 
        CASE 
            WHEN a.usertype = '1' 
              THEN (SELECT companyshortnm_3 FROM companymst WHERE companymst.companycd =a.companycd) 
            WHEN a.usertype = '2' 
              THEN (SELECT customershortnm_3 FROM customermst WHERE customermst.customercd =a.companycd) 
        END AS [companyshortnm_]        
  FROM usermst a LEFT JOIN usergroupmst b ON a.usergroupid = b.usergroupid 
  WHERE a.companycd IS NOT NULL 
    AND a.usertype = '1' 
    AND a.companycd LIKE '%SHDLDSJ%')a 
where [companyshortnm_] like '%支所4%' 

[csdyyr]

SELECT * FROM (
SELECT  a.*, b.usergroupname_3 AS usergroupname_, a.usernm_3 AS usernm_,
        CASE
            WHEN a.usertype = '1'
              THEN (SELECT companyshortnm_3 FROM companymst WHERE companymst.companycd =a.companycd)
            WHEN a.usertype = '2'
              THEN (SELECT customershortnm_3 FROM customermst WHERE customermst.customercd =a.companycd)
        END AS companyshortnm_       
  FROM usermst a LEFT JOIN usergroupmst b ON a.usergroupid = b.usergroupid
  WHERE a.companycd IS NOT NULL
    AND a.usertype = '1'
    AND a.companycd LIKE '%SHDLDSJ%'
  --AND a.companyshortnm_ like '%支所4%' 
) WHERE companyshortnm_  like '%支所4%' 

[dawugui]

不过即使改为上面的,你的语句然后不对.你这个语句得使用判断语句或动态SQL来完成.

[等不到来世]

select * from
(
	SELECT  a.*, b.usergroupname_3 AS usergroupname_, a.usernm_3 AS usernm_, 
		CASE 
			WHEN a.usertype = '1' 
			  THEN (SELECT companyshortnm_3 FROM companymst WHERE companymst.companycd =a.companycd) 
			WHEN a.usertype = '2' 
			  THEN (SELECT customershortnm_3 FROM customermst WHERE customermst.customercd =a.companycd) 
		END AS companyshortnm_        
	FROM usermst a LEFT JOIN usergroupmst b ON a.usergroupid = b.usergroupid 
	WHERE a.companycd IS NOT NULL 
	AND a.usertype = '1' 
	AND a.companycd LIKE '%SHDLDSJ%' 
) t
where companyshortnm_ like '%支所4%'

[李冬宝]

确实不能这样用。
因为你的别名是在输出的结果集中，而不是表a

[StrutHibernate]

不过即使改为上面的,你的语句仍然不对.你这个语句得使用判断语句或动态SQL来完成.