This SQL works,

select cast(total as money) as total
from table
union all
select sum(if(flag='D',total*-1,total))  as total
from table

this however doesn't

select cast(total as money) as total
from table
union all
select round(sum(if(flag='D',total*-1,total)),2)  as total
from table

the error returned is

DBISAM Engine Error # 11949 SQL parsing error - The number of columns in
queries involved in a UNION, INTERSECT, or EXCEPT operation must match
in SELECT SQL statement at line 0, column 0.

total datatype is Money.

I'm using Delpi 7 with DBISAM 4.22 Build 3

(if the sql makes no sense its because i stripped it of non necessary
clauses)

Any reason why this is happening?

Best regards,

Samuel da Costa

Samuel,

> DBISAM Engine Error # 11949 SQL parsing error - The number of columns in
> queries involved in a UNION, INTERSECT, or EXCEPT operation must match in
> SELECT SQL statement at line 0, column 0.

This looks like it was already corrected in a later build of DBISAM,

http://www.elevatesoft.com/scripts/incident.dll?action=viewaddr&release=4.23&type=f&incident=2174

So you might want to pick up the latest version and see if that corrects the
issue for you.

--

---------------------------------------
 Terry Swiers
 Millennium Software, LLC
 http://www.1000years.com
 http://www.atrex.com

 Atrex Inventory Control/POS -
    Big business features without spending big business bucks!

Atrex Electronic Support Options:
 Atrex Knowledgebase: http://www.atrex.com/atrexkb.asp
 Email: mailto:support@atrex.com
 Newsgroup: news://news.1000years.com/millennium.atrex
 Fax: 1-925-829-1851
 Phone: 1-925-828-5892 (M-F, 9a-5p Pacific)
 ---------------------------------------

Ok, i'll do that.

Thanks,

Sam

Terry Swiers wrote:
> Samuel,
>
>> DBISAM Engine Error # 11949 SQL parsing error - The number of columns in
>> queries involved in a UNION, INTERSECT, or EXCEPT operation must match in
>> SELECT SQL statement at line 0, column 0.
>
> This looks like it was already corrected in a later build of DBISAM,
>
> http://www.elevatesoft.com/scripts/incident.dll?action=viewaddr&release=4.23&type=f&incident=2174
>
> So you might want to pick up the latest version and see if that corrects the
> issue for you.
>