[Oracle] Union van 3 views

[robbertb]

Ik wil 3 views samenstellen tot een 4e view. Deze 3 views bevatten alledrie 3 kolommen. De eerste 2 zijn gelijk (crewId en Naam), de derde kolommen zijn verschillend (Pilot, copilot en purser). Nu is het de bedoeling dat in de 4e view met behulp van UNION er een tabel uitkomt van 5 kolommen. maar met onderstaande code krijgen we een tabel met 3 kolommen.

code:

drop view copilot_view;
drop view pilot_view;
drop view purser_view;
drop view bemanning;

create view pilot_view as 
SELECT fc.crewid CREW, e.name NAAM, fc.pilot PILOT 
FROM flight_crew fc, employee e 
WHERE e.ssnum = fc.pilot;

create view copilot_view as 
SELECT fc.crewid CREW, e.name NAAM, fc.copilot COPILOT 
FROM flight_crew fc, employee e 
WHERE e.ssnum = fc.copilot;

create view purser_view as 
SELECT fc.crewid CREW, e.name NAAM, fc.purser PURSER 
FROM flight_crew fc, employee e 
WHERE e.ssnum = fc.purser;

create view bemanning as
SELECT CREW, NAAM, PILOT
FROM pilot_view
UNION
SELECT CREW, NAAM, COPILOT
FROM copilot_view
UNION
SELECT CREW, NAAM, PURSER
FROM purser_view;

select * from bemanning;

De uitkomst ziet er ongeveer zo uit:

code:

      CREW NAAM                                          PILOT
---------- ---------------------------------------- ----------
      8001 Jack Unger                                983433342
      8001 Kim Barnes                                893923334
      8001 Steve Smith                               234223432
      8002 Albert Lulushi                            123456789

Maar de uitkomst zou er zo uit moeten zien:

code:

      CREW NAAM                                        PILOT                    COPILOT           PURSER
---------- ---------------------------------------- ---------- ---------------------------   -------------
      8001 Jack Unger                                983433342
      8001 Kim Barnes                                                          893923334
      8001 Steve Smith                                                                               234223432
      8002 Albert Lulushi                            123456789

[watzie]

Los van het feit dat ik niet snap waarom je dit zou willen (via die views) omdat het enorm duur (rekenintensief/traag) is, en je beter direct via je hoofdtabel deze view kan bouwen, kan het geen kwaad om het volgende voorbeeldje te geven volgens wat jij denk ik wil:

code:

create view bemanning as
SELECT CREW, NAAM, PILOT, '' as COPILOT, '' AS PURSER
FROM pilot_view
UNION
SELECT CREW, NAAM, '' as PILOT, COPILOT, '' as purser
FROM copilot_view
UNION
SELECT CREW, NAAM, '' as pilot, '' as copilot, PURSER
FROM purser_view
ORDER BY CREW;

helpt dat?

[Verwijderd]

robbertb schreef op 06 mei 2004 @ 22:16:

code:

create view bemanning as
SELECT CREW, NAAM, PILOT
FROM pilot_view
UNION
SELECT CREW, NAAM, COPILOT
FROM copilot_view
UNION
SELECT CREW, NAAM, PURSER
FROM purser_view;

Maar de uitkomst zou er zo uit moeten zien:

code:

      CREW NAAM                                        PILOT                    COPILOT           PURSER
---------- ---------------------------------------- ---------- ---------------------------   -------------
      8001 Jack Unger                                983433342
      8001 Kim Barnes                                                          893923334
      8001 Steve Smith                                                                               234223432
      8002 Albert Lulushi                            123456789

Dat je 3 kolommen als resultaat krijgt komt doordat je in de view bemanning slechts 3 velden declareert na de SELECT.
Om 5 kolommen te krijgen zou je in elke SELECT dummy-velden kunnen toevoegen.
De view ziet er dan als volgt uit:

code:

create view bemanning as
SELECT CREW, NAAM, PILOT, NULL, NULL
FROM pilot_view
UNION
SELECT CREW, NAAM, NULL, COPILOT, NULL
FROM copilot_view
UNION
SELECT CREW, NAAM, NULL, NULL, PURSER
FROM purser_view;

