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