List:Internals« Previous MessageNext Message »
From:Martijn Tonies Date:September 30 2004 10:17am
Subject:Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEW
View as plain text  


> PS: it also doesn't honor "carriage returns" and "line feeds"
> when showing the source of the view.

For a real-life problem example:

I created this view:
CREATE VIEW DEBITEUR_NAW
(
 RELATIEID,
 NAAM,
 ADRES,
 POSTCODE,
 PLAATS,
 LAND,
 DEBITEURNUMMER,
 TELEFOON,
 FAX,
 ACTUEEL,
 EMAIL,
 KVKNUMMER
) AS

select d.relatieID, r.naam, a.adres, a.postcode, a.plaats, l.omschrijving,
d.debiteurnummer,
       (select telefoonnummer from relatie_telefoon
         where relatieid = r.relatieid and telefooncode = 1 and nummer = 1),
       (select telefoonnummer from relatie_telefoon
         where relatieid = r.relatieid and telefooncode = 2 and nummer = 1),
       r.actueel, r.email, r.kvknummer
from debiteur d
join adres a on (d.relatieid = a.relatieid and a.adrescode = 2)
join relatie r on (d.relatieid = r.relatieid)
join land l on (a.landcode = l.landcode)


Take a look at the SELECT statement, this is returned from SHOW CREATE VIEW
as:
select `test`.`d`.`relatieid` AS `RELATIEID`,`test`.`r`.`naam` AS
`NAAM`,`test`.`a`.`adres` AS `ADRES`,`test`.`a`.`postcode` AS
`POSTCODE`,`test`.`a`.`plaats` AS `PLAATS`,`test`.`l`.`omschrijving` AS
`LAND`,`test`.`d`.`debiteurnummer` AS `DEBITEURNUMMER`,(select
`test`.`relatie_telefoon`.`telefoonnummer` AS `telefoonnummer` from
`test`.`relatie_telefoon` where ((`test`.`relatie_telefoon`.`relatieid` =
`test`.`r`.`relatieid`) and (`test`.`relatie_telefoon`.`telefooncode` = 1)
and (`test`.`relatie_telefoon`.`nummer` = 1))) AS `TELEFOON`,(select
`test`.`relatie_telefoon`.`telefoonnummer` AS `telefoonnummer` from
`test`.`relatie_telefoon` where ((`test`.`relatie_telefoon`.`relatieid` =
`test`.`r`.`relatieid`) and (`test`.`relatie_telefoon`.`telefooncode` = 2)
and (`test`.`relatie_telefoon`.`nummer` = 1))) AS `FAX`,`test`.`r`.`actueel`
AS `ACTUEEL`,`test`.`r`.`email` AS `EMAIL`,`test`.`r`.`kvknummer` AS
`KVKNUMMER` from (((`test`.`debiteur` `d` join `test`.`adres` `a`
on(((`test`.`d`.`relatieid` = `test`.`a`.`relatieid`) and
(`test`.`a`.`adrescode` = 2)))) join `test`.`relatie` `r`
on((`test`.`d`.`relatieid` = `test`.`r`.`relatieid`))) join `test`.`land`
`l` on((`test`.`a`.`landcode` = `test`.`l`.`landcode`)));

(but all on 1 line). Now, this doesn't look right to me! Sure, it's valid
syntax, but your users will go ape :-)

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com

Thread
CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWMartijn Tonies29 Sep
  • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWMartijn Tonies29 Sep
    • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWMartijn Tonies7 Oct
      • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWSanja Byelkin7 Oct
  • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWMartijn Tonies30 Sep
    • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWSanja Byelkin5 Oct
      • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWMartijn Tonies7 Oct
  • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWMartijn Tonies5 Oct
    • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWSanja Byelkin5 Oct
  • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWMartijn Tonies5 Oct
    • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWSanja Byelkin5 Oct
  • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWMartijn Tonies5 Oct
    • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWSanja Byelkin5 Oct
  • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWMartijn Tonies5 Oct
    • Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEWSanja Byelkin7 Oct