Hello,

I have 3 SQL statements, each of which is running OK

sql1:

SELECT bt.DEPARTMENT,
bt.TEAM,
ba.AREA,
bp.PRODUCT,
aty.SUBSIDIARY_ACCOUNT_CODE,
bac.ACTIVITY_TYPE_CODE,
CAST(null AS DATE) AS TRANS_DATE,
CAST(null AS INTEGER) AS MARKETING_EXPENSE_NUMBER,
CAST(null AS INTEGER) AS ADJUSTMENT_NUMBER,
CAST(null AS VARCHAR(30)) AS STAFF_NAME,
CAST(null AS VARCHAR(50)) AS PARTICULAR,
CAST(null AS NUMERIC(11,2)) AS APPROVED_AMOUNT,
bp.BUDGET_AMOUNT
FROM BUDGET_TEAM bt
JOIN BUDGET_AREA ba ON ba.TEAM_ID = bt.ID
JOIN BUDGET_PRODUCT bp ON bp.AREA_ID = ba.ID
JOIN BUDGET_ACTIVITY bac ON bac.PRODUCT_ID = bp.ID
LEFT OUTER JOIN ACTIVITY_TYPE_MASTER aty ON aty.ACTIVITY_TYPE_CODE =
bac.ACTIVITY_TYPE_CODE
WHERE bt.BUDGET_YEAR = 2004

sql2:

SELECT bt.DEPARTMENT,
bt.TEAM,
ba.AREA,
bp.PRODUCT,
aty.SUBSIDIARY_ACCOUNT_CODE,
bac.ACTIVITY_TYPE_CODE,
me.SUBMIT_DATE AS TRANS_DATE,
me.MARKETING_EXPENSE_NUMBER,
CAST(null AS INTEGER) AS ADJUSTMENT_NUMBER,
s.STAFF_NAME,
me.EVENT AS PARTICULAR,
mea.APPLIED_BASE AS APPROVED_AMOUNT,
bp.BUDGET_AMOUNT
FROM BUDGET_TEAM bt
JOIN BUDGET_AREA ba ON ba.TEAM_ID = bt.ID
JOIN BUDGET_PRODUCT bp ON bp.AREA_ID = ba.ID
JOIN BUDGET_ACTIVITY bac ON bac.PRODUCT_ID = bp.ID
LEFT OUTER JOIN MARKETING_EXPENSE_DETAIL mea ON mea.DEPARTMENT =
bt.DEPARTMENT AND mea.TEAM = bt.TEAM AND mea.AREA = ba.AREA AND
mea.PRODUCT = bp.PRODUCT
LEFT OUTER JOIN MARKETING_EXPENSE me ON me.MARKETING_EXPENSE_ID =
mea.MARKETING_EXPENSE_ID AND me.ACTIVITY_TYPE_CODE =
bac.ACTIVITY_TYPE_CODE
LEFT OUTER JOIN ACTIVITY_TYPE_MASTER aty ON aty.ACTIVITY_TYPE_CODE =
bac.ACTIVITY_TYPE_CODE
LEFT OUTER JOIN ORG_STAFF s ON s.STAFF_CODE = me.STAFF_CODE
WHERE me.STATUS IN ('V','F','Y')
AND (me.PERIOD_FROM_DATE BETWEEN '2004-01-01' AND '2004-12-31')
AND bt.BUDGET_YEAR = 2004

sql3:

SELECT bt.DEPARTMENT,
bt.TEAM,
ba.AREA,
bp.PRODUCT,
aty.SUBSIDIARY_ACCOUNT_CODE,
bac.ACTIVITY_TYPE_CODE,
aj.ADJUSTMENT_DATE AS TRANS_DATE,
al.MARKETING_EXPENSE_NUMBER,
al.ADJUSTMENT_NUMBER,
s.STAFF_NAME,
aj.REMARK AS PARTICULAR,
al.APPROVED_AMOUNT AS APPROVED_AMOUNT,
bp.BUDGET_AMOUNT
FROM BUDGET_TEAM bt
JOIN BUDGET_AREA ba ON ba.TEAM_ID = bt.ID
JOIN BUDGET_PRODUCT bp ON bp.AREA_ID = ba.ID
JOIN BUDGET_ACTIVITY bac ON bac.PRODUCT_ID = bp.ID
LEFT OUTER JOIN ME_ADJUSTMENT_LINE al ON al.DEPARTMENT_CODE =
bt.DEPARTMENT AND al.TEAM_CODE = bt.TEAM AND al.AREA_CODE = ba.AREA
AND al.PRODUCT_CODE = bp.PRODUCT AND al.ACTIVITY_TYPE_CODE =
bac.ACTIVITY_TYPE_CODE
LEFT OUTER JOIN ME_ADJUSTMENT aj ON aj.ADJUSTMENT_ID = al.ADJUSTMENT_ID
LEFT OUTER JOIN MARKETING_EXPENSE me ON me.MARKETING_EXPENSE_ID =
aj.MARKETING_EXPENSE_ID
LEFT OUTER JOIN ACTIVITY_TYPE_MASTER aty ON aty.ACTIVITY_TYPE_CODE =
bac.ACTIVITY_TYPE_CODE
LEFT OUTER JOIN ORG_STAFF s ON s.STAFF_CODE = me.STAFF_CODE
WHERE aj.STATUS = 'U'
AND bt.BUDGET_YEAR = 2004;


But if I union them as

<sql1> UNION ALL <sql2> UNION ALL <sql3>

It failed with

Statement failed, SQLCODE = -104

Dynamic SQL Error
-SQL error code = -104
-Invalid command
-Data type unknown


If I try <sql1> UNION ALL <sql2>, also failed
If I try <sql2> UNION ALL <sql1>, also failed
If I try <sql1> UNION ALL <sql3>, also failed
If I try <sql3> UNION ALL <sql1>, also failed
If I try <sql2> UNION ALL <sql3>, it is OK.

What does the data type unknown mean?
I am using FirebirdSS 1.5.2.4731 running on RedHat 9.
Please help.

Thanks
C. N.