Odd Error when attempting to create a macro

UDA
Enthusiast

Odd Error when attempting to create a macro

Statement 2 - < Compound statement not alone >

I'm receiving the above error when attempting to create a macro via SQL Assistant. I've never seen an error like this in Teradata before. Any ideas on where to find documentation? Or does anyone know exactly why I'm receiving this error?
3 REPLIES
Enthusiast

Re: Odd Error when attempting to create a macro

Post your create ddl,
may be able to help.

Enthusiast

Re: Odd Error when attempting to create a macro

FYI - this macro was created in our test system; I'm getting this error when I try to create it in the production environment...

explain
REPLACE MACRO GCR_M_V50_LIST_KEY_STYLES(DIVN_NBR INTEGER, SUPER_ITEM_NBR INTEGER, KEY_ITEM_NBR INTEGER) as(

select STYLE_TYPE, DIVN_ABBR, DIVN_NBR, KEY1, KEY2, KEY3,
DESCRIPTION AS DESCRIPTION

from
(

select STYLE_TYPE, DIVN_ABBR, DIVN_NBR, KEY1, KEY2, KEY3,
'Mkst: ' || TRIM(DIVN_ABBR) || '/' || TRIM(KEY1) || '/' || TRIM(KEY2) || '/' || TRIM(KEY3) || ' - ' || DESCRIPTION AS DESCRIPTION
-- DESCRIPTION AS DESCRIPTION

from

(select
'Mkst' as STYLE_TYPE, D.DIVN_ABBR, K.DIVN_NBR, CAST(k.DEPT_NBR AS CHAR(04)) as KEY1,
CAST(k.VND_NUMERIC_DESC AS CHAR(10)) AS KEY2,
CAST(k.MKST AS CHAR(10)) AS KEY3,
COALESCE(m.STYLE_DESC, cast('Not found' as varchar(30))) as DESCRIPTION
from KEY_ITEM_MKST_V k
INNER JOIN OPER_DIVN_V d
on d.divn_nbr = k.divn_nbr
LEFT OUTER JOIN MARKSTYLE_V m
on k.divn_nbr = m.divn_nbr
and k.dept_nbr = m.dept_nbr
and k.vnd_numeric_desc = m.vnd_numeric_desc
and k.mkst = m.mkst
WHERE SUPER_ITEM_NBR = :SUPER_ITEM_NBR AND
KEY_ITEM_NBR = :KEY_ITEM_NBR) M1 ) a1

UNION ALL

select STYLE_TYPE, DIVN_ABBR, DIVN_NBR, KEY1, KEY2, KEY3,
'Mstyl: ' || TRIM(DIVN_ABBR) || '/' || TRIM(KEY1) || '/' || TRIM(KEY2) || ' - ' || DESCRIPTION AS DESCRIPTION
-- DESCRIPTION AS DESCRIPTION

from

(select
'Mstyl' as STYLE_TYPE, d.DIVN_ABBR, K.DIVN_NBR, CAST(k.ZL_DIV_MSTSTYL_NBR AS CHAR(04)) AS KEY1,
CAST(k.ZL_MSTSTYL_NBR AS CHAR(10)) as KEY2,
cast(0 as CHAR(10)) AS KEY3,
COALESCE(m.MSTSTYL_DESC, cast('Not found' as varchar(30))) as DESCRIPTION
from KEY_ITEM_MSTSTYL_V k
INNER JOIN OPER_DIVN_V d
on d.divn_nbr = k.divn_Nbr
LEFT OUTER JOIN DIVN_MSTSTYL_V m
on k.divn_nbr = m.divn_nbr
and k.ZL_MSTSTYL_NBR = m.ZL_MSTSTYL_NBR
and k.ZL_DIV_MSTSTYL_NBR = m.ZL_DIV_MSTSTYL_NBR
WHERE SUPER_ITEM_NBR = :SUPER_ITEM_NBR AND
KEY_ITEM_NBR = :KEY_ITEM_NBR) ms1

UNION ALL
select STYLE_TYPE, DIVN_ABBR, DIVN_NBR, KEY1, KEY2, KEY3,
'Vstyl: ' || TRIM(DIVN_ABBR) || '/' || TRIM(KEY1) || '/' || TRIM(KEY2) || '/' || TRIM(KEY3) || ' - ' || DESCRIPTION AS DESCRIPTION
-- DESCRIPTION AS DESCRIPTION

from

(select
'Vstyl' as STYLE_TYPE, m.DIVN_ABBR, K.DIVN_NBR, CAST(k.DEPT_NBR AS CHAR(04)) as KEY1,
CAST(k.VND_NUMERIC_DESC AS CHAR(10)) AS KEY2,
CAST(k.ZL_VSTYLE AS CHAR(10)) AS KEY3,
COALESCE(m.STYLE_DESC, cast('Not found' as varchar(30))) as DESCRIPTION
from KEY_ITEM_VSTYLE_V k
LEFT OUTER JOIN
(select DIVN_ABBR, DIVN_NBR, DEPT_NBR, VND_NUMERIC_DESC, ZL_VSTYLE,
max(STYLE_DESC) as STYLE_DESC
FROM
( select d.divn_abbr, K.DIVN_NBR, k.DEPT_NBR, k.VND_NUMERIC_DESC, k.ZL_VSTYLE,
coalesce(p.STYLE_DESC, cast('Not found' as varchar(30))) as STYLE_DESC
from KEY_ITEM_VSTYLE_V k
INNER JOIN OPER_DIVN_V d
on d.divn_nbr = k.divn_nbr
LEFT OUTER JOIN PROD_DIM_V p
on k.divn_nbr = p.divn_nbr
and k.dept_nbr = p.dept_nbr
and k.vnd_numeric_desc = p.vnd_numeric_desc
and k.ZL_VSTYLE = p.ZL_VSTYLE
WHERE SUPER_ITEM_NBR = :SUPER_ITEM_NBR AND
KEY_ITEM_NBR = :KEY_ITEM_NBR) p1
group by 1, 2, 3, 4, 5) m

ON k.divn_nbr = m.divn_nbr
and k.dept_nbr = m.dept_nbr
and k.vnd_numeric_desc = m.vnd_numeric_desc
and k.ZL_VSTYLE = m.ZL_VSTYLE
WHERE SUPER_ITEM_NBR = :SUPER_ITEM_NBR AND
KEY_ITEM_NBR = :KEY_ITEM_NBR) V1

ORDER BY 4
;-) ;
Enthusiast

Re: Odd Error when attempting to create a macro

The problem was that I had the statement to set my database right before the 'create macro' statement.

Thanks for looking at it!