dhis2-devs team mailing list archive

[Hieu Dang Duy <hieu.hispvietnam@xxxxxxxxx>]

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 !