1

I did union on 2 of my queries below but gets the error: ORA-00932: inconsistent datatypes: expected - got CLOB

SQL looks like this:

SELECT A.EMPLID, A.NAME, A.ADM_APPL_NBR, A.ADMIN_FUNCTION, A.STDNT_CAR_NBR, 
A.ACAD_CAREER, A.APPL_PROG_NBR, A.ADMIT_TERM, A.CHECKLIST_CD, A.DESCRSHORT, 
A.CHECKLIST_STATUS, TO_CHAR(A.STATUS_DT,'YYYY-MM-DD'), 
TO_CHAR(A.DUE_DT,'YYYY-MM-DD'), A.COMM_COMMENTS, A.SEQ_3C, A.CHKLST_ITEM_CD, 
A.DESCR, A.ITEM_STATUS, TO_CHAR(A.UM_STATUS_DT_CL,'YYYY-MM-DD'), 
TO_CHAR(A.UM_DUE_DT_CL,'YYYY-MM-DD'), A.RESPONSIBLE_ID 
FROM PS_UM_7902_VW2 A 
WHERE ( 1 = 1 AND EMPLID LIKE DECODE (:1, '', '%', :1) 
AND ADM_APPL_NBR LIKE DECODE(:2, '', '%', :2) 
AND CHECKLIST_CD LIKE DECODE(:3, '', '%', :3) 
 AND A.CHECKLIST_STATUS = :4 
 OR '' = :4 
 AND A.CHECKLIST_STATUS = 'I' 
 OR ( A.CHECKLIST_STATUS = 'C' 
 AND SYSDATE BETWEEN A.TERM_BEGIN_DT AND A.TERM_END_DT) 
 AND ( A.ADMIT_TERM = A.STRM 
 AND SYSDATE BETWEEN A.TERM_BEGIN_DT AND A.TERM_END_DT)) 
 UNION 
 SELECT B.EMPLID, B.NAME, B.ADM_APPL_NBR, B.ADMIN_FUNCTION, B.STDNT_CAR_NBR, 
 B.ACAD_CAREER, B.APPL_PROG_NBR, B.ADMIT_TERM, B.CHECKLIST_CD, B.DESCRSHORT, 
 B.CHECKLIST_STATUS, TO_CHAR(B.STATUS_DT,'YYYY-MM-DD'), 
 TO_CHAR(B.DUE_DT,'YYYY-MM-DD'), B.COMM_COMMENTS, B.SEQ_3C, 
 B.CHKLST_ITEM_CD, B.DESCR, B.ITEM_STATUS, TO_CHAR(B.UM_STATUS_DT_CL,'YYYY- 
 MM-DD'), TO_CHAR(B.UM_DUE_DT_CL,'YYYY-MM-DD'), B.RESPONSIBLE_ID 
 FROM PS_UM_7902_VW2 B 
 WHERE ( B.EMPLID = :1 
 AND 1 = 1 and ADM_APPL_NBR like DECODE(:2, '', '%', :2)
 and CHECKLIST_CD like DECODE(:3, '', '%', :3) 
 AND B.CHECKLIST_STATUS = 'I' 
 OR ( B.CHECKLIST_STATUS = 'C' 
 AND SYSDATE BETWEEN B.TERM_BEGIN_DT AND B.TERM_END_DT) 
 AND B.ADMIT_TERM = B.STRM 
 AND SYSDATE BETWEEN B.TERM_BEGIN_DT AND B.TERM_END_DT);

Please help guys.

Link
  • 171
  • 2
  • 19
  • 1
    Remove columns one at a time until the query compiles. Then fix the column or columns causing the error. – Gordon Linoff Apr 10 '18 at 17:35
  • you mean the columns I am selecting? – Link Apr 10 '18 at 17:40
  • 1
    Although each of your two queries has 21 columns, and their names appear to match, it seems at least one column has a different datatype in one query to the corresponding column in the other. (I'm assuming the two queries run individually.) One way to diagnose the problem is to remove columns until the error goes away. – William Robertson Apr 10 '18 at 17:51
  • By the way, you have mixed `or` and `and` conditions in your `where` clauses. Are you sure they are doing what you want? – William Robertson Apr 10 '18 at 17:55
  • 2
    You can't use plain UNION when one of your selected columns is a CLOB. See this duplicate question: https://stackoverflow.com/a/17732879/3061852 – kfinity Apr 10 '18 at 19:32
  • 1
    The SELECT clause seems to be fine. You are fetching the same column names from the same view in the same order. Check if the columns passed to TO_CHAR functions are in fact DATE columns. If they are fine, then you issue is only in the WHERE clause. Compare the column data type and each input value. – ArtBajji Apr 10 '18 at 19:34
  • @WilliamRobertson: Im still going to test the sql once data is available, for your reference though - what i want is when EMPLID is provided and the rest is not, ADM_APPL_NBR and CHECKLIST_CD get every value, while CHECKLIST_STATUS is I or CHECKLIST_STATUS is C when term is current TERM. ADMIT_TERM when blank defaults to Current Term. – Link Apr 11 '18 at 06:11

1 Answers1

2

Column A.COMM_COMMENTS and B.COMM_COMMENTS are CLOB. You can't union CLOBs. One alternative would be do a TO_CHAR(A.COMM_COMMENTS) and TO_CHAR(B.COMM_COMMENTS), then it should work.

Walucas
  • 2,549
  • 1
  • 21
  • 44