Author Posts

June 4, 2018 at 4:06 pm

I'm trying to find a way of adding lines to a tsql script that have a number of select commands listed in alphabetical order......So when we add a new Client, update the tsql script via Powershell command.
eg: new Clients are De and Fc.
I want to add a new line "SELECT 'De' AS client_name, 'Blogs,Joey' AS director UNION", in between Db and Df and "SELECT 'Fc' AS client_name, 'Blogs,Joey' AS director UNION" in between Fa and Fg

Query as is now:

from mantis_bug_table a
left join mantis_user_table b
on b.id = a.handler_id
left join mantis_project_table c
on c.id = a.project_id
left join mantis_user_table d
on d.id = a.reporter_id
left join mantis_category_table mct
on mct.id = a.category_id
left join mantis_custom_field_string_table mcfst
on mcfst.bug_id = a.id
join mantis_project_hierarchy_table mpht on mpht.child_id = a.project_id
LEFT OUTER JOIN (
	SELECT 'A' AS client_name, 'Blogs,Joey' AS director UNION
	SELECT 'Ba' AS client_name, 'Blogs,Joey' AS director UNION
    SELECT 'Bb' AS client_name, 'Blogs,Joey' AS director UNION
	SELECT 'Cc' AS client_name, 'Blogs,Joey' AS director UNION
    SELECT 'Cd' AS client_name, 'Blogs,Joey' AS director UNION
	SELECT 'Da' AS client_name, 'Blogs,Joey' AS director UNION
    SELECT 'Db' AS client_name, 'Blogs,Joey' AS director UNION
    SELECT 'Df' AS client_name, 'Blogs,Joey' AS director UNION
	SELECT 'Fa' AS client_name, 'Blogs,Joey' AS director UNION
    SELECT 'Fg' AS client_name, 'Blogs,Joey' AS director UNION
    SELECT 'Me' AS client_name, 'Blogs,Joey' AS director UNION
    SELECT 'Mn' AS client_name, 'Blogs,Joey' AS director UNION
) AS csg ON CAST(c.name AS CHAR(100)) = CAST(csg.client_name AS CHAR(100))
where (mpht.parent_id in ('22','354','281')
	OR mpht.parent_id IN (SELECT child_id FROM mantis_project_hierarchy_table WHERE parent_id in ('22','354','281')))
and a.status not in( '90','80')
and mcfst.field_id = 68 and length(mcfst.value) = 10
and mct.name  'Master Ticket/Non-Development'

June 4, 2018 at 6:11 pm

Try separating what is sortable and then build the query, like so:

$arr = 'A','Ba','Bb', 'Cc','Cd','Da','Db','Df','Fa','Fg','Me','Mn'

$arr+='De'
$arr+='Fe'

$subQuery = foreach ($row in $arr | Sort-Object) {
    "    SELECT '{0}' AS client_name, 'Blogs,Joey' AS director UNION" -f $row
}

$query = @"
from mantis_bug_table a
left join mantis_user_table b
on b.id = a.handler_id
left join mantis_project_table c
on c.id = a.project_id
left join mantis_user_table d
on d.id = a.reporter_id
left join mantis_category_table mct
on mct.id = a.category_id
left join mantis_custom_field_string_table mcfst
on mcfst.bug_id = a.id
join mantis_project_hierarchy_table mpht on mpht.child_id = a.project_id
LEFT OUTER JOIN (
$($subQuery -join [Environment]::NewLine | Out-String)
) AS csg ON CAST(c.name AS CHAR(100)) = CAST(csg.client_name AS CHAR(100))
where (mpht.parent_id in ('22','354','281')
	OR mpht.parent_id IN (SELECT child_id FROM mantis_project_hierarchy_table WHERE parent_id in ('22','354','281')))
and a.status not in( '90','80')
and mcfst.field_id = 68 and length(mcfst.value) = 10
and mct.name  'Master Ticket/Non-Development'
"@

$query

June 4, 2018 at 7:56 pm

Thanks Rob,
This I'd not thought of or got to yet, thanks, very helpfull!

$($subQuery -join [Environment]::NewLine | Out-String)

I'd got figuring out holding the values in a Hashtable and was looping through that. I'd not mentioned there were actually 2 values I've to add to each tsql select.

 foreach($key in $Networks.keys | Sort)
 {
    $text +=  'SELECT {0} AS client_name, {1} AS director UNION' -f $Key , $networks[$key]
 }

June 4, 2018 at 8:16 pm

Rob, Thanks, works a treat....