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
