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
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
