dhis2-devs team mailing list archive

[Orvalho Augusto <orvaquim@xxxxxxxxx>]

Really thanks for this advice!

Caveman


On 6/27/11, Jason Pickering <jason.p.pickering@xxxxxxxxx> 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.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
>>
>>
>