帆软

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

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

SQL yzmknpQp2068966 发布于 2023-3-16 10:47 (编辑于 2023-3-16 10:51)
1min目标场景问卷 立即参与
回答问题
悬赏:0 F币 + 添加悬赏
提示:增加悬赏、完善问题、追问等操作,可使您的问题被置顶,并向所有关注者发送通知
共2回答
最佳回答
0
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

  • yzmknpQp2068966 yzmknpQp2068966(提问者) 不行,报错了 错误代码:11300001 数据集 [ds2] 配置错误除非另外还指定了 TOP、OFFSET 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
    2023-03-16 10:56 
  • snrtuemc snrtuemc 回复 yzmknpQp2068966(提问者) 那要看你的完成的sql语句,但是这个where语句的,这个改法,不会影响你的使用,不会报错的
    2023-03-16 10:58 
  • yzmknpQp2068966 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 
  • snrtuemc snrtuemc 回复 yzmknpQp2068966(提问者) 知道了。我的语句掉了一个括号,看修改答案
    2023-03-16 11:04 
  • yzmknpQp2068966 yzmknpQp2068966(提问者) 回复 snrtuemc 。。。斜杠太多,看不明白。。
    2023-03-16 11:11 
最佳回答
0
Z4u3z1Lv6专家互助
发布于2023-3-16 10:51

说清楚啊

什么数据库?是子句结果为空出数据?还是子句结果中的code中有空的(null)也出数据?

  • 2关注人数
  • 241浏览人数
  • 最后回答于:2023-3-16 11:05
    请选择关闭问题的原因
    确定 取消
    返回顶部