oracle - Oracle Where 案例
问题描述
我需要根据布尔值在 where 子句中创建一个条件。
如果为 true,则当最终值与预期值不同时(ConfirmedVolume > ExpectedVolume 等)我选择行,如果为 false,我选择所有内容。
我查看了一些链接,但无法将这个想法应用于我的查询。
例如。
Oracle 中带有 CASE 语句的条件 WHERE 子句
代码:
SELECT RouteId,
Id,
RouteStatusId,
Address,
Latitude,
Longitude,
ExpectedQty,
ConfirmedQty,
FinalQty,
ExpectedWeight,
ConfirmedWeight,
FinalWeight,
ExpectedVolume,
ConfirmedVolume,
FinalVolume,
Comm,
MAX(EndTime) - MAX(StartTime),
MAX(StartTime),
MAX(EndTime),
WaitTime,
MAX(CAST(EndTime AS DATE)),
UOM,
BPName
FROM
(
SELECT
{RouteStop}.[RouteId] RouteId,
{RouteStop}.[Id] Id,
{RouteStop}.[RouteStatusId] RouteStatusId,
{SiteAddress}.[Address] Address,
{SiteAddress}.[Latitude] Latitude,
{SiteAddress}.[Longitude] Longitude,
SUM({RouteStop_Product}.[ExpectedQty]) ExpectedQty,
SUM({RouteStop_Product}.[ConfirmedQty]) ConfirmedQty,
SUM({RouteStop_Product}.[FinalQty]) FinalQty,
SUM({RouteStop_Product}.[ExpectedWeight]) ExpectedWeight,
SUM({RouteStop_Product}.[ConfirmedWeight]) ConfirmedWeight,
SUM({RouteStop_Product}.[FinalWeight]) FinalWeight,
SUM({RouteStop_Product}.[ExpectedVolume]) ExpectedVolume,
SUM({RouteStop_Product}.[ConfirmedVolume]) ConfirmedVolume,
SUM({RouteStop_Product}.[FinalVolume]) FinalVolume,
{RouteStop}.[Comment] Comm,
CASE WHEN {GPS}.[EventTypeId] = @RouteStopStarted
THEN {GPS}.[DateTime]
ELSE CAST(NULL AS DATE)
END StartTime,
CASE WHEN {GPS}.[EventTypeId] = @RouteStopCompleted
OR {GPS}.[EventTypeId] = @RouteStopFailed
THEN {GPS}.[DateTime]
ELSE CAST(NULL AS DATE)
END EndTime,
{GPS}.[EventTypeId] EventTypeId,
{RouteStop}.[WaitTime] WaitTime,
{Order}.[UOM] UOM,
{BusinessPartner}.[CardName] BPName
FROM {RouteStop}
LEFT JOIN {RouteStop_Product} ON {RouteStop}.[Id] = {RouteStop_Product}.[RouteStopId]
LEFT JOIN {GPS} ON {RouteStop}.[Id] = {GPS}.[RouteStopID]
LEFT JOIN {SiteAddress} ON {RouteStop}.[SiteAddress] = {SiteAddress}.[Id]
LEFT JOIN {BusinessPartner} ON {SiteAddress}.[CardCode] = {BusinessPartner}.[Id]
LEFT JOIN {Order} ON {RouteStop_Product}.[OrderId] = {Order}.[Id]
LEFT JOIN {SitePlanningArea} ON {SiteAddress}.[Id] = {SitePlanningArea}.[SiteAddressId]
WHERE {RouteStop}.[Id] = @RouteStopId
AND ({SitePlanningArea}.[PlanningAreaId] = @PlanningAreaId OR @PlanningAreaId = 0)
GROUP BY {RouteStop}.[RouteId],
{RouteStop}.[Id],
{RouteStop}.[RouteStatusId],
{SiteAddress}.[Address],
{SiteAddress}.[Latitude],
{SiteAddress}.[Longitude],
{RouteStop}.[Comment],
{GPS}.[EventTypeId],
{GPS}.[DateTime],
{RouteStop}.[WaitTime],
{Order}.[UOM],
{BusinessPartner}.[CardName]
)
GROUP BY RouteId,
Id,
RouteStatusId,
Address,
Latitude,
Longitude,
ExpectedQty,
ConfirmedQty,
FinalQty,
ExpectedWeight,
ConfirmedWeight,
FinalWeight,
ExpectedVolume,
ConfirmedVolume,
FinalVolume,
Comm,
WaitTime,
UOM,
BPName
我想在哪里做类似的事情:
WHERE {RouteStop}.[Id] = @RouteStopId
AND ({SitePlanningArea}.[PlanningAreaId] = @PlanningAreaId OR @PlanningAreaId = 0)
AND IF boolean = true
ConfirmedVolume > ExpectedValue
AND ConfirmedQuantity > ExpectedQuantity
解决方案
伪代码推理,您可能需要编辑
IF boolean = true
ConfirmedVolume > ExpectedValue
变成类似的东西
( (boolean = true and ConfirmedVolume > ExpectedValue) OR (boolean = false) )
推荐阅读
- for-loop - 如何在 PL/SQL 中打印此模式
- reactjs - 如何为我的 Stripe 集成编写 Lambda 函数?
- pca - 如何在 PCA 中找到变量和个体的坐标、质量和贡献?
- kubernetes - 为什么我的 vs-code 容器部署到我的 GKE K8s 集群返回“没有最低可用性”
- minecraft-forge - Minecraft Forge 1.16 获得 Blockdamage 进度
- python - 如何使 2 个 while 循环在 python turtle 中同时工作
- javascript - react this.state中的方括号[]未定义,数据未呈现
- docker - 由于守护程序中的错误,docker容器不断重启
- r - Pivot_wider 对发布表使用多个变量
- python - Pygame:添加一个 Escape 键以使用秒表功能退出整个游戏