Thread: Re: Query, view join question.

Hi Tom,

I could give you access to the database itself if needed. But these are the actual tables and view. I hope I will never
makeany tpo's again to upset you this way. 

CREATE TABLE abo_his
(
  klantnummer int4,
  abonnement int2,
  artikelnummer int4,
  omschrijving char(40),
  nummer_vd_levering int2,
  artikelnummer_gratis int4,
  artikelnummer_gratis_2 int4,
  artikelnummer_gratis_3 int4,
  omschrijving_gratis_artikel char(40),
  omschrijving_gratis_artikel_2 char(40),
  omschrijving_gratis_artikel_3 char(40),
  datum_selectie date,
  ordernummer int4,
  code_retour int2,
  briefnummer int2,
  orderbedrag_guldens numeric(8,2),
  orderbedrag_valuta numeric(8,2),
  aantal_besteld int4,
  verzendkosten numeric(8,2),
  handmatige_toevoeging int2
)
WITH OIDS;

CREATE TABLE abo_klt
(
  klantnummer int4 NOT NULL,
  abonnement int2 NOT NULL,
  waardering_klant char(10),
  gem_betaaltermijn int4,
  reden_blokkade_oud char(40),
  aantal_abonnementen int2,
  herkomst int4,
  datum_abonnee date,
  datum_laatste_selectie date,
  reden_blokkade int2,
  datum_blokkade date,
  max_bedrag_lev_jaar numeric(8,2),
  bestelfrequentie_in_dagen int2,
  incasso int2,
  instap_categorie int2,
  afgewerkt int2,
  eenmaligemachtigingeerstekeer int2,
  naar_ander_abo int2
)
WITH OIDS;

CREATE OR REPLACE VIEW even AS
 SELECT DISTINCT abo_his.klantnummer, abo_his.artikelnummer, abo_his.code_retour, abo_klt.aantal_abonnementen,
abo_klt.afgewerkt
   FROM abo_his
   JOIN abo_klt ON abo_his.klantnummer = abo_klt.klantnummer
  WHERE abo_his.abonnement = 238
  ORDER BY abo_his.klantnummer, abo_his.artikelnummer, abo_his.code_retour, abo_klt.aantal_abonnementen,
abo_klt.afgewerkt;


Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl

"Joost Kraaijeveld" <J.Kraaijeveld@Askesis.nl> writes:
> CREATE OR REPLACE VIEW even AS
>  SELECT DISTINCT abo_his.klantnummer, abo_his.artikelnummer, abo_his.code_retour, abo_klt.aantal_abonnementen,
abo_klt.afgewerkt
>    FROM abo_his
>    JOIN abo_klt ON abo_his.klantnummer = abo_klt.klantnummer
>   WHERE abo_his.abonnement = 238
>   ORDER BY abo_his.klantnummer, abo_his.artikelnummer, abo_his.code_retour, abo_klt.aantal_abonnementen,
abo_klt.afgewerkt;

Okay ... but the view is constraining abo_his.abonnement and outputting
abo_klt.aantal_abonnementen.  Why would you assume that joining on
klantnummer would cause these two fields to necessarily be the same?

            regards, tom lane

On Thu, 2005-01-06 at 17:57 +0100, Joost Kraaijeveld wrote:
> Hi Tom,
>
> I could give you access to the database itself if needed. But these are the actual tables and view.
>
> I hope I will never make any tpo's again to upset you this way.

no-one was upset. the point is just that you are more likely to get
useful answers when those who would help you do not first
have to guess what you did.

a simplified case, as you tried to show us, is excellent, but
you should test it first, and post a cut-and-paste copy of your
commands and output to minimize typos.

gnari

unsubscribe