帆软 - 我的帆软

帆软

能不能让下面一句改成if格式，为空也能出数据，

AND i.CODE IN ( SELECT CODE FROM MMS_Material_Typ_Info WHERE Typ in ('${typ}') )

SQL yzmknpQp2068966 发布于 2023-3-16 10:47 （编辑于 2023-3-16 10:51）

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：0 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共2回答

最佳回答

snrtuemcLv8专家互助
发布于2023-3-16 10:52（编辑于 2023-3-16 11:05）

${if(len(typ)=0,""," and i.CODE IN ( select CODE from mms_material_typ_info where typ in ('"+TYP+"')")}

==============================

${if(len(typ)=0,""," and i.CODE IN ( select CODE from mms_material_typ_info where typ in ('"+TYP+"'))")}

=========

整个完成的

SELECT h.id, h.Fab, h.MaterialNo, i.Typ MaterialType, i.CODE, h.STATUS, i.ProductionDate, i.ExpiredDate, i.StockOutDate, i.LimitDay, i.ValidateTime, i.ReadyTime, h.Loc, h.EqpId, i.Unit, h.Qty, i.BatchNo, h.EmpID, h.Rectime, h.RelTime, h.Attr1, h.Attr2, h.Attr3, h.Attr4, h.Attr5, h.Attributes, h.LotID, h.DeviceName FROM MMS_Material_Hist h, MMS_Material_Info i

WHERE h.MaterialNo = i.MaterialNo

${if(StartTime=='',""," AND h.Rectime >= ('"+StartTime+"') ")}

${if(EndTime=='',""," AND h.Rectime <= ('"+EndTime+"') ")}

${if(len(typ)=0,""," and i.CODE IN ( select CODE from mms_material_typ_info where typ in ('"+typ+"'))")}

${if(hFab=='',""," AND h.Fab in ('"+hFab+"') ")} --厂区

${if(hLoc=='',""," AND h.Loc in ('"+hLoc+"') ")} --位置

${if(iCODE=='',""," AND i.CODE in ('"+iCODE+"') ")} --物料号

${if(hmaterialno=='',""," AND h.materialno in ('"+hmaterialno+"') ")}--条码

order by i.Typ,h.MaterialNo,h.RecTime

举报收起评论(6)

yzmknpQp2068966(提问者) 不行，报错了错误代码:11300001 数据集 [ds2] 配置错误除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

2023-03-16 10:56

snrtuemc 回复 yzmknpQp2068966(提问者) 那要看你的完成的sql语句，但是这个where语句的，这个改法，不会影响你的使用，不会报错的

2023-03-16 10:58

yzmknpQp2068966(提问者) 回复 snrtuemc SELECT h.id, h.Fab, h.MaterialNo, i.Typ MaterialType, i.CODE, h.STATUS, i.ProductionDate, i.ExpiredDate, i.StockOutDate, i.LimitDay, i.ValidateTime, i.ReadyTime, h.Loc, h.EqpId, i.Unit, h.Qty, i.BatchNo, h.EmpID, h.Rectime, h.RelTime, h.Attr1, h.Attr2, h.Attr3, h.Attr4, h.Attr5, h.Attributes, h.LotID, h.DeviceName FROM MMS_Material_Hist h, MMS_Material_Info i WHERE h.MaterialNo = i.MaterialNo ${if(StartTime==\'\',\"\",\" AND h.Rectime >= (\'\"+StartTime+\"\') \")} ${if(EndTime==\'\',\"\",\" AND h.Rectime <= (\'\"+EndTime+\"\') \")} ${if(len(typ)=0,\"\",\" and i.CODE IN ( select CODE from mms_material_typ_info where typ in (\'\"+typ+\"\')\")} ${if(hFab==\'\',\"\",\" AND h.Fab in (\'\"+hFab+\"\') \")} --厂区 ${if(hLoc==\'\',\"\",\" AND h.Loc in (\'\"+hLoc+\"\') \")} --位置 ${if(iCODE==\'\',\"\",\" AND i.CODE in (\'\"+iCODE+\"\') \")} --物料号 ${if(hmaterialno==\'\',\"\",\" AND h.materialno in (\'\"+hmaterialno+\"\') \")}--条码 order by i.Typ,h.MaterialNo,h.RecTime

2023-03-16 10:59