Adding line of text in alphbetical order

This topic contains 3 replies, has 2 voices, and was last updated by  iain Barnetson 2 weeks, 4 days ago.

  • Author
    Posts
  • #101736

    iain Barnetson
    Participant

    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'
    
  • #101743

    Rob Simmers
    Participant

    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
    
    • #101751

      iain Barnetson
      Participant

      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]
       }
      
    • #101754

      iain Barnetson
      Participant

      Rob, Thanks, works a treat....

You must be logged in to reply to this topic.