select column and If value exist show a column and show yes or no-CodePudding

I would like to write a query to add extra column (ACCESS) if the data in ACCESS_TPE o existed then show "yes" otherwise show "no.

Table

EMPLID  RCD DI_ACCESS_NAME  ACCESS
123450  0   TK              Y
123450  1   HCS-P           Y
123450  1   SI              Y
123450  3   NATO            Y
123451  0                   N
123451  1                   N
123452  0   HCS-P           Y
123453  0                   N

My query is

SELECT emplid, empl_rcd, di_access_name, (CASE WHEN coalesce(di_access_name,'') <> '' THEN 'yes' else 'no' end) as access FROM PS_DI_SEC_ACCESS;

SELECT emplid, empl_rcd, di_access_name,(CASE WHEN di_access_name IS NOT NULL THEN 'yes' else 'no' end) as access
FROM PS_DI_SEC_ACCESS;

both gives me error, ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected" *Cause:
*Action: Error at Line: 5 Column: 116

CodePudding user response：

ACCESS is a reserved word and cannot be used as an unquoted identifier. Either use a different word or a quoted identifier.

SELECT emplid,
       empl_rcd,
       di_access_name,
       CASE WHEN di_access_name IS NOT NULL THEN 'yes' else 'no' end as "ACCESS"
FROM   PS_DI_SEC_ACCESS;

or:

SELECT emplid,
       empl_rcd,
       di_access_name,
       NVL2(di_access_name, 'yes', 'no') as "ACCESS"
FROM   PS_DI_SEC_ACCESS;

fiddle

Note: In Oracle, '' and NULL are identical and Oracle uses tri-nary logic where NULL <> anything gives the result NULL rather than true so your first query will not work.