The total width of result returned by SELECT can't exceed 64k.
Can this be a problem ?

Ivan

----- Original Message -----
From: "Robert" <rjm@...>
To: "firebird-support" <firebird-support@yahoogroups.com>
Sent: Friday, March 26, 2004 12:09 PM
Subject: [firebird-support] Did I reached the boundaries of Firebird?


> Hi,
> It seems i reached the boundaries of Firebird (I use WI-V1.5.0.4290 Firebird
> 1.5).
>
> I receive a -901 "cannot sort on a field that does not exist" or a -151
> "attemted update of read-only column" when i execute a SELECT statement that
> uses views with a lot subqueries.
>
> The messages doesnot make sense (for me). For the -901 occurs for SELECT's
> without ORDER clause (so nothing to sort) and the -151 error occurs for just
> a SELECT-satement (so nothing to update).
>
> I searched Google for the error messages but found no threads for my
> situation.
> Can someone explain why i get this error messages?
>
> F.e.:
> I will get a prepare error -901 "cannot sort on a field that does not exist"
> for this statement:
>
> SELECT
> O.OpdrachtId,
> O.OpdrachtGever,
> O.FCodering,
> OVS.Verdeelsleutel,
> OVS.VerdeelSleutel*HPT.PersoneelUren,
> OVS.VerdeelSleutel*HPT.PersoneelGeld,
> OVS.VerdeelSleutel*HPT.NietPersoneelGeld,
> OVS.VerdeelSleutel*HPT.ReleaseGeld,
> OVS.VerdeelSleutel*HPT.TotaalGeld
> FROM VW_Opdracht O
> LEFT JOIN VW_OpdrachtVerdeelSleutel OVS
> ON (OVS.HoofdProjectId = O.HoofdProjectId AND OVS.OpdrachtId =
> O.OpdrachtId)
> LEFT JOIN VW_HoofdProjectTotalen HPT
> ON (HPT.HoofdProjectId = O.HoofdProjectId)
>
> If i reduce the number of fields in the select clause to 6 there is no
> problem. It doesn't mater which fields i delete: 7 or more fileds gives me
> the error, less than 7 fields will run ok.
>
> F.e.: this will run just fine:
>
> SELECT
> O.OpdrachtId,
> O.FCodering,
> OVS.Verdeelsleutel,
> OVS.VerdeelSleutel*HPT.PersoneelUren,
> OVS.VerdeelSleutel*HPT.ReleaseGeld,
> OVS.VerdeelSleutel*HPT.TotaalGeld
> FROM VW_Opdracht O
> LEFT JOIN VW_OpdrachtVerdeelSleutel OVS
> ON (OVS.HoofdProjectId = O.HoofdProjectId AND OVS.OpdrachtId =
> O.OpdrachtId)
> LEFT JOIN VW_HoofdProjectTotalen HPT
> ON (HPT.HoofdProjectId = O.HoofdProjectId)
>
> BTW The two views i join are rather complex, using a lot of subqueries in
> the select clause:
>
> VW_OpdrachtVerdeelSleutel:
> SELECT
> O.HoofdProjectId,
> O.OpdrachtId,
> (SELECT SUM(K.Geld) FROM VW_Kosten K WHERE K.HoofdProjectId =
> O.HoofdProjectId AND K.OpdrachtId = O.OpdrachtId)/NULLIF(HPT.SpecifiekGeld,
> 0)
> FROM VW_Opdracht O
> LEFT JOIN VW_HoofdProjectTotalen HPT ON (HPT.HoofdProjectId =
> O.HoofdProjectId)
>
> SELECT
> HP.HoofdProjectId,
> (SELECT SUM(K1.Uren) FROM VW_Kosten K1 WHERE K1.HoofdProjectId =
> HP.HoofdProjectId AND K1.Categorie = 'P'),
> (SELECT SUM(K2.Geld) FROM VW_Kosten K2 WHERE K2.HoofdProjectId =
> HP.HoofdProjectId AND K2.Categorie = 'P'),
> (SELECT SUM(K3.Geld) FROM VW_Kosten K3 WHERE K3.HoofdProjectId =
> HP.HoofdProjectId AND K3.Categorie = 'N'),
> (SELECT SUM(K4.Geld) FROM VW_Kosten K4 WHERE K4.HoofdProjectId =
> HP.HoofdProjectId AND K4.Categorie = 'R'),
> (SELECT SUM(K5.Uren) FROM VW_Kosten K5 WHERE K5.HoofdProjectId =
> HP.HoofdProjectId),
> (SELECT SUM(K6.Geld) FROM VW_Kosten K6 WHERE K6.HoofdProjectId =
> HP.HoofdProjectId),
> (SELECT SUM(K7.Geld) FROM VW_Kosten K7 WHERE K7.HoofdProjectId =
> HP.HoofdProjectId AND K7.OpdrachtId IS NOT NULL),
> (SELECT SUM(O.AantalFunctiePunten) FROM VW_Opdracht O WHERE
> (O.HoofdProjectId = HP.HoofdProjectId)),
> (SELECT MIN(T1.BeginDatum) FROM VW_Traject T1 WHERE T1.HoofdProjectId =
> HP.HoofdProjectId),
> (SELECT MAX(T2.EindDatum) FROM VW_Traject T2 WHERE T2.HoofdProjectId =
> HP.HoofdProjectId)
> FROM VW_HoofdProject HP