<cfquery datasource="askseaton" name="getOffices">
select
iif(lm.user_id = s.lead_mgr, (lm.first + ' ' +
lm.last) , s.lead_note) as leadMgr,
iif(lm.user_id = s.AREA_MGR_RDO,
lm.first + ' ' + lm.last , s.Area_Note) as director
,
iif(lm.user_id = s.sdir_ndir, lm.first
+ ' ' + lm.last , s.sdir_note) as sndir
,
iif(lm.user_id = s.vice_pre, lm.first +
' ' + lm.last , s.vice_note) as vicePre
,
iif(lm.user_id = s.fast_mgr, lm.first +
' ' + lm.last , s.fast_note) as fastMgr
,
from sourcebook_1 s,user_info lm
where s.active = 1 and (lm.user_id = s.lead_mgr or
lm.user_id = s.AREA_MGR_RDO or lm.user_id = s.sdir_ndir or lm.user_id =
s.vice_pre or lm.user_id = s.fast_mgr)
and s.office_id not in(36,37,38,73)
order by s.office_number
</cfquery>
i am writing like this, it is working fine, thanks for your response....
>I think you want it this way, using a CASE statement for the leadmgr
>column. I also moved all your subselects into left joins, which should
>give a bit better performance.
>
><cfquery datasource="askseaton" name="getOffices" result="varibles">
>select
> s.address + ' ' + s.address2 + ' ' + s.address3 + ',' + s.city + ',' +
>s.state + ',' + s.city as office_address,
> leadmgr = case
> when (lm.user_id is null) then lm.lead_note
> else lm.first + ' ' + lm.last
> end,
> amr.first + ' ' + amr.last as director,
> sn.first + ' ' + sn.last as SDIRNDIR,
> vp.first + ' ' + vp.last as VICEPRE,
> fm.first + ' ' + fm.last as FASTMGR
>from sourcebook_1 s left outer join
> user_info lm on s.lead_mgr = lm.user_id left outer join
> user_info amr on s.area_mgr_rdo = amr.user_id left outer join
> user_info sn on s.sdir_ndir = sn.user_id left outer join
> user_info vp on s.vice_pre = vp.user_id left outer join
> user_info fm on s.fast_mgr = fm.user_id
>where active = 1
> and office_id not in (36,37,38,73)
>order by office_number
></cfquery>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324803
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
