CODEWITHSUNDEEP
sqloracle-databasetableau-api
heyooo678
heyooo678

Reputation: 91

Oracle database error 907: ORA-00907: missing right parenthesis

I transferred this code directly from SQL developer. Works perfectly in there.

SELECT 
    a.INCIDENT_NUMBER,
    a.DETAILED_DESCRIPTION,    
    a.INCIDENT_ROOT_CAUSE

FROM 
    N_EVALUATION as a

INNER JOIN N_DISPOSITION as b
    ON (a.INCIDENT_NUMBER = b.INCIDENT_NUMBER) 

WHERE
    b.DISPOSITION_LINE_NUM in (NULL, 1) AND
    a.ACTIVE_FLAG = 'Y' AND
    b.ACTIVE_FLAG = 'Y' AND
    a.DETAILED_DESCRIPTION IS NOT NULL

However when I transfer the same exact code into Tableau to create a custom SQL query. It gives me an error;

An error occurred while communicating with the data source. Bad Connection: Tableau could not connect to the data source. Oracle database error 907: ORA-00907: missing right parenthesis

This has me completely stumped, not really sure what to do here. Any help or advice is much appreciated. I am more concerned regarding the missing right parenthesis rather than the bad connection.

Upvotes: 1

Views: 5513

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Remove the AS from the FROM clause. Oracle does not recognize that.

In addition, this condition:

 b.DISPOSITION_LINE_NUM in (NULL, 1)

Does not do what you expect. It never evaluates to true if b.DISPOSITION_LINE_NUM is NULL.

You should replace it with:

 (b.DISPOSITION_LINE_NUM IS NULL OR b.DISPOSITION_LINE_NUM = 1)

Otherwise, your query looks like it has balanced parentheses, but you should write it as:

SELECT e.INCIDENT_NUMBER, e.DETAILED_DESCRIPTION, e.INCIDENT_ROOT_CAUSE
FROM N_EVALUATION e JOIN
     N_DISPOSITION d
     ON e.INCIDENT_NUMBER = d.INCIDENT_NUMBER
WHERE (d.DISPOSITION_LINE_NUM IS NULL OR d.DISPOSITION_LINE_NUM = 1) AND
      e.ACTIVE_FLAG = 'Y' AND
      d.ACTIVE_FLAG = 'Y' AND
      e.DETAILED_DESCRIPTION IS NOT NULL;

Notes:

  • User meaningful table aliases rather than arbitrary letters (this uses abbreviations).
  • Do not use as in the FROM clause.
  • Be careful with NULL comparisons.

Finally, your original query is equivalent to:

SELECT e.INCIDENT_NUMBER, e.DETAILED_DESCRIPTION, e.INCIDENT_ROOT_CAUSE
FROM N_EVALUATION e JOIN
     N_DISPOSITION d
     ON e.INCIDENT_NUMBER = d.INCIDENT_NUMBER
WHERE d.DISPOSITION_LINE_NUM = 1 AND
      e.ACTIVE_FLAG = 'Y' AND
      d.ACTIVE_FLAG = 'Y' AND
      e.DETAILED_DESCRIPTION IS NOT NULL;

This has no parentheses. So it cannot return that particular error.

Upvotes: 2

Related Questions