im new here in the Forum and have a Problem with Postgresql Linq Provider und a Error:
I have to write this SQL-Query in LINQ.
Code: Select all
select h.hrstnr as Nummer,
h.hrst_kurzname as Bezeichnung,
akt.Anzahl as AktivAnzahl,
pakt.Anzahl as PAktivAnzahl,
pass.Anzahl as PassivAnzahl
from hersteller h
left join (select art_herstellernr, count(art_artikelnr) as Anzahl from artview
where art_belegt_merker=255 group by art_herstellernr) as akt on h.hrstnr =akt.art_herstellernr
left join (select art_herstellernr, count(art_artikelnr) as Anzahl from artview
where art_belegt_merker=250 group by art_herstellernr) as pakt on h.hrstnr =pakt.art_herstellernr
left join (select lieferantennr, count(satznr) as Anzahl from artik group by lieferantennr) as pass
on h.hrstnr =pass.lieferantennr
order by h.hrstnr
Can anyone help me? Each LINQ query works well only the last doesnt.
Code: Select all
Dim aktivartikel = From art In cont.ArtViews _
Where art.art_belegt_merker.Equals(caktiv) _
Select hrstnr = art.Herstellernummer, art.CarlaNummer
Dim aktivpartikel = From art In cont.ArtViews _
Where art.art_belegt_merker.Equals(cpaktiv) _
Select hrstnr = art.Herstellernummer, art.CarlaNummer
Dim passivartikel = From art In cont.artiks _
Select hrstnr = art.lieferantennr, art.lieferantenartikelnr
Dim resakt = From a In aktivartikel _
Group a By a.hrstnr Into Group _
Select hrstnr, count = Group.Count
Dim respakt = From a In aktivpartikel _
Group a By a.hrstnr Into Group _
Order By hrstnr _
Select hrstnr, count = Group.Count
Dim respas = From p In passivartikel _
Group By p.hrstnr Into Group _
Select hrstnr, count = Group.Count
Dim hqu = From hr In cont.herstellers _
Distinct _
Select hr.hrstnr, hr.hrst_kurzname
Dim qu1 = From a In hqu _
Group Join akt In aktivartikel On a.hrstnr Equals akt.hrstnr Into aktgroup1 = Group _
From aktgroup In aktgroup1.DefaultIfEmpty _
Group Join pakt In aktivpartikel On a.hrstnr Equals pakt.hrstnr Into paktgroup1 = Group _
From paktgroup In paktgroup1.DefaultIfEmpty _
Group Join ppas In passivartikel On a.hrstnr Equals ppas.hrstnr Into ppasgroup1 = Group _
From ppasgroup In ppasgroup1.DefaultIfEmpty _
Select Nummer = a.hrstnr, Bezeichnung = a.hrst_kurzname, AnzahlAktiv = aktgroup.count, AnzahlPAktiv = paktgroup.count, AnzahlPassiv = ppasgroup.count
sincerly
ggb[/code]