Hello Michael, Please use the newest 7.6 version of SQL Studio. As you can see in the PTS Link http://pts:1080/webpts?wptsdetail=yes&ErrorType=0&ErrorID=1142469 some of the problems you describe are already resolved.
To see the original view definition kept by the kernel you can execute a 'SELECT * FROM "DOMAIN"."VIEWDEFS" where VIEWNAME = '<your view>'. If the line breaks are missing in the definition field, SQL Studio does not have a chance to display them in the view dialog. The comments you make are very interesting and will be considered in the new development of SQL Studio. To repeat the situation you describe could you please send me the complete definitions of your views and tables. Regards Wolfgang -----Ursprüngliche Nachricht----- Von: Michael Neuber [mailto:[EMAIL PROTECTED] Gesendet: Montag, 30. Oktober 2006 18:14 An: maxdb@lists.mysql.com Betreff: SQL Studio and View Definitions 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] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
