Flag Best Practice in SQL Oracle developer

Question

I have one question, I just created lot of flag in CASE ( ... 'yes' or 'No', and I don't know the best practices to convert in smallest format VARCHAR or VACHAR2 ?

Like this one :

-- Created in FO (flag)

CASE
WHEN CRE_DD.DIVISION  IN( 
    'Postinfo 1st line' ,
    'CX - CC INFODESK' ,
    'CC FO First Line' ,
    'CX - CC MIDDLE OFFICE'
)
OR  ( CRE_DD.DIVISION =  'FINES CC' AND DSR.ENTRY_CHANNEL = 'Phone')
THEN 'YES'
ELSE 'NO'
END   AS "Created in FO (flag)",

--FO treatment (flag)

 CASE
 WHEN CUR_DD.DIVISION IN ('Postinfo 1st line' ,'CX - CC INFODESK','CC FO First Line' )
       
         OR  (CUR_DD.DIVISION =  'FINES CC' AND DSR.ENTRY_CHANNEL = 'Phone')
    
 THEN 'YES'
      ELSE 'NO'
           END AS "FO treatment (flag)",

Oracle recommends creating `varchar2` for storing variable length text. — Popeye, Dec 21 '20 at 07:39
And, in the select In Oracle it's possible to measure the size of my column ? — , Dec 21 '20 at 07:42
Yes you can, tho `varchar2` has two different variants to limit its length, by `char`s or by `byte`s, by defining `varchar2(50)` this would default to `char`, `varchar2(64 byte)`, would give 64 byte space, as some chars take more than one byte — procra, Dec 21 '20 at 07:45
i would use varchar2(4), because I personally like to stick on potencies of 2. Another question: how many flags do you have in total? Otherwise it would be more change-proof if you dispatch the flags in another table — procra, Dec 21 '20 at 08:00
Hmmm good idea, I think 10 -15 flag . Can you explain me the advantage ? It's not a new table, it's view in oracle Select... — , Dec 21 '20 at 08:02
Ok I have to correct myself, if you store each flag in a single column you can leave them as they are. I thought about a case where someone used 13 flags inside one field. This is possible with `number` too but the downside is that you have to document the meaning of each flag en detail and in a way you and others can find it later. Also we all had to hope that the meaning (and the ranking) of the flag wont change at any time, because the we would have a real lot of work deleting old flags from every columns — procra, Dec 21 '20 at 08:17
Yes I always make a comment with description, So I have no advantage in making a small table except for the flag only? — , Dec 21 '20 at 08:29
Not really, except, the number of flags can change in the future — procra, Dec 21 '20 at 08:34
Hmm they can change the definition and the ever changing flag count. — , Dec 21 '20 at 08:37
I'm not sure why the datatype is a consideration at all when you are creating a view. Oracle will assign a datatype, unless you explicitly cast the expression to something else. Or are you asking about the naming convention - `YES`/`NO`, `Y`/`N`, `0`/`1` etc? — William Robertson, Dec 21 '20 at 11:02
@WilliamRobertson, my future users needs YES/NO . But I don't know if oracle will choose the best data type. Because there will be no other answer than YES / NO — , Dec 21 '20 at 11:42
It will choose `VARCHAR2(3)`, which will be fine. What is your concern though? It's only a view column, so there is no storage consideration. The [documentation](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html#GUID-7B72E154-677A-4342-A1EA-C74C1EA928E6) explicitly says not to use `VARCHAR`, and don't get me started on [`CHAR`](https://stackoverflow.com/a/42165653/230471). — William Robertson, Dec 21 '20 at 11:44

MT0 · Accepted Answer · 2020-12-21T12:27:34.963

In Oracle, there is only VARCHAR2 or CHAR string data types that are applicable to your data (NVARCHAR2, NCHAR and CLOB are not appropriate).

VARCHAR is a synonym of VARCHAR2; so asking whether you should use one or the other is pointless as they are the same thing. Just use VARCHAR2 rather than using its synonym.

Don't use CHAR as it will right-pad the string with spaces so you won't have 'NO' but would have 'NO ' instead and it may not always behave as expected in comparisons and would have to trim the trailing spaces.

If you are using variable length strings that can either be 'YES' or 'NO' and you want to put them into a table then define the column as VARCHAR2(3).

For example:

CREATE TABLE your_table (
  id   NUMBER(10,0)
       GENERATED ALWAYS AS IDENTITY
       CONSTRAINT your_table__id__pk PRIMARY KEY,
  data NUMBER,
  flag VARCHAR2(3)
       NOT NULL
       CONSTRAINT your_table__flag__ck CHECK ( flag IN ( 'YES', 'NO' ) )
);

INSERT INTO your_table ( data, flag )
SELECT data,
       CASE
       WHEN some_condition = 1
       THEN 'YES'
       ELSE 'NO'
       END
FROM   other_table;

If you are using a view then just use the string literals 'YES' and 'NO' and allow Oracle to implicitly manage the data type and you don't need to worry about it.

For example:

CREATE VIEW your_view ( id, data, flag ) AS
SELECT id,
       data,
       CASE
       WHEN some_condition = 1
       THEN 'YES'
       ELSE 'NO'
       END
FROM   other_table;

Flag Best Practice in SQL Oracle developer

1 Answers1