Focal Point
[SOLVED] DMC : DB_EXPR(NEWID()) 'Non Optimizable'

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/1967012386

July 26, 2016, 05:35 PM
A Fisher
[SOLVED] DMC : DB_EXPR(NEWID()) 'Non Optimizable'
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,


WebFOCUS 8.2.0.1 / App Studio 8.2.0.1 (04092014) / Windows 7 / HTML5, PDF, XLS
July 26, 2016, 08:49 PM
Avatar
I think your DEFINE field should be anchored to real field.
Try using DEFINE XXXX/AXX WITH << "existing table field ">>


WebFOCUS 8
Windows, All Outputs
July 27, 2016, 10:55 AM
Clif
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.
July 27, 2016, 08:10 PM
A Fisher
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.


WebFOCUS 8.2.0.1 / App Studio 8.2.0.1 (04092014) / Windows 7 / HTML5, PDF, XLS
July 28, 2016, 09:55 AM
Clif
In the SQL Calculator

Alias: READ_ID
Expression: DB_EXPR(NEWID())

This message has been edited. Last edited by: Clif,
July 28, 2016, 11:55 AM
A Fisher
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!


WebFOCUS 8.2.0.1 / App Studio 8.2.0.1 (04092014) / Windows 7 / HTML5, PDF, XLS