with temp as(
select substring(p.Description,4,len(p.Description)-1) 组分编码,iif(p.N_IsShareWithShare=1,N'是',N'否') 是否现配现分,iif(p.N_IsLucifuge=1,N'是',N'否') 是否避光,iif(p.N_IsOpenAir=1,N'是',N'否') 超净台关风
,p.N_TemperatureControl 温控条件,pmli.QtyRequired/b.N_BaseQty 分装体积,IdUom.UOMName 分装单位,p.N_SepLimits 分装上下限,p.N_HoleSite 孔位,iif(p.N_IsInkjetPrint=1,N'是',N'否') 是否喷码,p.N_InkjetPrintInfo 喷码信息
from Product p
join ProductBase pb on pb.ProductBaseId=p.ProductBaseId
left join BOM b on b.BOMId=p.BOMId and b.BOMRevision =
(select top 1 b.BOMRevision
from product p
join ProductBase pb on pb.ProductBaseId=p.ProductBaseId
left join bom b on p.ProductId = b.N_ProductId
where isnull(pb.ProductName,'NULL') like '%'+?N_Product+'%'
and isnull(p.N_Mnemoniccode,'NULL') like '%'+?PNumber+'%' order by b.BOMRevision desc)
left join ProductMaterialListItem pmli on b.bomid=pmli.bomid
left join Product p3 on p3.ProductId=pmli.ProductId
left join N_SecondaryClass sc on sc.N_SecondaryClassId=p3.N_SecondaryClassId
left join UOM IdUom on IdUom.UOMId=pmli.UOMId
where sc.N_SecondaryClassName in('2105','1106') and (isnull(pb.ProductName,'NULL') like '%'+?N_Product+'%'
and isnull(p.N_Mnemoniccode,'NULL') like '%'+?PNumber+'%'))
SELECT TOP 1 *FROM (
select *, 1 as level from temp where temp.分装单位='μL'---最小体积单位时保持单位不变
union all
select *, 2 as level from temp where temp.分装体积>=1---分装体积大于1保持单位不变
union all
select 组分编码,是否现配现分,是否避光,超净台关风,温控条件, 分装体积*1000 分装体积,'ML'分装单位,分装上下限,孔位,是否喷码,喷码信息,
CASE WHEN 分装体积*1000>=1 THEN 3 ELSE 99 END as level from temp where temp.分装单位='L'
union all
select 组分编码,是否现配现分,是否避光,超净台关风,温控条件, 分装体积*1000*1000 分装体积,'μL'分装单位,分装上下限,孔位,是否喷码,喷码信息,4 as level from temp where temp.分装单位='L'
union all
select 组分编码,是否现配现分,是否避光,超净台关风,温控条件, 分装体积*1000 分装体积,'μL'分装单位,分装上下限,孔位,是否喷码,喷码信息,5 as level from temp where temp.分装单位='ML'
) AS T
ORDER BY LEVEL
