Oracle: Flat subquery.
Ulrik Lunddahl
Jeg har to tabeller:
Varer
varenummer
varenavn
o.s.v
Priser
Pristype (Kan enten være køb, kost eller salg)
Pris
Jeg ønsker følgende:
Varenummer, Varenavn, købspris, kostpris, salgspris.
Altså 1 ROW for hver vare, men med alle 3 priser i nye 3 kolonner.
Hvordan løses dette...
--
Med venlig hilsen / kind regards
Ulrik Lunddahl
PROconsult Data A/S
e-mail: u...@proconsult.dk
Bo Larsson
wrote:
>Varer
> varenummer
> varenavn
> o.s.v
>
>Priser
> Pristype (Kan enten være køb, kost eller salg)
> Pris
>
>Jeg ønsker følgende:
>
>Varenummer, Varenavn, købspris, kostpris, salgspris.
>
>Altså 1 ROW for hver vare, men med alle 3 priser i nye 3 kolonner.
>
Jeg går ud fra, at tabellen Priser også indeholder en varenummer
kolonne.
Følgende query fungerer på SQL Server (den skulle meget gerne fungere
på Oracle, da den (såvidt jeg ved) er ANSI compliant):
SELECT v.varenummer, v.varetekst, p1.pris as 'Køb', p2.pris as 'Kost',
p3.pris as 'Salg'
FROM varer v
LEFT JOIN priser p1
ON v.varenummer = p1.varenummer
AND p1.pristype = 1
LEFT JOIN priser p2
ON v.varenummer = p2.varenummer
AND p2.pristype = 2
LEFT JOIN priser p3
ON v.varenummer = p3.varenummer
AND p3.pristype = 3
--
Med venlig hilsen
Bo Larsson
http://isa.dknet.dk/~booglea/
Ulrik Lunddahl
> Jeg går ud fra, at tabellen Priser også indeholder en varenummer
> kolonne.
Ja, det gør den.
> Følgende query fungerer på SQL Server (den skulle meget gerne fungere
> på Oracle, da den (såvidt jeg ved) er ANSI compliant):
>
> SELECT v.varenummer, v.varetekst, p1.pris as 'Køb', p2.pris as 'Kost',
> p3.pris as 'Salg'
> FROM varer v
> LEFT JOIN priser p1
> ON v.varenummer = p1.varenummer
> AND p1.pristype = 1
> LEFT JOIN priser p2
> ON v.varenummer = p2.varenummer
> AND p2.pristype = 2
> LEFT JOIN priser p3
> ON v.varenummer = p3.varenummer
> AND p3.pristype = 3
Det gør den ikke, jeg kan slet ikke få den til at virke overhovedet, hvis
jeg slår op i 'Oracle8 SQL Reference Release 8.0' kan jeg ikke finde noget
som helst om LEFT JOIN.
I øjeblikket udfører jeg bare 3 nye forespørgsler for hver post i tabellen
med varer, men det virker ret dumt og er sikkert heller ikke særligt
hurtigt.
Er der ikke nogen oracle brugere som kan hjælpe mig, eller henvise mig til
nogen steder hvor jeg kan finde mere information, det behøver ikke
nødvendigvis at være gratis.
Bo Larsson
wrote:
>Bo Larsson <boo...@isa.dknet.dk> wrote:
>
>
>> SELECT v.varenummer, v.varetekst, p1.pris as 'Køb', p2.pris as 'Kost',
>> p3.pris as 'Salg'
>> FROM varer v
>> LEFT JOIN priser p1
>> ON v.varenummer = p1.varenummer
>> AND p1.pristype = 1
>> LEFT JOIN priser p2
>> ON v.varenummer = p2.varenummer
>> AND p2.pristype = 2
>> LEFT JOIN priser p3
>> ON v.varenummer = p3.varenummer
>> AND p3.pristype = 3
>
>Det gør den ikke, jeg kan slet ikke få den til at virke overhovedet, hvis
>jeg slår op i 'Oracle8 SQL Reference Release 8.0' kan jeg ikke finde noget
>som helst om LEFT JOIN.
>
Jamen, så må vi jo prøve med Oracles syntaks:
SELECT v.varenummer, v.varetekst, p1.pris as 'Køb', p2.pris as 'Kost',
p3.pris as 'Salg'
FROM varer v, priser p1, priser p2, priser p3
WHERE v.varenummer = p1.varenummer
AND p1.pristype = 1
AND v.varenummer = p2.varenummer
AND p2.pristype = 2
AND v.varenummer = p3.varenummer
AND p3.pristype = 3
Ovenstående sætning tager ikke højde for, at et af prisfelterne kan
være NULL (hvis et af felterne er det, kommer rækken slet ikke med).
Og sætningen er i øvrigt ikke testet af.
Ulrik Lunddahl
> Jamen, så må vi jo prøve med Oracles syntaks:
>
> SELECT v.varenummer, v.varetekst, p1.pris as 'Køb', p2.pris as 'Kost',
> p3.pris as 'Salg'
> FROM varer v, priser p1, priser p2, priser p3
> WHERE v.varenummer = p1.varenummer
> AND p1.pristype = 1
> AND v.varenummer = p2.varenummer
> AND p2.pristype = 2
> AND v.varenummer = p3.varenummer
> AND p3.pristype = 3
>
> Ovenstående sætning tager ikke højde for, at et af prisfelterne kan
> være NULL (hvis et af felterne er det, kommer rækken slet ikke med).
> Og sætningen er i øvrigt ikke testet af.
Det ser meget mere oracle ud, jeg prøver det i morgen.
Jeg syntes dog at den første version var væsentligt mere læsbar.
Ovenstående er vist også en =join og ikke en left join hvis jeg har forstået
det rigtigt, kan man ved at benytte v.varenummer(+) i stedet gøre det til en
left join.
Jeg skal også lige have flettet nogen andre kritierier ind i forespørgslen,
da databasen kan indeholde data for op til 15000 virksomheder.
Tusinde tak for hjælpen.
Bo Larsson
<lund...@post1.tele.dk> wrote:
>
>Det ser meget mere oracle ud, jeg prøver det i morgen.
>Jeg syntes dog at den første version var væsentligt mere læsbar.
>
Jeg er lige begyndt at kigge nærmere på SQL Server efter at have
arbejdet med Oracle i nogle år, og i begyndelsen havde jeg svare
problemer med JOIN syntaksen, men nu synes jeg egentlig at du har ret:
den er på sin vis mere logisk.
Den Oracle version, som jeg arbejder med til daglig er 7.2 (ja, ja,
grin bare - vi håber snart at kunne opgradere til 8i), så jeg var ikke
klar over, om Oracle havde fået indført JOIN syntaksen - det har de
tilsyneladende ikke.
>Ovenstående er vist også en =join og ikke en left join hvis jeg har forstået
>det rigtigt, kan man ved at benytte v.varenummer(+) i stedet gøre det til en
>left join.
>
Det er korrekt forstået. Jeg overvejede også, at skrive '(+)' i
sætningen, men jeg kunne ikke lige overskue, hvordan 'AND p1.pristype
= 1' ville reagere, så jeg lod det være, men fortæl os endelig
resultatet, når du får det prøvet.
Ulrik Lunddahl
> Den Oracle version, som jeg arbejder med til daglig er 7.2 (ja, ja,
> grin bare - vi håber snart at kunne opgradere til 8i), så jeg var ikke
> klar over, om Oracle havde fået indført JOIN syntaksen - det har de
> tilsyneladende ikke.
Hvorfor 8i og ikke 8.05, og hvad er forskellen egentlig på 8 og 8i.
> Det er korrekt forstået. Jeg overvejede også, at skrive '(+)' i
> sætningen, men jeg kunne ikke lige overskue, hvordan 'AND p1.pristype
> = 1' ville reagere, så jeg lod det være, men fortæl os endelig
> resultatet, når du får det prøvet.
Det kan jeg heller ikke lige overskue, men hvis jeg laver en left join i
stedet for en equijoin får jeg samme resultat, så jeg holder mig til
equijoin, da det jo også er det jeg har brug for.
Hele forespørgsen kommer her:
SELECT
IT.ITEMID,
IT.ITEMNAME,
ITM0.PRICE AS "KOST",
TRUNC(SYSDATE) - TRUNC(ITM0.PRICEDATE) AS "KOSTAGE",
ITM1.PRICE AS "INDKOEB",
ITM2.PRICE AS "SALG",
ITM2.PRICE - ITM0.PRICE AS "DB",
(ITM2.PRICE - ITM0.PRICE) / (ITM2.PRICE + 0.000001) * 100 AS "DG"
FROM
BMSSA.INVENTTABLE IT,
BMSSA.INVENTTABLEMODULE ITM0,
BMSSA.INVENTTABLEMODULE ITM1,
BMSSA.INVENTTABLEMODULE ITM2
WHERE
(IT.ITEMID = ITM0.ITEMID AND ITM0.MODULETYPE = 0 AND ITM0.DATAAREAID =
'dat') AND
(IT.ITEMID = ITM1.ITEMID AND ITM1.MODULETYPE = 1 AND ITM1.DATAAREAID =
'dat') AND
(IT.ITEMID = ITM2.ITEMID AND ITM2.MODULETYPE = 2 AND ITM2.DATAAREAID =
'dat') AND
LOWER(IT.ITEMID) Like 'netdl%' AND
IT.ITEMGROUPID <> 'udgået' AND
IT.DATAAREAID = 'dat'
ORDER BY IT.ITEMID
Hvorfor kan jeg i denne forespørgsel godt lave en ORDER BY DG men ikke
f.eks. en WHERE DG > 1000, jeg troede da godt at man kunne filtrere på
felter man selv har lavet.
Jeg har faktisk brug for at afgrænse på DB og DG og dette kan jeg godt klare
med:
WHERE ((ITM2.PRICE - ITM0.PRICE) / (ITM2.PRICE + 0.000001) * 100) > X
Er det den eneste løsning.
Til dem der er intereserede er den en Concorde Axapta DB jeg kikker ned i.