Subject Re: Problem with ordering links
Author radevojvodic <rvojvodic@rsdef.org>
You can't create ordering item on field calculated in TIB_Query.
Tipical solution woud be to make that view in the database along with
the calculated fields and then make TIB_query for that view. Then you
will be able to create ordering item on field marza.

--- In IBObjects@yahoogroups.com, "Zile" <zilez@p...> wrote:
> I have folowing table:
>
> CREATE TABLE DOBAVLJACI (
> VRSTA VARCHAR(1) CHARACTER SET NONE,
> NAZIV VARCHAR(20) CHARACTER SET NONE,
> FAKTOR DOUBLE PRECISION,
> OPIS VARCHAR(15) CHARACTER SET NONE,
> REDOSLED SMALLINT);
>
> and view:
>
> CREATE VIEW PRODAJASVIVIEWNAS(
> DATUM,
> KURS,
> STATUS,
> SIFRA,
> POREZ,
> NACIN,
> SPEC_RABAT,
> POPUST,
> STAVKE,
> KOMADA,
> CENA,
> SP,
> RABAT,
> VRSTA,
> LC,
> STANJE,
> BROJ,
> REGION,
> FUNKCIJA,
> MESECI,
> DAN,
> PRODAVAC)
> AS
> SELECT ANALIZAMALO.DATUM, ANALIZAMALO.KURS, ANALIZAMALO.STATUS,
> ANALIZAMALO.SIFRA,
> ANALIZAMALO.POREZ, ANALIZAMALO.NACIN, ANALIZAMALO.SPEC_RABAT,
> ANALIZAMALO.POPUST,
> ANALIZAMALO_ST.STAVKE, ANALIZAMALO_ST.KOMADA, ANALIZAMALO_ST.CENA,
> ANALIZAMALO_ST.SP,
> ANALIZAMALO_ST.RABAT, ANALIZAMALO_ST.VRSTA, ANALIZAMALO_ST.LC,
> ARTIKLI.STANJE, ANALIZAMALO.BROJ,
> KUPCI.REGION, KUPCI.FUNKCIJA, extract ( month from DATUM),
extract ( day
> from DATUM),analizamalo.prodavac
> FROM ARTIKLI
> INNER JOIN ANALIZAMALO_ST ON (ARTIKLI.SIFRA_PROIZ =
> ANALIZAMALO_ST.STAVKE)
> INNER JOIN ANALIZAMALO ON (ANALIZAMALO_ST.FAKTURA =
ANALIZAMALO.BROJ)
> LEFT OUTER JOIN KUPCI ON (ANALIZAMALO.SIFRA = KUPCI.SIFRA_KUPCA)
> union all
> SELECT vele.DATUM_PROF, vele.KURS, vele.STATUS, vele.SIFRA,
> vele.POREZ, vele.NACIN, vele.SPEC_RABAT, vele.POPUST,
> vele_ST.STAVKE, vele_ST.KOMADA, vele_ST.CENA, vele_ST.SP,
> vele_ST.RABAT, vele_ST.VRSTA, vele_ST.LC, ARTIKLI.STANJE,
vele.BROJ,
> KUPCI.REGION, KUPCI.FUNKCIJA, extract ( month from datum_prof),
extract
> ( day from datum_prof),vele.prodavac
> FROM ARTIKLI
> INNER JOIN vele_ST ON (ARTIKLI.SIFRA_PROIZ = vele_ST.STAVKE)
> INNER JOIN vele ON (vele_ST.FAKTURA = vele.BROJ)
> LEFT OUTER JOIN KUPCI ON (vele.SIFRA = KUPCI.SIFRA_KUPCA)
> where vele.broj>1
> union all
> SELECT tamni.DATUM, tamni.KURS, tamni.STATUS, tamni.SIFRA,
> tamni.POREZ, tamni.NACIN, tamni.SPEC_RABAT, tamni.POPUST,
> tamnist.STAVKE, tamnist.KOMADA, tamnist.CENA*(1+(tamni.porez/100)),
> tamnist.SP,
> tamnist.RABAT, tamnist.VRSTA, tamnist.LC, ARTIKLI.STANJE,
tamni.BROJ,
> KUPCI.REGION, KUPCI.FUNKCIJA, extract ( month from datum),
extract ( day
> from datum),tamni.prodavac
> FROM ARTIKLI
> INNER JOIN tamnist ON (ARTIKLI.SIFRA_PROIZ = tamnist.STAVKE)
> INNER JOIN tamni ON (tamnist.FAKTURA = tamni.BROJ)
> LEFT OUTER JOIN KUPCI ON (tamni.SIFRA = KUPCI.SIFRA_KUPCA);
>
> When I prepare folowing query:
>
> select
> dobavljaci.naziv,
> (1-((Sum
> (v1.komada*(v1.lc)))/(Sum(v1.Komada*v1.cena*(1-(v1.Spec_rabat/100))*
((100-po
> pust)/100)))))*100 as marza,
> count(distinct stavke) as Brojeva,
> Sum(v1.komada) as Komada,
>
> ((Sum(v1.Komada*v1.cena*(1-(v1.Spec_rabat/100))*((100-
popust)/100)))-(Sum
> (v1.komada*(v1.lc)))) as brcrazlika,
> sum(v1.lc*v1.komada) as nabavna_vrednost,
> Sum(v1.Komada*v1.cena*(1-(v1.Spec_rabat/100))*((100-
popust)/100)) as
> Ukupan_promet,
> v1.vrsta
> from dobavljaci
> inner join prodajasviviewNAS v1 on (dobavljaci.vrsta=v1.vrsta)
> where (datum>='01.01.2003' and datum<='11.01.2003') and
(v1.status='R' or
> v1.status='F' or v1.status='O')
> Group by dobavljaci.naziv, v1.vrsta
> Order by 7 descending
>
> and define ordering items and ordering links with button "Create"
in tab
> sheet ordering, and go to tab sheet data, I can prepare query, also
activate
> query, but when I click on column header "Marza" because I want to
reorder
> grid by values in this column, I get error:
>
> Dynamic SQL Error
> SQL Error code = -206
> Column unkonwn
> MARZA
>
> I use Delphi 6, IBO 4.2 Ib, Interbase 6.0.1.6, SQL Dialect 1.
>
> I would very appriciate if someone can help.
>
> Best Regards
>
> Zile