SQL Error : SQL Scriptline SQL State:00000

Mon, 25 Oct 2010 17:40:32 GMT

Hi...

I am getting this error when I am loading it in Qlikview. When I run the same query in Toad, the query works fine. I am not able to locate the problem here. Any help would be really appreciated.

SQL Error:
SQL Scriptline:
SQL State:00000
Decline_Chart:

SQL select nvl(sp_user.middle,'Not Available') SP_USER,nvl(sp_user2.middle,'Not Available') SP_USER_COMPLETE,
res_first_name || ' ' || res_last_name RES_NAME, decode(flat_request.req_type,1,'SP','TP') || req_oid ORDER_NUM,
decode(flat_request.req_type,1,'SP','TP') || nvl(main_req_oid,req_oid) MAIN_ORDER,
order_stat(req_status) ORDER_STAT, audit_date_mpsent ORDER_DATE, TIMES_IN_PUF PUF_NUMBER,
HOURS_MINS_SECS_BETWEEN(audit_date_msent,audit_date_mpsent,'N/A') TIME_TO_OPEN,
HOURS_MINS_SECS_BETWEEN(nvl(nvl(audit_date_accept,audit_date_decline),audit_date_cancel),audit_date_msent,
'N/A') TIME_TO_PROCESS, HOURS_MINS_SECS_BETWEEN(nvl(nvl(audit_date_accept,audit_date_decline),
audit_date_cancel),audit_date_mpsent,'N/A') TOTAL_TIME, svc_prov_name SP_NAME,
svc_cat_prov_name || ' (' || svc_cat_name || ')' SCP_NAME, RESPONSE, PCOMMENT, parent.name REGION,
svc_address_State, SVC_ADDRESS_ZIPCODE ZIP9, SVC_ADDRESS_CITY, decode(psr.DECLINED_BY_PUF_FLAG, 1, 'PUF Cutoff', ref.name) DECLINE_REASON,
nvl(REF_PUF.NAME,' ') PUF_REASON, decode(psr.DECLINED_BY_PUF_FLAG,1,'X',' ') PUF_DECLINE,
decode(ORDER_TYPE,'M','P','B','S','I','S',' ') ORDER_TYPE
from flat_request, sp_user, sp_user sp_user2,
(Select oid, error_info response, PROVIDER_COMMENT pcomment from psr_account) pa,
configuration_manager_new cmn, configuration_manager_new parent, REF_DECLINE_CODE REF, system_request psr,
ref_puf_reason REF_PUF
where req_oid = psr.oid and pa.oid = decode(ORDER_TYPE,'I', req_oid, nvl(main_req_oid,req_oid))
and psr.OID_DECLINE_CODE = ref.oid (+) and psr.OID_PUF_CODE = REF_PUF.OID (+)
and SPUSER_COMPLETE_OID = sp_user2.oid (+) and SPUSER_OID = sp_user.oid (+)
and req_status in (9,14,16) and audit_date_mpsent is not null and flat_request.req_type = 1
and cmn.oid = cm_oid and cmn.oid_parent = parent.oid
and flat_request.SVC_PROV_OID = 1000494
and trunc(flat_request.REQ_ORDER_DATE) between trunc(last_day(Add_Months(sysdate-395,-1))+1) and trunc(last_day(Add_Months(sysdate,-1)))
order by svc_prov_oid, svc_cat_prov_oid, sp_user.middle desc, req_status, req_order_date desc;

Thank you.

Regards

Kumar

SQL Error : SQL Scriptline SQL State:00000

pover — Mon, 25 Oct 2010 18:10:27 GMT

When QlikView returns a SQL error, it usually orginates from the ODBC driver and not QlikView.

There could be a function that the ODBC driver doesn't support. Try taking apart the SQL statement and running is in parts to see what function or column is causing the problem.

Also check that you have the proper permissions and that all the columns have an alias.

Regards.

SQL Error : SQL Scriptline SQL State:00000

Mon, 25 Oct 2010 18:23:46 GMT

Hi Karl,

Thanks for the suggestion. I did exactly what you said and identified that there were two columns which were of CLOB datatypes and were not being handled by Qliview. I changed the ODBC driver to OLEDB driver and it is working fine now. So, as you mentioned the problem was not with qlikview but the driver.

Thank you.

Regards

Kumar

topic SQL Error : SQL Scriptline SQL State:00000 in QlikView

SQL Error : SQL Scriptline SQL State:00000

SQL Error : SQL Scriptline SQL State:00000

SQL Error : SQL Scriptline SQL State:00000