DD not constructing valid queries when using dates

Rick Poulin

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:

---

<BR /><column <BR /> name="UPDATE_DATE" <BR /> data-type="DATE" <BR /> data-format="dd-MMM-yy"<BR /> db-datetime-format="DD-MON-YY"<BR /> value-from-field="content/0/details/0/update_date/0" <BR /> is-replicant="no" <BR /> allows-null="no" <BR /> is-url="no" /><BR />

---

In a root group, I can see in the logs that the generated query looks like this:

---

<BR />DBD: Col Name: FLOORPLAN_ID, value: 10417<BR />DBD: SELECT<img class="emoticon emoticon-smileyfrustrated" id="smileyfrustrated" src="https://hpg.i.lithium.com/i/smilies/16x16_smiley-frustrated.gif" alt="Smiley Frustrated" title="Smiley Frustrated" />ELECT (snip),<FONT color="DarkOrange"><B>TO_CHAR(UPDATE_DATE,'DD-MON-YY') AS UPDATE_DATE</B></FONT>,FLOORPLAN_ID FROM FLOORPLAN WHERE FLOORPLAN_ID = ? ORDER BY FLOORPLAN.FLOORPLAN_ID<BR />DBD: Recurse select: pIndex = 0, value = 10417<BR />DBD: BuildTuples:executeQuery returned valid result set<BR />

---

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):

---

<BR />DBD: TTableSchemaHelper not found in cache for [FLOORPLAN_TEXT]. Creating new.<BR />DBD: SELECT<img class="emoticon emoticon-smileyfrustrated" id="smileyfrustrated" src="https://hpg.i.lithium.com/i/smilies/16x16_smiley-frustrated.gif" alt="Smiley Frustrated" title="Smiley Frustrated" />ELECT (snip),FLOORPLAN_TEXT.NAME,FLOORPLAN_TEXT.PARAGRAPH_NUM,FLOORPLAN_TEXT.PARAGRAPH_TEXT,FLOORPLAN_TEXT.UPDATED_BY,FLOORPLAN_TEXT.CREATED_BY,<FONT color="DarkOrange"><B>TO_CHAR(FLOORPLAN_TEXT.UPDATE_DATE,'DD-MON-YY') AS FLOORPLAN_TEXT.UPDATE_DATE</B></FONT> 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<BR />DBD: Recurse select: pIndex = 0, value = 10417<BR />DBD: <BR />DBD: *******************************************************<BR />DBD: SQLException occured in TDbSchemaGroupInfoNode<BR />DBD: Exception Message: ORA-00923: FROM keyword not found where expected<BR /> <BR />DBD: Vendor Error Code: 923<BR />DBD: SQL state: 42000<BR />DBD: *******************************************************<BR />

---

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)

Rick Poulin

FYI: Interwoven has said that this is a problem introduced in Patch 1 for OD 6.2.0 (Build 69716) and is not present in plain OD 6.2.0 (Build 68916). They're looking to fix this in the next patch, but removing Patch 1 is apparently a temporary fix. Can't try this myself as we're far too late in the game (in UAT) to rollback something of this magnitude.