Good Morning, Wolfgang,
right now available from MySQL.com is SQL Studio Vs 7.6.00.27,
packed in the file maxdb-sqlstudio-setup-7_6_00-130190.exe
(I just checked this again - [2006-10-31 08:20]).
For this Version 7.6.00.27 -which, IMHO can thus be regarded to be the
latest available version -
the problem (1) and (2) I described in my mail yesterday are still
present, problem (3) (multiple occurrence of selected attributes) is
partly solved, however, one needs still some fantasy to recognize the
own (in my case admittedly long) view definition for a UNION SELECT.
The work-around to use the
SELECT * FROM "DOMAIN"."VIEWDEFS" where VIEWNAME = '<your view>'
does not really help :
By default, SQL Studio displays only 1kB of long columns.
Hence, the select without further change displays just this 1kB.
Since I know that my definition had about 35kB length, I configured SQL
Studio to limit the length for display of long columns to 50000 Byte or
even down to 38000Byte, and then again issued the query.
After about 10secs of contemplation, the system returned with a crash of
the video display system:
(a) a video reconfiguration (!) to ... presumably VGA display...
(640x480pixels, lowest color quality 4bit), and that is a dramatic
change on a laptop capable to show 1680x1050 pixel;
(b) the system error message with the dialog title:
Windows - Display Driver Stopped
and the content:
The ialmrnt5 display driver has stopped working normally. Save
your work and reboot the system to restore full display
functionality. The next time you reboot the machine a dialog
will be displayed giving you a chance to upload data about this
failure to Microsoft.
[OK]
TBH, this is close to the least what I expect when I have the chance to
set limits on lengths of to be displayed columns in SQL Studio.
The link you supply in the last of your messages
http://www.sapdb.org/webpts?wptsdetail=yes&ErrorType=0&ErrorID=1142469
leads to a web page in which an SQL Studio Vs. 7.6.02.00 is mentioned.
Where can I get this version in order to check, whether currently there
is indeed a chance to see all of my view definitions in a manner, which
allows to inspect in detail what I have defined in my views?
Michael Neuber
Auer, Wolfgang schrieb:
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]
