dhis2-devs team mailing list archive

[Orvalho Augusto <orvaquim@xxxxxxxxx>]

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