I'm gettin an invalid Union clause error message. If I delete the order by in the first select then I get another error message stating there must be a order by element.



>Try

-- Select all subaccounts
>Select A.Cuenta, A.Descripcion, A.Descarga,
>		Isnull((Select Balance From CNTBal03
>			Where Cuenta=A.Cuenta And Periodo=12 and Fiscal='2003' ),0)
>			+
>		Isnull((Select Sum(Debito-Credito) From CNTDET02
>			Where Cuenta=A.Cuenta And Periodo=1 And Fiscal='2004'),0)
>		As Balance
>	From cntCuentas A
>	WHERE A.Cuenta <> A.Descarga,
>	Order By rTrim(lTrim(A.Cuenta))
>UNION ALL
>--- Main accounts
>Select A.Cuenta, A.Descripcion, A.Descarga,
>		Isnull((Select SUM(Balance) From CNTBal03 b
>				JOIN cntCuentas c ON c.Cuenta = b.Cuenta
>				Where c.Descarga=A.Descarga
>					And b.Periodo=12 and b.Fiscal='2003' ),0)
>			+
>		Isnull((Select Sum(Debito-Credito) From CNTDET02 d
>				JOIN cntCuentas c ON c.Cuenta = d.Cuenta
>				Where c.Descarga=A.Descarga
>					And d.Periodo=1 And d.Fiscal='2004'),0)
>		As Balance
>	From cntCuentas A
>	WHERE A.Cuenta = A.Descarga,
>	Order By rTrim(lTrim(A.Cuenta))
>

>>That's what I thought but if I group by descarga I still get the same results beside not all the records show.
>>
>>