Hi,
Trying to find a difference between SQL. vs DB_EXPR
Seems like it finctions the same way. Is one prefered over the other.

I am planning to use it with a DEFINE.

Read in the documentation that:
DB_EXPR is used in a DEFINE with a WITH phrase.

Not sure how to use it WITH?

Looking for expert advise.

Thanks

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

Hi BM,

Check the documentation, there is an example of the use of DB_EXPR.

e.g.
DB_EXPR

As for the differences, you will need to check the SQL generated to compare, using SQL tracing

WITH is just a way to associate some info that is not in the table with a row in the table.

e.g.
Field/format WITH DBField = FUNCTION...

The SQL. is for direct reference of a sql function, where the DB_EXPR you can enter an full expression. That being said, you can reference a sql function using the DB_EXPR.

Example (MS SQL):

PASCALESUM/I11  WITH DMHR.ID_NUM=DB_EXPR(SUM(PAYSCALE) OVER(PARTITION BY PLANT));
START_DOW/A10  WITH DMHR.ID_NUM=DB_EXPR( DATENAME( dw,  START_DATE ) );
START_MONTH/I2=SQL.MONTH( START_DATE );   

The WITH is not always required with the SQL. prefix, but it doesn't hurt to add it.

Even when calling a function with SQL. it's limited. The parameters must be columns, constants or expressions.

If you want to call a function that takes a keyword then use DB_EXPR. For example the MS SQL Server function CONVERT takes a parameter of datatype such as CHAR:

DB_EXPR(CONVERT(CHAR, "start_date", 102))

Thanks all

quote:

it's limited.

One of the most limiting is (or was) that only one pair of parenthesis can be used in SQL.

T