SQLTeam.com | Weblogs | Forums

Ms sql dynamic query help


#1

Hi all,

I am trying to populate below query's LayoutInformation with these dynamic uids (UID_PWODecisionSubMethod,UID_PWODecisionStep) . At example below 0 zero is levelNumber that is also dynamic and the dynamic key uid of UID_PWODecisionStep (wrong key ->5daf8159-130b-4e3b-b4bf-96283c11251c that should be replaced with current db rows key of d293905e-3f43-4a45-bb82-0a9305d39eeb vice and verse) , and <Element Key="5db6f41e-fbe1-47e5-8bad-332a62ce685f" that is also dynamic and should be replace with UID_PWODecisionSubMethod with "current db rows" uid of a27a1df1-155f-4bba-8cbe-b395252bdbaf.

<Layout><Element Key="0|5daf8159-130b-4e3b-b4bf-96283c11251c" Location="554, 82" /><Element Key="5db6f41e-fbe1-47e5-8bad-332a62ce685f" Location="457, 0" /><Element Key="1|0aea2b9b-a9a5-4837-928d-7caaff7c411c" Location="144, 118" /><Element Key="2|63e5a539-7db8-40e3-9a1c-de9aa0a2ed69" Location="337, 295" /><Element Key="3|2fe62040-dc9f-40b8-a374-4147b61cc123" Location="675, 504" /><Element Key="4|badf28ed-e3a6-4a45-a930-c8118f481e34" Location="337, 536" /><Element Key="5|e73c6dc3-ecf7-44c6-8604-62d774c820e0" Location="680, 667" /><Element Key="6|bb2e81d2-a047-4c82-8f9b-8a42963d0351" Location="542, 778" /><Element Key="7|09bd8b32-df7c-4b25-a250-75d6a041ee2a" Location="713, 1063" /><Element Key="8|0d49cf82-9112-40a3-af18-9d5beef87aaa" Location="672, 265" /><Element Key="" Location="495, 362" /></Layout>

what I need per row:

<Layout><Element Key="0|d293905e-3f43-4a45-bb82-0a9305d39eeb" Location="554, 82" /><Element Key="a27a1df1-155f-4bba-8cbe-b395252bdbaf" Location="457, 0" /><Element Key="1|12482af1-a70a-474e-b77d-68346a922e07" Location="144, 118" /><Element Key="2|ce6d5ddf-e09f-431e-9062-0ca0f3aeff08" Location="337, 295" /></Layout>

Current db rows:

