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.
“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@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.
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@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 <orvaquim@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.
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@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.hispvietnam@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 <orvaquim@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.
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.
Yes, I confirmed this and may be it should be remove and allow to out of range between 1 and 255 characters.
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!
Sorry, did you create v_omissos view in your db before creating a new view which contains this “select * from v_omissos”? As in my testing with your query then it’s working fine.
···
On Mon, Jun 27, 2011 at 2:53 PM, Orvalho Augusto orvaquim@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 orvaquim@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.
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).
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@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.
On 6/27/11, Jason Pickering <jason.p.pickering@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.hispvietnam@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 >> <orvaquim@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 <orvaquim@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.hispvietnam@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 <orvaquim@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: DHIS 2 developers in Launchpad
>>> Post to : dhis2-devs@lists.launchpad.net
>>> Unsubscribe : DHIS 2 developers in Launchpad
>>> More help : ListHelp - Launchpad Help
>>>
>>>
>>
>>
>> --
>> Good heath !
>>
>
>
> --
> Databases, Data Analysis and
> OpenSource Software Consultant
> CENFOSS (www.cenfoss.co.mz)
> Fundacao Manhica/CISM (www.manhica.org)
> email: orvaquim@cenfoss.co.mz
> cell: +258846031265
> cell: +258828810980
>
In fact, SqlView just allow us to write a query where the SELECT keyword at the beginning as I presented in the previous mail and belongs to the resource tables (7 ones) to serve your purpose on querying data. If one of them deleted/regenerated then the view, which are linked to, would be recreated for updating in data.
On the regard issue of security, so SqlView does not support in calling a store-procedure untill now.
···
On Mon, Jun 27, 2011 at 11:49 PM, Orvalho Augusto orvaquim@gmail.com wrote:
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 orvaquim@gmail.com wrote:
