Sql view does not excute my SQL

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

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 :slight_smile:

···

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: 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 !

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 :slight_smile:

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: 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: orvaquim@cenfoss.co.mz
cell: +258846031265
cell: +258828810980

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 :slight_smile:

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: 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: orvaquim@cenfoss.co.mz
cell: +258846031265
cell: +258828810980

Hello Orralho,

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:

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 :slight_smile:

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: 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: orvaquim@cenfoss.co.mz

cell: +258846031265

cell: +258828810980


Good heath !

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 :slight_smile:

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: 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: orvaquim@cenfoss.co.mz

cell: +258846031265

cell: +258828810980


Good heath !

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 :slight_smile:

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: 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: orvaquim@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

Really thanks for this advice!

Caveman

···

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 :slight_smile:
>>
>> 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: 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: orvaquim@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

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:

Really thanks for this advice!

Caveman

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.comwrote:

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.comwrote:

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 :slight_smile:

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: 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: orvaquim@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

Hello,

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:

Really thanks for this advice!

Caveman

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.comwrote:

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.comwrote:

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 :slight_smile:

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: 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: orvaquim@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


Good heath !