edit:
watzie heeft natuurlijk gelijk dat het in jouw voorbeeld onzinnig is een view op een view te baseren.

[ Voor 4% gewijzigd door Verwijderd op 06-05-2004 22:40 ]

[robbertb]

Verwijderd schreef op 06 mei 2004 @ 22:34:
[...]


Dat je 3 kolommen als resultaat krijgt komt doordat je in de view bemanning slechts 3 velden declareert na de SELECT.
Om 5 kolommen te krijgen zou je in elke SELECT dummy-velden kunnen toevoegen.
De view ziet er dan als volgt uit:

code:

create view bemanning as
SELECT CREW, NAAM, PILOT, NULL, NULL
FROM pilot_view
UNION
SELECT CREW, NAAM, NULL, COPILOT, NULL
FROM copilot_view
UNION
SELECT CREW, NAAM, NULL, NULL, PURSER
FROM purser_view;

edit:
watzie heeft natuurlijk gelijk dat het in jouw voorbeeld onzinnig is een view op een view te baseren.

Dit lijkt inderdaad te zijn wat ik zoek! Klinkt logisch.. maar ik krijg alsnog een fout:
FOUT in regel 1:
.ORA-00998: must name this expression with a column alias

[justmental]

In je eerste select moet je aliassen gebruiken voor de null kolommen.
Waarschijnlijk moet je ook het datatype aangeven, bijv: to_number(null) as copilot

[robbertb]

justmental schreef op 06 mei 2004 @ 22:44:
In je eerste select moet je aliassen gebruiken voor de null kolommen.
Waarschijnlijk moet je ook het datatype aangeven, bijv: to_number(null) as copilot

Thnx allemaal! het is gelukt!

Dit moest ik nog toevoegen

code:

create view bemanning as
SELECT CREW, NAAM, PILOT, NULL as COPILOT, NULL as PURSER

[Verwijderd]

justmental schreef op 06 mei 2004 @ 22:44:
In je eerste select moet je aliassen gebruiken voor de null kolommen.
Waarschijnlijk moet je ook het datatype aangeven, bijv: to_number(null) as copilot

Alleen NULL declareren is voldoende om een kolom te forceren.
Ik zou ook niet weten waarom je NULL zou willen omzetten naar een number. Wat geeft de functie to_number(NULL) terug volgens jou ?

[justmental]

Verwijderd schreef op 06 mei 2004 @ 23:58:
Alleen NULL declareren is voldoende om een kolom te forceren.
Ik zou ook niet weten waarom je NULL zou willen omzetten naar een number. Wat geeft de functie to_number(NULL) terug volgens jou ?

Voor de definitie van de view kijkt Oracle naar de eerste select.
Bij "null" weet ie niet wat voor datatype de kolom is.
Die to_number is dan ter info.

[Verwijderd]

justmental schreef op 07 mei 2004 @ 07:17:
[...]

Voor de definitie van de view kijkt Oracle naar de eerste select.
Bij "null" weet ie niet wat voor datatype de kolom is.
Die to_number is dan ter info.

Ik ben nog niet overtuigd.
Bij het opbouwen van de view kijkt Oracle naar de volledige query. De datatypen van de geselecteerde velden worden uit de brontabellen gehaald.
Het NULL statement wordt in dit geval misbruikt om een kolom te forceren maar doet in feite helemaal niets. Dus waarom zou je niets converteren naar een ander datatype, al dan niet ter info ?

[justmental]

code:

08:48:13 SQL> create table temp (temp1 number, temp2 number);

Table created.

Elapsed: 00:00:00.11
08:48:31 SQL> create table temp2 (temp1 number, temp3 number);

Table created.

Elapsed: 00:00:00.01
08:48:45 SQL> create view tempx as select temp1, null temp3 from temp union all select temp1, temp3 from temp2;
create view temp3 as select temp1, null from temp union all select temp1, temp3 from temp2
                                   *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

voor de volledigheid:

code:

08:52:02 SQL> create view tempx as select temp1, to_number(null) temp3 from temp union all select temp1, temp3 from temp
2;

View created.

