CODEWITHSUNDEEP
oracle-database
Can't Tell
Can't Tell

Reputation: 13476

Oracle compare dimensions of a box against a reference

I have a table with box dimensions LENGTH, WIDTH and HEIGHT. I want to compare this box with a reference box to see if the box is bigger than the reference box and output for each box, if the box is bigger or not.

Lets say LENGTH, WIDTH and HEIGHT are 4,6 and 2 respectively. And the reference box dimensions are 3,5 and 8. In this case the box is smaller than the reference because (2,4,6) < (3,5,8).

So basically I have to compare the ordered box dimensions with the ordered given dimensions. Is there a way to do this with oracle?

I have already come across the GREATEST and LEAST functions, but I can't compare the middle dimension using those methods.

Upvotes: 1

Views: 27

Answers (3)

Can&#39;t Tell
Can&#39;t Tell

Reputation: 13476

I ended up using the GREATEST and LEAST functions as follows.

GREATEST(BOX_LENGTH, BOX_HEIGHT, BOX_WIDTH) > GREATEST(REF_LENGTH, REF_HEIGHT, REF_WIDTH) OR 

LEAST(BOX_LENGTH, BOX_HEIGHT, BOX_WIDTH) > LEAST(REF_LENGTH, REF_HEIGHT, REF_WIDTH) OR 

BOX_LENGTH + BOX_HEIGHT + BOX_WIDTH - GREATEST(BOX_LENGTH, BOX_HEIGHT, BOX_WIDTH) - LEAST(BOX_LENGTH, BOX_HEIGHT, BOX_WIDTH) > REF_LENGTH + REF_HEIGHT + REF_WIDTH - GREATEST(REF_LENGTH, REF_HEIGHT, REF_WIDTH) - LEAST(REF_LENGTH, REF_HEIGHT, REF_WIDTH)

That is

(Sum of dimensions) - Greatest - Least = Middle dimension

Upvotes: 0

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

As middle value take least of greatest from three pairs:

-- sample data
with boxes(length, width, height) as (
  select 4, 6, 2 from dual union all
  select 2, 6, 4 from dual union all
  select 1, 3, 2 from dual union all
  select 9, 7, 8 from dual )
-- 
select length, width, height,
       case when least(least(length, width), height)           < 3
                 and least(greatest(length, width), 
                           greatest(length, height), 
                           greatest(width, height))            < 5
                 and greatest(greatest(length, width), height) < 8
           then 'smaller than reference'
           else 'bigger or equal'
       end comparison
  from boxes

Result:

    LENGTH      WIDTH     HEIGHT COMPARISON
---------- ---------- ---------- ----------------------
         4          6          2 smaller than reference
         2          6          4 smaller than reference
         1          3          2 smaller than reference
         9          7          8 bigger or equal

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59563

You could do it like this one:

CREATE OR REPLACE TYPE NUMBER_TABLE_TYPE AS TABLE OF NUMBER;


DECLARE
    refBox NUMBER_TABLE_TYPE := NUMBER_TABLE_TYPE(4,6,2);
    inBox NUMBER_TABLE_TYPE := NUMBER_TABLE_TYPE(3,5,8);

    sortedRefBox NUMBER_TABLE_TYPE;
    sortedInBox NUMBER_TABLE_TYPE;

    fitting BOOLEAN := TRUE;

BEGIN

    IF refBox.count <> 3 OR inBox.count <> 3 THEN
        RAISE VALUE_ERROR;      
    END IF;

    SELECT COLUMN_VALUE 
    BULK COLLECT INTO sortedRefBox 
    FROM TABLE(refBox)
    ORDER BY COLUMN_VALUE;

    SELECT COLUMN_VALUE 
    BULK COLLECT INTO sortedinBox
    FROM TABLE(inBox)
    ORDER BY COLUMN_VALUE;

    FOR i IN 1..3 LOOP
        fitting := fitting AND sortedinBox(i) <= sortedRefBox(i);
    END LOOP;

    IF fitting THEN
        DBMS_OUTPUT.PUT_LINE('InBox fits in Reference Box' );
    else
        DBMS_OUTPUT.PUT_LINE('InBox does not fit in Reference Box' );   
    end if;

END;

Upvotes: 1

Related Questions