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()
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