Oracle Where case

Question

I need to create a condition in the where according to a boolean.

If boolean = true then I will select the lines with higher end values than expected (ConfirmedVolume > ExpectedVolume, etc) if it is falso I select everything.

I took a look at several links but could not pass the idea to my query, for example:

• https://stackoverflow.com/questions/18104884/conditional-where-clause-with-case-statement-in-oracle

• https://stackoverflow.com/questions/29027425/oracle-conditional-where-clause

Code:

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

Thank you

Answer 1

Solved with the clause below:

((boolean = true and ConfirmedVolume > ExpectedValue) OR (boolean = false))