0

I'm connecting throught Qlikview to pl/sql (oracle) and for the same script I use an ODBC connection and the script runs with no issues but when I connect using an OLEDB I receive an error ORA-01861: literal does not match format string. My question is mainly why would my script work using an ODBC connection but not with the OLEDB?

Adding code as I would like to make my query compatabile with the OLE DB Connection...The below is where the hang up is occuring...

SELECT DISTINCT B.CLT_CLT_PGMID,TO_CHAR(B.FIRST_PHONE_CONTACT,'MM/DD/YYYY') AS FIRST_PHONE_CONTACT,
                        ABS(TO_DATE(B.FIRST_ASSGN_DT, 'DD-MM-YYYY') - TO_DATE(B.FIRST_PHONE_CONTACT, 'DD-MM-YYYY')) AS FIRST_Contacted_By_Phone_Days

         FROM (
                SELECT DISTINCT MIN(SERVICE_DATE) AS FIRST_PHONE_CONTACT
                                   ,a.FIRST_ASSGN_DT, a.FIRST_CONTACT_DT, d.is_visit, d.attempt_or_contact,a.PERS_GEN_KEY,a.CLIENT_PROGRAM,a.clt_clt_pgmid

                  FROM  A
                    LEFT JOIN   D ON A.CLT_CLT_PGMID = D.CLT_CLT_PGMID AND A.ID_CARD = D.ID_CARD

                    WHERE 1=1
                   rownum <10

                      GROUP BY  a.FIRST_ASSGN_DT, a.FIRST_CONTACT_DT, d.is_visit, d.attempt_or_contact,a.PERS_GEN_KEY,a.CLIENT_PROGRAM,a.clt_clt_pgmid
                                                                                                                                                         ) B

the below is where the error is occurring ABS(TO_DATE(B.FIRST_ASSGN_DT, 'DD-MM-YYYY') - TO_DATE(B.FIRST_PHONE_CONTACT, 'DD-MM-YYYY')) AS FIRST_Contacted_By_Phone_Days

Toby
  • 135
  • 2
  • 17
  • 3
    Provide code snippet please ! – Sud Jun 30 '16 at 14:29
  • Which drivers do you use? The driver from Microsoft or from Oracle? – Wernfried Domscheit Jun 30 '16 at 16:53
  • For the OLEDB connection Oracle Provider for OLEDB which produces the string error. For ODBC using Microsoft – Toby Jun 30 '16 at 17:23
  • Now I would like to fix my error. The below code works in the client but produces an error when tring to connect via OLE DB. – Toby Jun 30 '16 at 19:07
  • I figured out the correct syntax: ABS(B.FIRST_ASSGN_DT - B.FIRST_PHONE_CONTACT) AS FIRST_Contacted_By_Phone_Days – Toby Jun 30 '16 at 19:35
  • What is the datatype of `FIRST_PHONE_CONTACT`? Your statement is not consistent in that. You do `TO_CHAR(B.FIRST_PHONE_CONTACT,'MM/DD/YYYY')` which suggest that it is a `DATE` (or `TIMESTAMP`) datatype but `TO_DATE(B.FIRST_ASSGN_DT, 'DD-MM-YYYY'` suggest that it could be a `VARCHAR2` datatype (which would be the weak option) – Wernfried Domscheit Jul 01 '16 at 06:42
  • FIRST_PHONE_CONTACT would be a date and I now realize why I was receiving a string error. I have updated my query and this now works perfectly in both Oracle and in an OLE DB connection. Thanks for the help! – Toby Jul 01 '16 at 13:44

2 Answers2

2

Have a look at this on your PC:

For OLE DB it says:

The date format for the Oracle session cannot be set using the ALTER SESSION SET NLS_DATE_FORMAT command. In Visual Basic, date formats are controlled by the Regional Settings properties in Windows Control Panel. For more information on Visual Basic date formats, refer to your Visual Basic documentation.

For Oracle Provider for OLE DB, if the Connection property UseSessionFormat is FALSE, which is a default value, then NLS_DATE_FORMAT is fixed for the session to 'YYYY-MM-DD HH24:MI:SS' by the provider. If you pass the date to Oracle Database as a string, the date must be in the 'YYYY-MM-DD HH24:MI:SS' format. If UseSessionFormat is TRUE, then NLS_DATE_FORMAT is not fixed by Oracle Provider for OLE DB and the default session NLS_DATE_FORMAT is used.

The behavior at ODBC might be different.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • This helps me as I did not realize there could be different behaviors. Now I would like to update my query to resolve the issue – Toby Jun 30 '16 at 19:06
0

Ideally both should work. Please refer Post

  • ODBC provides access only to relational databases
  • OLE DB provides the following features
    • Access to data regardless of its format or location
  • Full access to ODBC data sources and ODBC drivers

So it would seem that OLE DB interacts with SQL-based datasources THRU the ODBC driver layer.

alt text

Community
  • 1
  • 1
Sud
  • 153
  • 4