I'm creating a flow in the Data Management Console and am running into an issue I can't seem to find answers on.

The field I'm working with is a 'UniqueIdentifier' field according to SQL, which DMC recognizes as A38.

I want SQL to generate a new guid when I insert a row, so in the expression box for this field I enter the command DB_EXPR(NEWID()). SQL has a command (NEWID()) which generates a new guid.

When I Test Transforms, I get the following error message :

0 ERROR AT OR NEAR LINE 41 IN PROCEDURE __WCFEX FOCEXEC *
(FOC32605) NON OPTIMIZABLE EXPRESSION WITH SQL SPECIFIC SYNTAX: DB_EXPR

Can someone tell what I'm doing wrong here?

(Edit: iWay Version 8)

This message has been edited. Last edited by: Tamra,

I think your DEFINE field should be anchored to real field.
Try using DEFINE XXXX/AXX WITH << "existing table field ">>

Avatar is correct, assuming that your transformation is a Source Transformation. In that case if you tested the transformation from the Calculator you would get the somewhat more helpful error:

(FOC36343) SQL SPECIFIC SYNTAX CANNOT BE USED IN CONSTANT DEFINE DB_EXPR(NEWID()). WITH OPTION IS REQUIRED.

To do so from the Calculator press the ellipses after the Format, then scroll down to WITH and select a field.

Alternatively instead of using a Source Transformation you could add the calculation in the Column Selection panel in the SQL Calculator.

But from your description it sounds like you may be attempting this in a Target Transformation. If so, don't. Use one of the two alternatives above.

Thank you both for the suggestions, they're both helpful.

I think the approach that I would like to take is adding the calculation in the Column Selection Panel.

currently the Sql Expression is 'T1.READ_ID'.

With my low skill-level, I was hoping that:
'SET FIELDNAME = NEWID()'
in the Expression box of the Column Selection Panel in the SQL Calculator screen would get the job done, but it errors out w/ a syntax error, so not sure what I'm missing.

Unless one of you fine gentlemen have a simple answer, I'll look into some documentation for this tool.

In the SQL Calculator

Alias: READ_ID
Expression: DB_EXPR(NEWID())

This message has been edited. Last edited by: Clif,

Thanks for making that grade-school simple for me. I was thinking that I could use SQL Directly, removing the need for the DB_EXPR command.

W/ the exception of the underscore in New_ID, it worked perfectly (I only mention this trivial change in case someone as ignorant as myself happens upon this issue in the future).

Thanks Clif!