Reputation: 924
ORA-00932 error. Not sure how to fix
I'm attempting to run this Oracle query...
SELECT COUNT(*) as total,
q1
FROM exit_responses
WHERE sdate BETWEEN '03-Aug-10 12:00:00 AM' AND '03-Nov-10 12:00:00 AM'
GROUP BY q1;
...but I keep getting this error...
Error starting at line 3 in command:
SELECT COUNT(*) as total, q1 FROM exit_responses WHERE sdate BETWEEN '03-Aug-10 12:00:00 AM' AND '03-Nov-10 12:00:00 AM' GROUP BY q1
Error at Command Line:3 Column:130
Error report:
SQL Error: ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Anyone have any ideas? Says it's an inconsistent data type... but I guess I'm not understanding completely.
Thanks
Btw, here's the DESC of my exit_responses table:
DESC exit_responses
Name Null Type
------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SDATE DATE
F_NAME VARCHAR2(255 CHAR)
L_NAME VARCHAR2(255 CHAR)
TITLE VARCHAR2(255 CHAR)
DEPARTMENT VARCHAR2(255 CHAR)
EMP_TYPE VARCHAR2(11 CHAR)
LENGTH_OF_SERVICE VARCHAR2(255 CHAR)
Q1 CLOB()
Q2 CLOB()
Q2_OTHER CLOB()
Q3_PAY NUMBER
Q3_HOLIDAYS NUMBER
Q3_VACATION NUMBER
Q3_SICK NUMBER
Q3_INSURANCE NUMBER
Q3_RETIREMENT NUMBER
Q3_FSA NUMBER
Q4_AVAILABILITY NUMBER
Q4_QUALITY NUMBER
Q4_SATISFACTION NUMBER
Q4_COMMENTS NUMBER
Q5_ORIENTATION NUMBER
Q5_POLICIES NUMBER
Q5_PROMOTIONAL NUMBER
Q6_JOBDUTIES NUMBER
Q6_RELATIONSHIPS NUMBER
Q6_COOPERATION NUMBER
Q6_EQUIPMENT NUMBER
Q6_CONDITIONS NUMBER
Q6_SAFETY NUMBER
Q7 NUMBER
Q8_KNOWLEDGE NUMBER
Q8_DELEGATION NUMBER
Q8_OBSERVANCE NUMBER
Q8_FEEDBACK NUMBER
Q8_CONTRIBUTIONS NUMBER
Q8_LISTENED NUMBER
Q8_COMPLAINTS NUMBER
Q9 VARCHAR2(3 CHAR)
Q9_DESCRIBE CLOB()
Q10 CLOB()
Q11 NUMBER
Q11_COMMENTS CLOB()
Q12 NUMBER
Q12_DESCRIBE CLOB()
ADDITIONAL_COMMENTS CLOB()
Upvotes: 1
Views: 4907
Answers (5)
Reputation: 19956
Not knowing anything about ORACLE, I would bet that you in fact can't GROUP BY on CLOB fields. It makes perfect sense...
Also, look at this. Excerpt:
This was significant from Oracle perspective because you cannot do a group by on a clob. In order to do a group by, you need to be able to sort and clob fields do not have a natural sort order. The workaround is to do a to_char function on the clob field but you can get a max of 4000 chars. For me, this suited the requirement because the substring I needed was a small string. But the issue is that I needed to sort a portion of an XML tag inside the clob field and group them based on that. Not only that I needed to find a specific type of error in the XML message and count them.
Upvotes: 1
Reputation: 231671
What is the data type of the SDATE column? Assuming it is a DATE, you almost certainly want to use explicit TO_DATE calls to convert your strings to dates, i.e.
WHERE sdate BETWEEN to_date('03-Aug-2010', 'DD-MON-YYYY')
AND to_date( '03-Nov-2010', 'DD-MON-YYYY' )
Since midnight is the default time when none is specified, you don't necessarily need the 12:00:00 AM bit. If you want to include that
WHERE sdate BETWEEN to_date('03-Aug-2010 12:00:00 AM', 'DD-MON-YYYY HH:MI:SS AM')
AND to_date( '03-Nov-2010 12:00:00 AM', 'DD-MON-YYYY HH:MI:SS AM' )
If SDATE is a DATE and you want the BETWEEN to take two different Unix epochs (milliseconds since Jan 1, 1970) rather than strings, you would want something like
WHERE sdate BETWEEN date '1970-01-01' + :1/86400000
AND date '1970-01-01' + :2/86400000
where :1 and :2 are the two bind variables. If your epochs are seconds since Jan 1, 1970
WHERE sdate BETWEEN date '1970-01-01' + :1/86400
AND date '1970-01-01' + :2/86400
Upvotes: 2
Reputation: 10517
i think, it's better to use to_date function for that.
where sdate between
TO_DATE('03.08.2010:00:00:0','DD.MM.YYYY:HH24:MI:SS') and TO_DATE('03.10.2010:00:00:0','DD.MM.YYYY:HH24:MI:SS')
Upvotes: 0
Reputation: 19635
my guess is that sdate is a DATE or DATETIME column, in which case you need to convert your date strings:
SELECT COUNT(*) as total,
q1
FROM exit_responses
WHERE sdate BETWEEN to_date('03-Aug-10 12:00:00 AM') AND to_date('03-Nov-10 12:00:00 AM')
GROUP BY q1;
Upvotes: 1
Reputation: 332581
Assuming sdate's date type is Oracle DATE, use the TO_DATE function to convert a string to a DATE in Oracle:
SELECT COUNT(*) as total,
q1
FROM exit_responses
WHERE sdate BETWEEN TO_DATE('03-Aug-10 12:00:00 AM', 'DD-MON-YY HH12:MI:SS AM')
AND TO_DATE('03-Nov-10 12:00:00 AM', 'DD-MON-YY HH12:MI:SS AM')
GROUP BY q1;
Upvotes: 3