conditionally set selected column in union all

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

conditionally set selected column in union all

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-05-21 : 14:28:33

I have three selects connected by UNION ALL selecting data "for xml explicit".

One of the columns in the middle select should be set to a hardcoded value depending on the answer to a question stored in another table in the db.

What would be the best way to go about this?

Thanks!

ls.valid should be set to 'Y' if a question is answered yes.
Here is the SELECT from the stored procedure:

select 1 as tag,
null as parent,
d.first_name as [pos_driver!1!first_name!element],
d.last_name as [pos_driver!1!last_name!element],
d.birth_date as [pos_driver!1!birth_date!element],
d.driver_number as [pos_driver!1!driver_number!element],
d.sr22_flag as [pos_driver!1!sr22_flag!element],
d.sr22a_flag as [pos_driver!1!sr22a_flag!element],
p.company as [pos_driver!1!company!element],
p.policy_type as [pos_driver!1!policy_type!element],
p.program as [pos_driver!1!program!element],
p.policy_state as [pos_driver!1!policy_state!element],
isnull(v.fr4_flag, 'N') as [pos_driver!1!fr4_flag!element],
isnull(d.sr50_flag, 'N') as [pos_driver!1!sr50_flag!element],
null as [License_Status!10!Status!element],
null as [License_Status!10!Valid!element],
null as [License_Class!20!DMVClass!element],
null as [License_Class!20!Class!element]
from pos_policy p
join pos_driver d
on p.pos_id = d.pos_id
left join (select @pos_id as pos_id, 'Y' as fr4_flag
where exists ( select 1 from pos_vehicle
where pos_id = @pos_id
and len(fr4_number) > 0)) v
on p.pos_id = v.pos_id
where p.pos_id = @pos_id
and driver_number = @driver

union all

select 10, 1,
/* pos_driver & pos_policy*/
null,null,null,null,null,null,null,null,null,null,null,null,
upper(ls.status), ls.valid,
/* license_Status*/
null, null
from license_status ls, pos_driver pd, pos_policy pp
where pp.pos_id = @pos_id
and pd.pos_id = pp.pos_id
and pd.driver_number = @driver
and ls.company = pp.company
and ls.license_state = pd.license_state

union all

select 20, 1,
/* pos_driver & pos_policy*/
null,null,null,null,null,null,null,null,null,null,null,null,
/* license_Status*/
null, null,
/* license_Status*/
DMVClass, Class
from license_class lc, pos_driver pd
where pd.pos_id = @pos_id
and pd.driver_number = @driver
and lc.license_state = pd.license_state

for xml explicit

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2009-05-21 : 19:33:06

Did you try something like this?


select 10, 1,
/* pos_driver & pos_policy*/
null,null,null,null,null,null,null,null,null,null,null,null,
upper(ls.status), ls.valid = CASE WHEN othertable.column = 1 THEN 'Y' ELSE null END,
/* license_Status*/
null, null 
from license_status ls, pos_driver pd, pos_policy pp
where pp.pos_id = @pos_id
and pd.pos_id = pp.pos_id
and pd.driver_number = @driver
and ls.company = pp.company
and ls.license_state = pd.license_state
and ls.column = othertable.column

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources