Under MySQL there is one problem. In mysql we call a stored procedure like this: call storedprocedure();
And this SQLview just allow SELECT as the first word. Any ideias? Caveman On Mon, Jun 27, 2011 at 1:33 PM, Orvalho Augusto <orvaq...@gmail.com> wrote: > Really thanks for this advice! > > Caveman > > > On 6/27/11, Jason Pickering <jason.p.picker...@gmail.com> wrote: > > It is important to keep in mind conceptually why this SQL view > functionality > > is there. DHIS2 destroys certain tables during some processes, such as > the > > resource table generation process. If you have any tables which the a > > particular view depends on, the database will prevent DHIS2 from dropping > > these tables. The resource generation process will fail (usually silently > > from within DHIS, but will show up in the logs). So, in general, you > should > > not use any views which are linked directly to DHIS database tables. > > > > Use of a stored procedure however will allow DHIS2 to delete a table, > even > > if the SP depends on it, and you could use the "SELECT * FROM > > my_stored_procedure" to allow (power) users/admins to execute the > > materialization of the SP (if you have more thatn 255 characters in the > > view, or actually need an SP for some other reason). > > > > Regards, > > Jason > > > > > > On Mon, Jun 27, 2011 at 5:34 AM, Hieu Dang Duy > > <hieu.hispviet...@gmail.com>wrote: > > > >> Yes, actually, the system will take your view's name be a name of > created > >> one in your db. But there is a validation on checking the given name > (from > >> input). If there is any special symple/characters then they will be > remove > >> before view created. Sorry for this inconvenient. > >> > >> > >> On Mon, Jun 27, 2011 at 3:28 PM, Orvalho Augusto > >> <orvaq...@gmail.com>wrote: > >> > >>> I know now why the Querry does not show UP: > >>> > >>> I gave the name "Missings_from_old_system" under DHIS. And DHIS > >>> created the following view _view_Missingsfromoldsystem". So the "_" is > >>> eaten! > >>> > >>> I reacreated with another name without "_" and it is working. > >>> > >>> Caveman > >>> > >>> > >>> On 6/27/11, Orvalho Augusto <orvaq...@gmail.com> wrote: > >>> > Thanks for the reply and I am so sorry to disturb you guys for my > >>> > ignorance. > >>> > > >>> > The thing is when I go from your the option 2 I get this: > >>> > "enter a value between 1 and 255 characters long." > >>> > That query has more than 255 characters. I did not explain clearly on > >>> > first time. > >>> > > >>> > So I have no choice. > >>> > > >>> > And indeed I place select * from v_omissos under SQL statement. I > >>> > execute it and it does not show up! > >>> > > >>> > Caveman > >>> > > >>> > > >>> > On 6/27/11, Hieu Dang Duy <hieu.hispviet...@gmail.com> wrote: > >>> >> Hello, > >>> >> > >>> >> "Sql View" is a functinality which allows the user (admin/mod) to > >>> create > >>> >> a > >>> >> view without interact inside DBMS directly. One more important point > >>> >> is > >>> >> that > >>> >> you cannot use any keyword such as "SELECT INTO, UPDATE, DELETE, > >>> >> ALTER" > >>> >> excepting "SELECT" in your query only. Btw, I would like to present > to > >>> >> you > >>> >> how to use this one for creating/using your own view. > >>> >> > >>> >> It's one of two ways to do this as following on: > >>> >> > >>> >> No.1: After you created your own view like v_omissos in your > database > >>> >> then > >>> >> go to the GUI of "Sql View" and creating another view with this > query > >>> >> statement "select * from v_omissos". Next clicking on the "Execute > >>> query" > >>> >> button which is corresponding to create a new view. Then clicking on > >>> >> "View" > >>> >> will see the result. > >>> >> > >>> >> No.2: Copying your main query as below and then save it with name > like > >>> >> "omissos" > >>> >> > >>> >> select '999' AS `OLDMISSCODE`, count(0) AS `contagem` from > `datavalue` > >>> >> where > >>> >> (`datavalue`.`value` = '999') > >>> >> union all > >>> >> select '9999' AS `OLDMISSCODE`,count(0) AS `count(*)` from > `datavalue` > >>> >> where > >>> >> (`datavalue`.`value` = '9999') > >>> >> union all select 'empty' AS `OLDMISSCODE`,count(0) AS `count(*)` > from > >>> >> `datavalue` where (trim(`datavalue`.`value`) = '') > >>> >> union all select 'NULL' AS `OLDMISSCODE`,count(0) AS `count(*)` from > >>> >> `datavalue` where isnull(`datavalue`.`value`) union all > >>> >> select 'NA from R conversion' AS `OLDMISSCODE`,count(0) AS > `count(*)` > >>> >> from > >>> >> `datavalue` where (`datavalue`.`value` = 'NA') > >>> >> > >>> >> Next to see your result you must click on "Execute query" button > first > >>> >> and > >>> >> then see by "View" button. > >>> >> > >>> >> *Notes*: The second way is the best choise for your case because we > >>> don't > >>> >> want to waste of capacity in memory of our database. If you are > >>> >> mention > >>> >> in > >>> >> the first way then you will realize that there are two views which > are > >>> >> created in the action. > >>> >> > >>> >> Hope you get it now :) > >>> >> > >>> >> On Sat, Jun 25, 2011 at 2:43 PM, Orvalho Augusto < > orvaq...@gmail.com> > >>> >> wrote: > >>> >> > >>> >>> I have this simply query: > >>> >>> -- > >>> >>> select * from v_omissos > >>> >>> -- > >>> >>> > >>> >>> The v_omissos is a view for: > >>> >>> CREATE OR REPLACE VIEW v_omissos AS > >>> >>> select '999' AS `OLDMISSCODE`, count(0) AS `contagem` from > >>> >>> `datavalue` > >>> >>> where (`datavalue`.`value` = '999') > >>> >>> union all > >>> >>> select '9999' AS `OLDMISSCODE`,count(0) AS `count(*)` from > >>> >>> `datavalue` > >>> >>> where (`datavalue`.`value` = '9999') > >>> >>> union all select 'empty' AS `OLDMISSCODE`,count(0) AS `count(*)` > from > >>> >>> `datavalue` where (trim(`datavalue`.`value`) = '') > >>> >>> union all select 'NULL' AS `OLDMISSCODE`,count(0) AS `count(*)` > from > >>> >>> `datavalue` where isnull(`datavalue`.`value`) union all > >>> >>> select 'NA from R conversion' AS `OLDMISSCODE`,count(0) AS > `count(*)` > >>> >>> from > >>> >>> `datavalue` where (`datavalue`.`value` = 'NA') > >>> >>> > >>> >>> That query has results directly on mysql but DHIS 2 claims > >>> >>> "Please execute query to create View table before viewing". > Actually > >>> >>> I > >>> >>> have > >>> >>> executed it. > >>> >>> > >>> >>> Nothing apears on the logs. > >>> >>> > >>> >>> Caveman > >>> >>> > >>> >>> > >>> >>> > >>> >>> > >>> >>> > >>> >>> _______________________________________________ > >>> >>> Mailing list: https://launchpad.net/~dhis2-devs > >>> >>> Post to : dhis2-devs@lists.launchpad.net > >>> >>> Unsubscribe : https://launchpad.net/~dhis2-devs > >>> >>> More help : https://help.launchpad.net/ListHelp > >>> >>> > >>> >>> > >>> >> > >>> >> > >>> >> -- > >>> >> Good heath ! > >>> >> > >>> > > >>> > > >>> > -- > >>> > Databases, Data Analysis and > >>> > OpenSource Software Consultant > >>> > CENFOSS (www.cenfoss.co.mz) > >>> > Fundacao Manhica/CISM (www.manhica.org) > >>> > email: orvaq...@cenfoss.co.mz > >>> > cell: +258846031265 > >>> > cell: +258828810980 > >>> > > >>> > >> > >> > >> > >> -- > >> Good heath ! > >> > >> > >> _______________________________________________ > >> Mailing list: https://launchpad.net/~dhis2-devs > >> Post to : dhis2-devs@lists.launchpad.net > >> Unsubscribe : https://launchpad.net/~dhis2-devs > >> More help : https://help.launchpad.net/ListHelp > >> > >> > > >
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : dhis2-devs@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
