SQLTeam.com | Weblogs | Forums

Removing the rows


#1

Hi,

I have the following query and the result,
How can I avoid the lines where there is no default value

INSERT INTO @goldlist VALUES('US Abdomen & Pelvis with Prep')

select

    ocs.name [Order set Name],
    ocmi.name as [Item Name],
  
 case
        when (field.label is Null) then ''
      else field.label
  END AS [Field Label],


	CASE  
             WHEN (field.dataitemcode = 'SpecialInstructions') THEN oai.specialinstructions 

			 ELSE 
           CASE 
                   WHEN oud.value IS NULL THEN ' '
                   ELSE oud.value
           END                            

END AS [defaultvalue]

from CV3orderCatalogMasterITem ocmi

JOIN cv3order o --join items to order
on ocmi.guid = o.ordercatalogmasteritemguid
JOIN cv3ordercatalogset ocs --join Order sets to Order
on o.ordersetguid = ocs.guid
join @goldlist g
on g.name = ocs.name
JOIN cv3orderentryform oef -- join order form to catalog items
ON oef.formguid = ocmi.entryformguid

JOIN cv3orderentryfield field -- join form fields to the form
ON field.orderentryformguid = oef.guid
and oef.iscurrent = 1
Left Join CV3OrderuserData oud
on o.guid = oud.orderguid AND field.dataitemcode = oud.userdatacode
left JOIN CV3OrderAddnlInfo oai
ON oai.guid = o.guid

where
o.clientguid = 0
AND (ocs.expirydate IS NULL OR ocs.expirydate > getdate()) AND ocs.name not like 'zz%' AND ocs.name not like 'qq%'
AND (ocmi.expirydate IS NULL OR ocmi.expirydate > getdate()) AND ocmi.name not like 'zz%' AND ocmi.name not like 'qq%'
--and field.Label = 'Clinical Communication :'

order by ocs.Name


#2

Try this is the where

AND CASE  
    WHEN (field.dataitemcode = 'SpecialInstructions') THEN oai.specialinstructions 
    ELSE 
           CASE 
                   WHEN oud.value IS NULL THEN ' '
                   ELSE oud.value
           END
END <> ' '