Thanks, how exactly does one check out one's ODBC/JDBC drivers and/or update them? I've never had to mess with these before.
It's amazing what a little sleep can accomplish. I fought with this nearly all day yesterday trying this way and that way to get the data correct from the original query. Then this morning it hit me. Query of a Query. I use one query that gets all the data from the Oracle 8.1.7 database through the faulty JDBC driver which works fine just as long as I don't ask it to order the data. Then a simple query of a query to order that data the way I need it. Works great and is just as fast. Cool Beans. -------------- Ian Skinner Web Programmer BloodSource Sacramento, CA -----Original Message----- From: Joe Eugene [mailto:[EMAIL PROTECTED] Sent: Monday, July 07, 2003 8:13 PM To: CF-Talk Subject: RE: ColdFusion MX Oracle JDBC driver > Ok, this one is beyond me to figure out. I'm wondering if it's > some kind of > bug in the JDBC driver or something. What JDBC Driver are you using? Try the Oracle Supplied JDBC TYPE IV NATIVE Driver from Oracle's website. That should do the trick. Joe Eugene > -----Original Message----- > From: Ian Skinner [mailto:[EMAIL PROTECTED] > Sent: Monday, July 07, 2003 4:31 PM > To: CF-Talk > Subject: ColdFusion MX Oracle JDBC driver > > > ColdFusion MX no updates on Windows 2000. > Oracle 9i on Unix HP3000 > > Ok, this one is beyond me to figure out. I'm wondering if it's > some kind of > bug in the JDBC driver or something. > > I have the enclosed query, that won't let me sort on the time fields > (R_MDT_DRWFTME, R_MDT_DRWTTME). If I put either of these two fields into > the ORDER BY clause, I receive the following uninformative error. > > [MERANT][SequeLink JDBC Driver][ODBC Socket][Oracle][ODBC] Restricted data > type attribute violation. > > What is weird about it, is that a simpler form of the query (see > second code > example) works just fine and does sort on these time fields. But, when I > added the embedded query into the from clause, I started getting > this error. > > > By process of elimination, I tracked it down to the time fields > in my ORDER > BY clause. I thought maybe I was having difficulties do to a > NULL value in > the data, so I added the NVL2() function to the two time fields. If I > substitute other numbers for the real values I get no error, but if I use > the correct time values for non-null fields I continue to get the error. > The problem seems to be somewhere between the DBMS and the application > server. I can run the query with no problems directly on the > database with > the "SQL-Plus" Oracle development tool. > > Any suggestions and how I can either work around or eliminate this problem > would be greatly appreciated. > > Thank You > -------------- > Ian Skinner > Web Programmer > BloodSource > Sacramento, CA > > ********************************* > *** NON FUNCTIONING QUERY *** > ********************************* > SELECT > MDL.R_MDL_MOBLID, > MDL.R_MDL_DRAWDTE, > MDL.R_MDL_RID, > MDL.R_MDL_BEDCNT, > MDL.R_MDL_LOCTYP, > > SUB.DRAWDTEDIFF, > > MDO.R_MDO_PROJDRW, > MDO.R_MDO_TOTDRAW, > > MOB.R_MOB_MNAME, > > NVL2(MDT.R_MDT_DRWFTME,R_MDT_DRWFTME,0) AS R_MDT_DRWFTME, > NVL2(MDT.R_MDT_DRWTTME,R_MDT_DRWTTME,0) AS R_MDT_DRWTTME, > > LOC.N_LOC_AREA > > FROM > EBIS.REC_MDL_DB_REC MDL, > (SELECT > ONE.R_MDL_MOBLID, > ONE.R_MDL_DRAWDTE, > MIN(TO_DATE(ONE.R_MDL_DRAWDTE,'YYYYMMDD') - > TO_DATE(TWO.R_MDL_DRAWDTE,'YYYYMMDD')) AS DRAWDTEDIFF > > FROM > EBIS.REC_MDL_DB_REC ONE, > EBIS.REC_MDL_DB_REC TWO > > WHERE > ONE.R_MDL_INSTID = TWO.R_MDL_INSTID(+) AND > ONE.R_MDL_MOBLID = TWO.R_MDL_MOBLID(+) AND > ONE.R_MDL_DRAWDTE > TWO.R_MDL_DRAWDTE(+) AND > > ONE.R_MDL_INSTID = ' ' AND > > (ONE.R_MDL_DRAWDTE BETWEEN > <cfqueryparam value="#DateFormat(firstDay,'yyyymmdd')#" > cfsqltype="cf_sql_char" maxlength="8"> > AND > <cfqueryparam value="#DateFormat(lastDay,'yyyymmdd')#" > cfsqltype="cf_sql_char" maxlength="8">) > > > GROUP BY > ONE.R_MDL_DRAWDTE, > ONE.R_MDL_MOBLID > ) SUB, > EBIS.REC_MDO_DB_REC MDO, > EBIS.REC_MOB_DB_REC MOB, > EBIS.REC_MDT_DB_REC MDT, > EBIS.NAT_LOC_DB_REC LOC > > WHERE > MDL.R_MDL_MOBLID = SUB.R_MDL_MOBLID AND > MDL.R_MDL_DRAWDTE = SUB.R_MDL_DRAWDTE AND > > MDL.R_MDL_INSTID = MDO.R_MDO_INSTID(+) AND > MDL.R_MDL_MOBLID = MDO.R_MDO_MOBLID(+) AND > MDL.R_MDL_DRAWDTE = MDO.R_MDO_DRAWDTE(+) AND > > MDL.R_MDL_INSTID = MOB.R_MOB_INSTID(+) AND > MDL.R_MDL_MOBLID = MOB.R_MOB_MOBLID(+) AND > > MDL.R_MDL_INSTID = MDT.R_MDT_INSTID(+) AND > MDL.R_MDL_MOBLID = MDT.R_MDT_MOBLID(+) AND > MDL.R_MDL_DRAWDTE = MDT.R_MDT_DRAWDTE(+) AND > > MDL.R_MDL_INSTID = LOC.N_LOC_INSTID(+) AND > MDL.R_MDL_LOCCD = LOC.N_LOC_LOCCD(+) AND > > MDL.R_MDL_INSTID = ' ' AND > > (MDL.R_MDL_DRAWDTE BETWEEN <cfqueryparam > value="#DateFormat(firstDay,'yyyymmdd')#" cfsqltype="cf_sql_char" > maxlength="8"> > AND > <cfqueryparam value="#DateFormat(lastDay,'yyyymmdd')#" > cfsqltype="cf_sql_char" maxlength="8">) > <cfif url.locationcode NEQ 'no'> > AND LOC.N_LOC_AREA = <cfqueryparam > value="#UCase(url.LocationCode)#" cfsqltype="cf_sql_char" maxlength="2"> > </cfif> > > ORDER BY > MDL.R_MDL_DRAWDTE, > LOC.N_LOC_AREA, > R_MDT_DRWFTME, > R_MDT_DRWTTME, > MDL.R_MDL_MOBLID > > **** END NON-FUNCTIONING QUERY **** > > > > ************************* > *** FUNCTIONING QUERY *** > ************************* > SELECT > REC_MDL_DB_REC.R_MDL_INSTID, > REC_MDL_DB_REC.R_MDL_DRAWDTE, > REC_MDL_DB_REC.R_MDL_RID, > REC_MDL_DB_REC.R_MDL_MOBLID, > REC_MDL_DB_REC.R_MDL_BEDCNT, > REC_MDL_DB_REC.R_MDL_LOCTYP, > > REC_MDO_DB_REC.R_MDO_PROJDRW, > REC_MDO_DB_REC.R_MDO_TOTDRAW, > > REC_MOB_DB_REC.R_MOB_MNAME, > > REC_MDT_DB_REC.R_MDT_DRWFTME, > REC_MDT_DB_REC.R_MDT_DRWTTME, > > NAT_LOC_DB_REC.N_LOC_AREA > > FROM > EBIS.REC_MDL_DB_REC REC_MDL_DB_REC, > EBIS.REC_MDO_DB_REC REC_MDO_DB_REC, > EBIS.REC_MOB_DB_REC REC_MOB_DB_REC, > EBIS.REC_MDT_DB_REC REC_MDT_DB_REC, > EBIS.NAT_LOC_DB_REC NAT_LOC_DB_REC > > WHERE > REC_MDL_DB_REC.R_MDL_INSTID = REC_MDO_DB_REC.R_MDO_INSTID(+) > AND > REC_MDL_DB_REC.R_MDL_MOBLID = REC_MDO_DB_REC.R_MDO_MOBLID(+) > AND > REC_MDL_DB_REC.R_MDL_DRAWDTE = > REC_MDO_DB_REC.R_MDO_DRAWDTE(+) AND > > REC_MDL_DB_REC.R_MDL_INSTID = REC_MOB_DB_REC.R_MOB_INSTID(+) > AND > REC_MDL_DB_REC.R_MDL_MOBLID = REC_MOB_DB_REC.R_MOB_MOBLID(+) > AND > > REC_MDL_DB_REC.R_MDL_INSTID = REC_MDT_DB_REC.R_MDT_INSTID(+) > AND > REC_MDL_DB_REC.R_MDL_MOBLID = REC_MDT_DB_REC.R_MDT_MOBLID(+) > AND > REC_MDL_DB_REC.R_MDL_DRAWDTE = > REC_MDT_DB_REC.R_MDT_DRAWDTE(+) AND > > REC_MDL_DB_REC.R_MDL_INSTID = NAT_LOC_DB_REC.N_LOC_INSTID(+) > AND > REC_MDL_DB_REC.R_MDL_LOCCD = NAT_LOC_DB_REC.N_LOC_LOCCD(+) > AND > > REC_MDL_DB_REC.R_MDL_INSTID = ' ' AND > > (REC_MDL_DB_REC.R_MDL_DRAWDTE BETWEEN <cfqueryparam > value="#DateFormat(firstDay,'yyyymmdd')#" cfsqltype="cf_sql_char" > maxlength="8"> > > AND <cfqueryparam value="#DateFormat(lastDay,'yyyymmdd')#" > cfsqltype="cf_sql_char" maxlength="8">) > <cfif url.locationcode NEQ 'no'> > AND NAT_LOC_DB_REC.N_LOC_AREA = <cfqueryparam > value="#UCase(url.LocationCode)#" cfsqltype="cf_sql_char" maxlength="2"> > </cfif> > > ORDER BY > REC_MDL_DB_REC.R_MDL_DRAWDTE, > NAT_LOC_DB_REC.N_LOC_AREA, > REC_MDT_DB_REC.R_MDT_DRWTTME, > REC_MDT_DB_REC.R_MDT_DRWFTME, > REC_MDL_DB_REC.R_MDL_MOBLID > > **** END FUNCTIONING QUERY **** > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
