D7 + DBISAM 4.22 Build 3

I get a complaint when doing the following union, but each SELECT on its
own gives 5 columns.

-----------------------------------------------------------------
SELECT locNum, amoY100M, tarMensuelTTC, Reglements, CAST(0.0 AS MONEY)
Solde
FROM memory\LocEtRgl

UNION

SELECT locNum, MAX(amoY100M) amoY100M, SUM(tarMensuelTTC) tarMensuelTTC,
SUM(Reglements) Reglements,
      CASE WHEN SUM(Reglements) = NULL THEN (SUM(tarMensuelTTC))
      ELSE (SUM(tarMensuelTTC)- SUM(Reglements)) END Solde
FROM memory\LocEtRgl
GROUP BY locNum

ORDER BY locNum, amoY100M
-----------------------------------------------------------------

Can anyone help me out?
--
Chris

Chris Erdal <chris@No-Spam-erdal.net> wrote in
news:Xns977F9F052EB3514torcatis@64.65.248.118:

> D7 + DBISAM 4.22 Build 3
>
> I get a complaint when doing the following union, but each SELECT on its
> own gives 5 columns.

[forgot the error message ;-*]

DbSys complains with :

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

--
Chris

Dear Chris,

Firstly with a UNION the columns in each SELECT segment must match *exactly* ... therefore, columns must be listed in the same order and must
match in terms of the implicit data type.

I can see in the SQL example you have given that column orders don't match in the 2 queries. This alone will make the query fail.

Secondly there is no guarantee that MAX(SomeField) will return the same type as Somefield ... you may have to CAST either or both values in the
SELECT in guarantee success. There is useful stuff in the DBISAM SQL helpfile on using CAST ... it is very, very useful with UNION queries, as it
allows you to "slot together" a lot of different data in ways whic can be very interesting & useful.

--

i.e.

The following SQL will work:

SELECT DateDelivered, CustName as "Cust/Supp", SaleValue as "ItemValue"
FROM Sales
WHERE DateDelivered > Current_Date-10
UNION
SELECT DateDelivered, SupplierName as "Cust/Supp", OrderValue as "ItemValue"
FROM  Orders

... because of the exact match, but even changing the order of the fields would immediately stop it from working.

How this helps.

Adam Brett

Chris,

<< I get a complaint when doing the following union, but each SELECT on its
own gives 5 columns. >>

It's a bug.  The CASE operator uses some hidden fields that are included
when they shouldn't be in the count comparison.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in
news:D0B05460-974B-482C-BDC2-341FECC900EA@news.elevatesoft.com:

> The CASE operator uses some hidden fields that are
> included when they shouldn't be in the count comparison.
>

Thanks for that, Tim. But I get the same error without the CASE:
-----------------------------------------------------
SELECT locClient,  amoY100M,  loyers,  Reglements, 0.0 Solde
FROM memory\LocEtRgl

UNION

SELECT locClient, MAX(amoY100M) amoY100M, SUM(Loyers) Loyers, SUM
(Reglements) Reglements,
SUM(loyers)- SUM(Reglements) Solde
FROM memory\LocEtRgl
GROUP BY locClient

ORDER BY locClient, amoY100M
-----------------------------------------------------

If I remove the subtraction of 2 SUMs thus:

/*SUM(loyers)-*/ SUM(Reglements) Solde

It is accepted.

I can probably work around it with another memory table, so it's not a
big problem in this case.

--
Chris

Thanks for looking at my problem, Adam, but:

adam <adam@nospamfmfoods.co.uk> wrote in
news:A4086635-597F-43AA-9975-6DB7B3F4094E@news.elevatesoft.com:

> I can see in the SQL example you have given that column orders don't
> match in the 2 queries.
>

I can't. Can you be more precise?

--
Chris

Chris,

<< Thanks for that, Tim. But I get the same error without the CASE: >>

My apologies, I should have been more precise in my explanation.  The
problem will occur with *any* aggregate expression, and you still have this
aggregate expression in your second SELECT statement:

SUM(loyers)- SUM(Reglements) Solde

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in
news:85203B28-A2A2-4942-A168-DB222B329AC0@news.elevatesoft.com:

> My apologies, I should have been more precise in my explanation.  The
> problem will occur with *any* aggregate expression, and you still have
> this aggregate expression in your second SELECT statement:
>
>  SUM(loyers)- SUM(Reglements) Solde
>

Your explanations are so spot-on and clear that it makes things easy for
us!

Just in case anyone else is wondering about this kind of thing, I fixed
this thus:
------------------------------------------------
UPDATE memory\LocEtRgl
SET Reglements = 0
WHERE Reglements IS NULL
;

SELECT locClient, MAX(amoY100M) amoY100M, SUM(Loyers) Loyers, SUM
(Reglements) Reglements,
      SUM(Loyers)- SUM(Reglements) Solde
INTO memory\CliSolde
FROM memory\LocEtRgl
GROUP BY locClient
;

UPDATE memory\LocEtRgl
SET Reglements = NULL
WHERE Reglements = 0
;

INSERT INTO memory\CliSolde
SELECT locClient, amoY100M, Loyers, Reglements, SUM(NULL)- SUM(NULL)
FROM memory\LocEtRgl
GROUP BY locClient, amoY100M
;
------------------------------------------------

--
Chris