Hey all,
We've encountered a rather odd problem where DataDeploy appears to construct invalid queries when using DATE columns in non-root groups. I've narrowed down the issue to the following.
If my column definition looks like this:
<column
name="UPDATE_DATE"
data-type="DATE"
data-format="dd-MMM-yy"
db-datetime-format="DD-MON-YY"
value-from-field="content/0/details/0/update_date/0"
is-replicant="no"
allows-null="no"
is-url="no" />
In a root group, I can see in the logs that the generated query looks like this:
DBD: Col Name: FLOORPLAN_ID, value: 10417
DBD: SELECT
ELECT (snip),TO_CHAR(UPDATE_DATE,'DD-MON-YY') AS UPDATE_DATE,FLOORPLAN_ID FROM FLOORPLAN WHERE FLOORPLAN_ID = ? ORDER BY FLOORPLAN.FLOORPLAN_ID
DBD: Recurse select: pIndex = 0, value = 10417
DBD: BuildTuples:executeQuery returned valid result set
and that works just great.
In a non-root group, it of course adds the table name to all referenced columns to avoid ambiguity, but then when it tries to name the column of the date (with the AS keyword), it uses a name with a period, which is invalid SQL (or at least Oracle doesn't like it):
DBD: TTableSchemaHelper not found in cache for [FLOORPLAN_TEXT]. Creating new.
DBD: SELECTELECT (snip),FLOORPLAN_TEXT.NAME,FLOORPLAN_TEXT.PARAGRAPH_NUM,FLOORPLAN_TEXT.PARAGRAPH_TEXT,FLOORPLAN_TEXT.UPDATED_BY,FLOORPLAN_TEXT.CREATED_BY,TO_CHAR(FLOORPLAN_TEXT.UPDATE_DATE,'DD-MON-YY') AS FLOORPLAN_TEXT.UPDATE_DATE FROM FLOORPLAN_TEXT,FLOORPLAN WHERE FLOORPLAN_TEXT.FLOORPLAN_ID = FLOORPLAN.FLOORPLAN_ID AND FLOORPLAN.FLOORPLAN_ID IN (?) ORDER BY FLOORPLAN_TEXT.FLOORPLAN_ID,FLOORPLAN_TEXT.LOCALE_ID
DBD: Recurse select: pIndex = 0, value = 10417
DBD:
DBD: *******************************************************
DBD: SQLException occured in TDbSchemaGroupInfoNode
DBD: Exception Message: ORA-00923: FROM keyword not found where expected
DBD: Vendor Error Code: 923
DBD: SQL state: 42000
DBD: *******************************************************
If I run that second query in SQL Developer, it fails for the same reason. BUT, if I rename the field from FLOORPLAN_TEXT.UPDATE_DATE to just UPDATE_DATE, then it runs fine in SQL Dev.
I'm assuming this is one for the Interwoven guys, so I'll be submitting a support case, but just thought I'd post it anyway.
TS 6.7.1 SP1
OD 6.2.0
Oracle 10g
Windows 2003 Server
(Attaching complete log snippet and dd config file)
