dhis2-devs team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

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.hispvietnam@xxxxxxxxx>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 <orvaquim@xxxxxxxxx>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 <orvaquim@xxxxxxxxx> 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.hispvietnam@xxxxxxxxx> 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 <orvaquim@xxxxxxxxx>
>> >> 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@xxxxxxxxxxxxxxxxxxx
>> >>> 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: orvaquim@xxxxxxxxxxxxx
>> > cell: +258846031265
>> > cell: +258828810980
>> >
>>
>
>
>
> --
> Good heath !
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs
> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-devs
> More help   : https://help.launchpad.net/ListHelp
>
>