Search code examples
oracleplsqlcaseoracle-sqldeveloper

Case when in sql oracle

I am new in sql oracle and I have a problem with how to use case when here, for example this is the case :

  1. if transaction_type RECEIVE then receipt date - promised date

  2. if receipt date > promised date then receipt date - promised date

  3. if receipt date <= promised date then null

  4. if not yet receive or transaction_type DELIVER then sysdate - promised date

    CASE WHEN transaction_type ='RECEIVE' THEN to_char(creation_date, 'DD-MON-YYYY') – to_char(promised_date, 'DD-MON-YYYY')
  WHEN to_char(creation_date, 'DD-MON-YYYY') > to_char(promised_date, 'DD-MON-YYYY') THEN to_char(creation_date, 'DD-MON-YYYY') – to_char(promised_date, 'DD-MON-YYYY')
  WHEN to_char(creation_date, 'DD-MON-YYYY') <= to_char(promised_date, 'DD-MON-YYYY') THEN null
  WHEN transaction_type ='DELIVER' THEN to_char(sysdate, 'DD-MON-YYYY') - to_char(promised_date, 'DD-MON-YYYY') END AS delay_day

ORA-00911: invalid character 00911. 00000 - "invalid character" *Cause: The identifier name started with an ASCII character other than a letter or a number. After the first character of the identifier name, ASCII characters are allowed including "$", "#" and "_". Identifiers enclosed in double quotation marks may contain any character other than a double quotation. Alternate quotation marks (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual. *Action: Check the Oracle identifier naming convention. If you are attempting to provide a password in the IDENTIFIED BY clause of a CREATE USER or ALTER USER statement, then it is recommended to always enclose the password in double quotation marks because characters other than the double quotation are then allowed. Error at Line: 21 Column: 94

and this is the error and I have no idea what's wrong with this

Solution

  • Looks like you copy/pasted code from some fancy text editor (MS Word, perhaps?) which uses fancy characters for single and double quotes, as well as minus signs. If converted to a "normal" minus -, then it should be OK:

          CASE
          WHEN transaction_type = 'RECEIVE'
          THEN
               TO_CHAR (creation_date, 'DD-MON-YYYY')
             - TO_CHAR (promised_date, 'DD-MON-YYYY')         --> this minus
          WHEN TO_CHAR (creation_date, 'DD-MON-YYYY') >
               TO_CHAR (promised_date, 'DD-MON-YYYY')
          THEN
               TO_CHAR (creation_date, 'DD-MON-YYYY')
             - TO_CHAR (promised_date, 'DD-MON-YYYY')         --> and this minus
          WHEN TO_CHAR (creation_date, 'DD-MON-YYYY') <=
               TO_CHAR (promised_date, 'DD-MON-YYYY')
          THEN
             NULL
          WHEN transaction_type = 'DELIVER'
          THEN
               TO_CHAR (SYSDATE, 'DD-MON-YYYY')
             - TO_CHAR (promised_date, 'DD-MON-YYYY')
       END AS delay_day

    However: what do you expect to get as a result? Subtraction of two strings is ridiculous. What is 'A' - '23FX__#'? If you wanted to subtract dates, sure - why not - result will be number of days between those two dates.

    Furthermore, comparing strings won't work either in most cases.

    It means that your should - perhaps - remove TO_CHAR entirely.

            CASE
          WHEN transaction_type = 'RECEIVE'
          THEN
             creation_date - promised_date
          WHEN creation_date > promised_date
          THEN
             creation_date - promised_date
          WHEN creation_date <= promised_date
          THEN
             NULL
          WHEN transation_type = 'DELIVER'
          THEN
             SYSDATE - promised_date
       END AS delay_day