CODEWITHSUNDEEP
oracle-databaseparameterscasewhere-clause
fuko
fuko

Reputation: 37

How to filter with short names in where clause

I have a question, how we can filter a value in a where clause under case statement OTHER then the actual values present at the DB end.

E.g.

WHERE
'&mwb_header.transport_mode' --want to create a parameter
(CASE WHEN (mwb_header.transport_mode='O')   THEN 'OCEAN' 
       WHEN (mwb_header.transport_mode='A')     THEN 'AIR' 
    WHEN (mwb_header.transport_mode='R')     THEN 'RAIL' 
    WHEN (mwb_header.transport_mode='T')     THEN 'TRUCK' 
  ELSE 'OTHERS' END) ;

Actual values present for this columns at the DB end are 'O','A','R','T' and 'OTH' but i just want to use any of the value for filters like 'O' or 'OCEAN' to filter the correct results.

Upvotes: 0

Views: 48

Answers (2)

wolφi
wolφi

Reputation: 8361

If you can change the table structure, I would suggest to add a "virtual column" to the table with the longer names (like OCEAN).

The data is computed (or generated) from the real column transport_mode. It looks like a normal column, but nothing is stored in the database, but computed as an when needed.

In other words, we move your code that does the translation from 'O' to 'OCEAN' from the WHERE clause into the table definition, so that it is available to everyone.

ALTER TABLE mwb_header ADD (long_transport GENERATED ALWAYS AS (
  CASE transport_mode
       WHEN 'O' THEN 'OCEAN'
       WHEN 'A' THEN 'AIR'
       WHEN 'R' THEN 'RAIL'
       WHEN 'T' THEN 'TRUCK'
                ELSE 'OTHERS'
   END));

Now you (and all other queries) can use the long names:

SELECT * from mwb_header WHERE long_transport='OCEAN';

exactly like the short names:

SELECT * from mwb_header WHERE transport_mode='O';

the results will be exactly the same.

Even better, if the table has many rows, you can even put an index on the computed column to speed things up:

CREATE INDEX mwb_short_transport_idx ON mwb_header(long_transport);

Upvotes: 0

wolφi
wolφi

Reputation: 8361

Your code looks fine and seems to work:

CREATE TABLE mwb_header (i NUMBER, transport_mode VARCHAR2(3));
INSERT INTO mwb_header VALUES (1, 'O');
INSERT INTO mwb_header VALUES (2, 'A');
INSERT INTO mwb_header VALUES (3, 'R');
INSERT INTO mwb_header VALUES (4, 'T');
INSERT INTO mwb_header VALUES (5, 'OTH');

SELECT *
  FROM mwb_header
 WHERE 'OCEAN' = (CASE WHEN (mwb_header.transport_mode='O') THEN 'OCEAN' 
                       WHEN (mwb_header.transport_mode='A') THEN 'AIR' 
                       WHEN (mwb_header.transport_mode='R') THEN 'RAIL' 
                       WHEN (mwb_header.transport_mode='T') THEN 'TRUCK' 
                                                            ELSE 'OTHERS' 
                   END) ;

returns 

id  transport_mode
1   O

I prefer personally the shorter syntax

SELECT *
  FROM mwb_header
 WHERE 'OCEAN' = (CASE mwb_header.transport_mode
                       WHEN 'O' THEN 'OCEAN'
                       WHEN 'A' THEN 'AIR'
                       WHEN 'R' THEN 'RAIL'
                       WHEN 'T' THEN 'TRUCK'
                                ELSE 'OTHERS'
                   END);

Upvotes: 1

Related Questions