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)",
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;