All Forums UDA
JustMe 76 posts Joined 03/07
12 Mar 2007
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?

RGlass 35 posts Joined 09/04
12 Mar 2007

Post your create ddl,may be able to help.

JustMe 76 posts Joined 03/07
13 Mar 2007

FYI - this macro was created in our test system; I'm getting this error when I try to create it in the production environment...explainREPLACE 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 DESCRIPTIONfrom(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 DESCRIPTIONfrom(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 ) a1UNION ALLselect STYLE_TYPE, DIVN_ABBR, DIVN_NBR, KEY1, KEY2, KEY3, 'Mstyl: ' || TRIM(DIVN_ABBR) || '/' || TRIM(KEY1) || '/' || TRIM(KEY2) || ' - ' || DESCRIPTION AS DESCRIPTION -- DESCRIPTION AS DESCRIPTIONfrom(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_NBRWHERE SUPER_ITEM_NBR = :SUPER_ITEM_NBR AND KEY_ITEM_NBR = :KEY_ITEM_NBR) ms1UNION ALLselect STYLE_TYPE, DIVN_ABBR, DIVN_NBR, KEY1, KEY2, KEY3, 'Vstyl: ' || TRIM(DIVN_ABBR) || '/' || TRIM(KEY1) || '/' || TRIM(KEY2) || '/' || TRIM(KEY3) || ' - ' || DESCRIPTION AS DESCRIPTION -- DESCRIPTION AS DESCRIPTIONfrom(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) V1ORDER BY 4 ;-) ;

JustMe 76 posts Joined 03/07
13 Mar 2007

The problem was that I had the statement to set my database right before the 'create macro' statement. Thanks for looking at it!

You must sign in to leave a comment.