I am presumably not alone when using view definitions to abbreviate
complex SQL queries.
When looking at the MaxDB manual, the page on Restrictions for SQL
Statements
http://dev.mysql.com/doc/maxdb/en/f6/069940ccd42a54e10000000a1550b0/content.htm
I could not find any explicite restriction on the size of a View up to
now. But there are some very annoying limits and difficulties, as it seems.
Anyway, at first I assumed that -as long as I am not violating the other
general restrictions- the Maximum length of an SQL statement (64kB)
could become the main restriction of a view,
especially so if the view is defined to replace a UNION SELECT with
quite some select clauses. But I stayed below that with the longest of
my view definitions (~35KB text).
SQL Studio is only of poor help here:
Unfortunately ...
(1) compared to the almost nice presentation of Stored Procedures in the
Description field of their definition window,
the presentation of views suffers a lot from missing structure in the
shown code. For example:
for an already defined view, click on the corresponding tree entry in
the Catalog Manager of SQL Studio, and when the view definition opens,
the menue bar changes, now showing a 'View Definition' menu entry, in
that menue click on 'Export to SQL Dialog'. In the coming up SQL dialog
Its hard to recognize some of my view definitions: any line breaks
before SELECT, FROM and WHERE are missing.
(2) SQL Studio can only show ~8kB of any view definition,
the rest of possibly longer definitions is just cut off ;-/
(3) Sums within views:
[This problem does not occur in a simple example of sums within views,
that's why I show as amyn details of the example as I think are
necessary to rebuild the problem:]
In one case of a mol complex view (b),
I am calling counting results of another
view (a) in order to create a pivot table:
(a)
CREATE OR REPLACE VIEW userview_One AS
SELECT i.productID,
count(i.tableID) as numOfEntries,
i.color
FROM aTable i
/* followed by here less important other joins....*/
INNER JOIN aTypeTable it ON i.PrimaryKeyID=it.PrimaryKeyID
AND <some other conditions>
(b)
CREATE OR REPLACE VIEW userview_Two AS
SELECT
p.name
VALUE(uvOneGreen.numOfEntries,0) +
VALUE(uvOneBlue.numOfEntries,0) +
VALUE(uvOneYellow.numOfEntries,0) +
VALUE(uvOneRose.numOfEntries,0) +
VALUE(uvOneRed.numOfEntries,0) as #all_colors,
VALUE(uvOneRose.numOfEntries,0) +
VALUE(uvOneRed.numOfEntries,0) as #reddish_colors,
....
FROM Product p
LEFT OUTER JOIN viewOne uvOne_Green
ON p.productID=uvOne_Green.productID
AND uvOne_Green.color ='GREEN'
LEFT OUTER JOIN viewOne uvOne_Blue
ON p.productID=uvOne_Blue.productID
AND uvOne_Blue.color ='BLUE'
LEFT OUTER JOIN viewOne uvOne_Yellow
ON p.productID=uvOne_Yellow.productID
AND uvOne_Yellow.color ='YELLOW'
LEFT OUTER JOIN viewOne uvOne_Rose
ON p.productID=uvOne_Rose.productID
AND uvOne_Rose.color ='ROSE'
LEFT OUTER JOIN viewOne uvOne_Red
ON p.productID=uvOne_Red.productID
AND uvOne_Red.color ='RED'
....
However, looking at the 'Export to SQL Dialog', the Summed up fields
#all_colors and #reddish_colors of (b) are not displayed as such, but
for each sum element, the given alias name for the whole sum is repeated!
Hence, I see sth like
create view "dba"."userview_Two"
(
"#all_colors",
"#all_colors",
"#all_colors",
"#all_colors",
"#all_colors",
"#reddish_colors",
"#reddish_colors",
...
) AS SELECT .... <very long unbroken text>
Even if I accept that I have to read the details of how the result is
built from the unstructurized SELECT clause, the result pattern of this
representation is wrong, since (b) returs 2 results (#all_colors and
#reddish_colors) and not 7, as suggested by the 'Export to SQL Dialog'.
I wonder for what the 'Export to SQL Dialog' might be useful then.
(3) UNION SELECT definitions are very poorly displayed in the 'Export to
SQL Dialog' of SQL Studio.
(Again, first define a view on a UNION SELECT, then provide the 'Export
to SQL Dialog' as described in (1))
In a first section, all fields defined in all parts of the UNION SELECT
are listed, and then the whole text of the FROM and WHERE clauses of the
UNION SELECT are crunched together in one unstructured big text, and
to top it all, the text is cut somewhere at an 8kB limit.
I avoid to display an example here, it looks awful.
It is strange though, that the View on this UNION select seems to work
pretty well.
But the impression remains:
SQL Studio 7.5.00.18 is everything else but well suited to handle
complex view statements.
I hope that thius changes mol soon.
Any comments appreciated.
Michael Neuber
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
