vendredi 30 octobre 2020

I have already created a stored procedure query. Now I want to modify my query to get the output with conditions for feature column

select distinct(l.ID),
    l.labID as OrderLabid, l.name as InterfaceName,
    l.deleteFlag,
    (case when l.labID = 28 then 'Uni-directional'  
          when l.interfaceconfigured = 1 then 'Uni-directional' 
          when l.interfaceconfigured = 2 then 'Bi-Directional' 
          when l.interfaceconfigured = 3 then 'Uni with ereq' 
     end) as "Uni/Bi",
    l.resultlabid, 
    e.practid, e.ecwlabid, e.hublabid,
    cast (eu.url as varchar(255)) as HUB_URL, 
    stuff((select ' (-) ' + P.keyname + ':= ' + c.classtobeexecuted + ', ' + 'RegisteredServerId:= ' + cast(c.RegisteredServerId as varchar) 
           from xyz P 
           join ecwschtasks c on cast(p.ID as varchar) = cast(c.ecwschjobdatamapids as varchar) 
           where cast(P.keyvalue as varchar) = cast(eu.PractId as varchar) 
             and c.deleteflag = 0 
           for xml path('')), 1, 1, '') as 'Jobs',
    replace(replace(replace(replace(stuff((select ' (-) ' + 'Name:= ' + li.name,' ** Itemid:= ',li.itemId,' ** Value:= ',li.value  
                                           from def li 
                                           where li.labid = l.ID 
                                             and (li.name in ('OMG')) 
                                           for xml path('')), 1, 1, ''), '<value>', ''), '</value>', ''), '<itemid>',''), '</itemid>','') 'Feature',
from 
    ABC l 
join 
    ecwlabhub_spoke_mapping e on l.ID = e.ecwlabid 
left join 
    ecwlabhub_spoke_url eu on e.practid = eu.practid 
                           and eu.Type = 0 
left join 
    ecwschjobsdatamap ed on eu.practid = ed.keyvalue 
left join 
    ecwschtasks es on ed.Id = es.ecwschjobdatamapids 
where 
    l.deleteflag = 0 and l.labid > 0;

Aucun commentaire:

Enregistrer un commentaire