Subject | Re: Problem with ordering links |
---|---|
Author | radevojvodic <rvojvodic@rsdef.org> |
Post date | 2003-01-20T08:07:50Z |
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.
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