[ Voor 19% gewijzigd door justmental op 07-05-2004 08:53 ]

[Verwijderd]

als je geen explicieite datatypering doet, gaat Oracle impliciete dataconversie doen. Dat gaat vaak goed en soms fout.
Toevallig hadden we laatst hier een query uit een VARCHAR2() veld, waar veel getallen in stonden.
Iemand deed een select 'x' from tabel where tekstveld =4848383, en kreeg een

code:

ORA-01722: invalid number

Probeer maar eens

[Verwijderd]

Verwijderd schreef op 07 mei 2004 @ 08:58:
als je geen explicieite datatypering doet, gaat Oracle impliciete dataconversie doen. Dat gaat vaak goed en soms fout.
Toevallig hadden we laatst hier een query uit een VARCHAR2() veld, waar veel getallen in stonden.
Iemand deed een select 'x' from tabel where tekstveld =4848383, en kreeg een

code:

ORA-01722: invalid number

Probeer maar eens

Ok, fair enough. Kan me niet herinneren dat ik een ooit expliciete conversie moest doen mbt NULL in een view.
BTW moet het dan niet zijn:

code:

select 'x' from tabel where tekstveld ='4848383'

want in jouw voorbeeld vergelijk je in de WHERE clause een NUMBER met een VARCHAR, dat is een ander probleem dan van de TS.

[Verwijderd]

Verwijderd schreef op 07 mei 2004 @ 09:27:

Ok, fair enough. Kan me niet herinneren dat ik een ooit expliciete conversie moest doen mbt NULL in een view.
BTW moet het dan niet zijn:

code:

select 'x' from tabel where tekstveld ='4848383'

want in jouw voorbeeld vergelijk je in de WHERE clause een NUMBER met een VARCHAR, dat is een ander probleem dan van de TS.

Ik heb dat probleem al wel een paar keer gehad met null values, vandaar die expliciete conversie. Met mijn voorbeeld wilde ik het wat zichtbaarder maken.
Het moet inderdaad zo zijn zoals jij aangeeft, met quotes, maar mijn voorbeeld werd in de code gegenereerd en was verkeerd geparameteriseerd (lang verhaal ).
Maarre.. volgens mij was de aanleiding van mijn reactie waarom je 'niets' zou converteren naar een ander datatype. Expliciete conversie dus
Ik ben daar altijd nogal scherp op omdat veel mensen het vaak niet zo nauw nemen, zo van 'och dat doet de parser wel voor me'. En ik durf te beweren dat 30% van de syntaxfouten 'Numeric or value errors' zijn.

[Verwijderd]

Verwijderd schreef op 07 mei 2004 @ 09:39:
[...]

Ik heb dat probleem al wel een paar keer gehad met null values, vandaar die expliciete conversie. Met mijn voorbeeld wilde ik het wat zichtbaarder maken.
Het moet inderdaad zo zijn zoals jij aangeeft, met quotes, maar mijn voorbeeld werd in de code gegenereerd en was verkeerd geparameteriseerd (lang verhaal ).
Maarre.. volgens mij was de aanleiding van mijn reactie waarom je 'niets' zou converteren naar een ander datatype. Expliciete conversie dus

Het is dus eerder een workaround dan een syntactische vereiste.
Mijn punt is dat een conversie niets inhoudelijks toevoegt aan de view want die expliciete conversie legt verder geen restrictie op aan de kolom van de view voor wat betreft het datatype. Je kan immers bij een UNION verschillende datatypen door elkaar als resultaat krijgen in dezelfde kolom van een view, dat is geheel afhankelijk van de geselecteerde velden uit de brontabellen.

Ik ben daar altijd nogal scherp op omdat veel mensen het vaak niet zo nauw nemen, zo van 'och dat doet de parser wel voor me'. En ik durf te beweren dat 30% van de syntaxfouten 'Numeric or value errors' zijn.

Dat zou heel goed kunnen maar 'Numeric or value errors' traden in dit geval niet op. Bovendien zijn dit voor de hand liggende fouten. Deze duiden op een mismatch van datatypen bij een vergelijking, insert, toewijzing enz. Ben wel met je eens dat een conversie dan is gewenst.