error in create macro 1

[Lesio]

The SQL inside the macro works fine, but the macro create statement generates 3706: syntax error: expected something between '(' and '('

Any idea why ?

create MACRO OLAPV2_DB.LOCAL_BUNDLE_6
AS
(

DELETE FROM OLAPV2_DB.LU_MIN_DT_SUMMARY
WHERE
MIN_DT = LTRIM(((ADD_MONTHS(DATE,-13) - (EXTRACT (DAY FROM ADD_MONTHS(DATE,-13)) -1))/100 + 190000) (CHAR(11)))
;

);

 

[dnoeth]

It's probably because you use LTRIM, this is not a valid Teradata SQL function.
This query will only run in Queryman, when Options -> Query -> "Allow use of ODBC SQL Extensions in queries" is checked.

So try to replace it with TRIM(LEADING FROM ...).

Btw, why do you use that complex calculation?
(ADD_MONTHS(DATE,-13) (format 'YYYYMM')) (CHAR(6))
will do the same...

Dieter

 

[Lesio]

Thanks for this shortcut and an idea to go around ltrim.