UID_PWODecisionSubMethod	UID_PWODecisionStep	LevelNumber	LayoutInformation
a27a1df1-155f-4bba-8cbe-b395252bdbaf	d293905e-3f43-4a45-bb82-0a9305d39eeb	0	<Layout><Element Key="0|5daf8159-130b-4e3b-b4bf-96283c11251c" Location="554, 82" /><Element Key="5db6f41e-fbe1-47e5-8bad-332a62ce685f" Location="457, 0" /><Element Key="1|0aea2b9b-a9a5-4837-928d-7caaff7c411c" Location="144, 118" /><Element Key="2|63e5a539-7db8-40e3-9a1c-de9aa0a2ed69" Location="337, 295" /><Element Key="3|2fe62040-dc9f-40b8-a374-4147b61cc123" Location="675, 504" /><Element Key="4|badf28ed-e3a6-4a45-a930-c8118f481e34" Location="337, 536" /><Element Key="5|e73c6dc3-ecf7-44c6-8604-62d774c820e0" Location="680, 667" /><Element Key="6|bb2e81d2-a047-4c82-8f9b-8a42963d0351" Location="542, 778" /><Element Key="7|09bd8b32-df7c-4b25-a250-75d6a041ee2a" Location="713, 1063" /><Element Key="8|0d49cf82-9112-40a3-af18-9d5beef87aaa" Location="672, 265" /><Element Key="" Location="495, 362" /></Layout>
a27a1df1-155f-4bba-8cbe-b395252bdbaf	12482af1-a70a-474e-b77d-68346a922e07	1	<Layout><Element Key="0|5daf8159-130b-4e3b-b4bf-96283c11251c" Location="554, 82" /><Element Key="5db6f41e-fbe1-47e5-8bad-332a62ce685f" Location="457, 0" /><Element Key="1|0aea2b9b-a9a5-4837-928d-7caaff7c411c" Location="144, 118" /><Element Key="2|63e5a539-7db8-40e3-9a1c-de9aa0a2ed69" Location="337, 295" /><Element Key="3|2fe62040-dc9f-40b8-a374-4147b61cc123" Location="675, 504" /><Element Key="4|badf28ed-e3a6-4a45-a930-c8118f481e34" Location="337, 536" /><Element Key="5|e73c6dc3-ecf7-44c6-8604-62d774c820e0" Location="680, 667" /><Element Key="6|bb2e81d2-a047-4c82-8f9b-8a42963d0351" Location="542, 778" /><Element Key="7|09bd8b32-df7c-4b25-a250-75d6a041ee2a" Location="713, 1063" /><Element Key="8|0d49cf82-9112-40a3-af18-9d5beef87aaa" Location="672, 265" /><Element Key="" Location="495, 362" /></Layout>
a27a1df1-155f-4bba-8cbe-b395252bdbaf	ce6d5ddf-e09f-431e-9062-0ca0f3aeff08	2	<Layout><Element Key="0|5daf8159-130b-4e3b-b4bf-96283c11251c" Location="554, 82" /><Element Key="5db6f41e-fbe1-47e5-8bad-332a62ce685f" Location="457, 0" /><Element Key="1|0aea2b9b-a9a5-4837-928d-7caaff7c411c" Location="144, 118" /><Element Key="2|63e5a539-7db8-40e3-9a1c-de9aa0a2ed69" Location="337, 295" /><Element Key="3|2fe62040-dc9f-40b8-a374-4147b61cc123" Location="675, 504" /><Element Key="4|badf28ed-e3a6-4a45-a930-c8118f481e34" Location="337, 536" /><Element Key="5|e73c6dc3-ecf7-44c6-8604-62d774c820e0" Location="680, 667" /><Element Key="6|bb2e81d2-a047-4c82-8f9b-8a42963d0351" Location="542, 778" /><Element Key="7|09bd8b32-df7c-4b25-a250-75d6a041ee2a" Location="713, 1063" /><Element Key="8|0d49cf82-9112-40a3-af18-9d5beef87aaa" Location="672, 265" /><Element Key="" Location="495, 362" /></Layout>

my update statement (I only want to update layout column dynamically )

;with xxa as (
select pwosm.UID_PWODecisionSubMethod,pwos.UID_PWODecisionStep,LevelNumber,LayoutInformation from PWODecisionSubMethod pwosm,PWODecisionStep pwos
where pwosm.UID_PWODecisionSubMethod=pwos.UID_PWODecisionSubMethod
and isnull(pwosm.XDateUpdated, '1899-12-30 00:00:00.000') >= '2015-08-28 00:00:00.000'
--and isnull(pwosm.xdateinserted, '1899-12-30 00:00:00.000') >= '2015-08-28 00:00:00.000'
and pwosm.UID_PWODecisionSubMethod='a27a1df1-155f-4bba-8cbe-b395252bdbaf'
order by pwos.UID_PWODecisionSubMethod,LevelNumber asc
) 
update pwosmn set pwosmn.LayoutInformation='<Layout>
<Element Key="4|30faa907-b910-4df0-8784-816fa41ca93d" Location="826, 902" />
<Element Key="2|7c5f726c-08ea-408f-9426-fec9202b2e0a" Location="824, 643" />
<Element Key="5|8b372c54-b2a0-49d3-abb4-6d54402bd43b" Location="822, 313" />
<Element Key="4a40c7df-8f1f-4c40-97f1-25312bda7d15" Location="467, 0" />
<Element Key="0|6ae4fd7e-4bf8-447b-b522-a9f489bcab25" Location="468, 99" />
<Element Key="1|0b532f6f-ea69-486f-a431-db21ce86e862" Location="473, 416" />
<Element Key="3|d50ca0e4-96b0-4af2-9f7b-d3dc7172a3ca" Location="470, 703" />
<Element Key="" Location="1309, 2273" /></Layout>'
from PWODecisionSubMethod pwosmn,xxa pwosmcurr 
where pwosmn.UID_PWODecisionSubMethod=pwosmcurr.UID_PWODecisionSubMethod

#2

so what happens when you run your query?


#3

The run successfully but please read the thread about what I need.


#4

OK but what's in

before the update?