Oracle Where Case

Question

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

If true then I select the row when the final values are different from the expected (ConfirmedVolume > ExpectedVolume, etc) if false I select everything.

I took a look on some links but couldn't apply the ideia to my query.

eg.

Conditional WHERE clause with CASE statement in Oracle

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

I'd like to do something like that in where:

WHERE {RouteStop}.[Id] = @RouteStopId
      AND ({SitePlanningArea}.[PlanningAreaId] = @PlanningAreaId OR @PlanningAreaId = 0)
      AND IF boolean = true
          ConfirmedVolume > ExpectedValue
      AND ConfirmedQuantity > ExpectedQuantity

Are you sure you are using Oracle? Those dreaded square brackets look more like Microsoft's T-SQL — , Aug 31 '18 at 13:50
[outsystems](https://www.outsystems.com/) is not a database product. — , Aug 31 '18 at 13:55
I understand what it is - a programming environment. It's not a database. But your question is asking for help with a SQL statement and therefore your IDE or framework is mostly irrelevant (I assume that all those invalid curly braces will be replaced at runtime) . And for you to get answers that will help you, you have to tell us which database product you are using. It won't help you if you get an answer that is for Oracle but you are using SQL Server. Those are two very different products — , Aug 31 '18 at 14:02
So, Outsystems convert what you type in its components and convert for code. The way we type in the platform is the way I sent, behind the platform there is an Oracle database. So, it's Outsystems and I think the idea of the where clause will be similar for any language and I asked for Oracle, so, if you wanna help you just need to send in Oracle. Thanks — Denis, Aug 31 '18 at 14:07

score 1 · Accepted Answer · answered Aug 31 '18 at 13:52

1

Reasoning in pseudo-code, you probably need to edit

IF boolean = true
          ConfirmedVolume > ExpectedValue

into something like

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

answered Aug 31 '18 at 13:52

Aleksej

22,443
5
33
38

Oracle Where Case

1 Answers1