Create View ERROR

Hi folks, I trying to create a view based in a select statement. The follow command was performed in dbaccess screen: select a.t_ugbs_o, a.t_dsca_o, a.t_resp_o,b.t_year_o, round(b.t_meta_o_1,2) as t_meta_o_1,round(b.t_meta_o_2,2) as t_meta_o_2, round(b.t_meta_o_3,2) as t_meta_o_3,round(b.t_meta_o_4,2) as t_meta_o_4,round(b.t_meta_o_5,2) as t_meta_o_5, round(b.t_meta_o_6,2) as t_meta_o_6,round(b.t_meta_o_7,2) as t_meta_o_7,round(b.t_meta_o_8,2) as t_meta_o_8, round(b.t_meta_o_9,2) as t_meta_o_9,round(b.t_meta_o_10,2) as t_meta_o_10,round(b.t_meta_o_11,2) as t_meta_o_11, round(b.t_meta_o_12,2) as t_meta_o_12, b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_meta_o_5+b.t_meta_o_6+b. t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10 +b.t_meta_o_11+b.t_meta_o_12 as t_tota_o, round(((b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_meta_o_5+b.t_met a_o_6+b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+ b.t_meta_o_10 +b.t_meta_o_11+b.t_meta_o_12)/ sum(c.t_tota_o)) * 100,2) as totalporc, round((b.t_meta_o_1/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+ b.t_meta_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_1, round((b.t_meta_o_2/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met a_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_2, round((b.t_meta_o_3/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met a_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_3, round((b.t_meta_o_4/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met a_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_4, round((b.t_meta_o_5/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met a_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_5, round((b.t_meta_o_6/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met a_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_6, round((b.t_meta_o_7/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met a_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_7, round((b.t_meta_o_8/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met a_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_8, round((b.t_meta_o_9/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met a_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_9, round((b.t_meta_o_10/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_me ta_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_10, round((b.t_meta_o_11/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_me ta_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_11, round((b.t_meta_o_12/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_me ta_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_12, sum(c.t_tota_o) as totalvalor from tstcms013200 a, tstsls951200 b, tstsls951200 c where a.t_ugbs_o=b.t_ugbs_o and c.t_year_o=b.t_year_oand c.t_cena_o=b.t_cena_o group by a.t_ugbs_o, a.t_dsca_o, a.t_resp_o,b.t_tota_o,b.t_year_o,b.t_meta_o_1,b.t_meta_o_2,b.t_meta_o_3,b.t_meta _o_4,b.t_meta_o_5,b.t_meta_o_6,b.t_meta_o_7,b.t_meta_o_8,b.t_meta_o_9,b.t_meta_o _10,b.t_meta_o_11,b.t_meta_o_12,b.t_meta_o_1,b.t_part_o_1,b.t_part_o_2,b.t_part_ o_3,b.t_part_o_4,b.t_part_o_5,b.t_part_o_6,b.t_part_o_7,b.t_part_o_8,b.t_part_o_ 9,b.t_part_o_10,b.t_part_o_11,b.t_part_o_12 order by t_ugbs_o; when a put the line "create view tbl_view as" on the top of sql query, I received the error Database selected. 201: A syntax error has occurred.Error in line 120Near character position 0Database closed. This line is exactly the last line of query: order by t_ugbs_o; Am I doing somthing wrong? Anybody? thanks, André _________________________________________________________________ Veja só alguns dos novos serviços online no Windows Live Ideas — são tão novos que ainda não foram disponibilizados oficialmente. http://ideas.live.com

Order by is not permitted in a VIEW. Art S. Kagel ----- Original Message ----- From: Andr• Luiz Rufino <ids@iiug.org> At: 4/12 12:45:10 Hi folks, I trying to create a view based in a select statement. The follow command was performed in dbaccess screen: select a.t_ugbs_o, a.t_dsca_o, a.t_resp_o,b.t_year_o, round(b.t_meta_o_1,2) as t_meta_o_1,round(b.t_meta_o_2,2) as t_meta_o_2, round(b.t_meta_o_3,2) as t_meta_o_3,round(b.t_meta_o_4,2) as t_meta_o_4,round(b.t_meta_o_5,2) as t_meta_o_5, round(b.t_meta_o_6,2) as t_meta_o_6,round(b.t_meta_o_7,2) as t_meta_o_7,round(b.t_meta_o_8,2) as t_meta_o_8, round(b.t_meta_o_9,2) as t_meta_o_9,round(b.t_meta_o_10,2) as t_meta_o_10,round(b.t_meta_o_11,2) as t_meta_o_11, round(b.t_meta_o_12,2) as t_meta_o_12, b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_meta_o_5+b.t_meta_o_6+b. t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10 +b.t_meta_o_11+b.t_meta_o_12 as t_tota_o, round(((b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_meta_o_5+b.t_met a_o_6+b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+ b.t_meta_o_10 +b.t_meta_o_11+b.t_meta_o_12)/ sum(c.t_tota_o)) * 100,2) as totalporc, round((b.t_meta_o_1/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+ b.t_meta_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_1, round((b.t_meta_o_2/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met a_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_2, round((b.t_meta_o_3/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met a_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_3, round((b.t_meta_o_4/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met a_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_4, round((b.t_meta_o_5/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met a_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_5, round((b.t_meta_o_6/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met a_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_6, round((b.t_meta_o_7/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met a_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_7, round((b.t_meta_o_8/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met a_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_8, round((b.t_meta_o_9/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met a_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_9, round((b.t_meta_o_10/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_me ta_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_10, round((b.t_meta_o_11/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_me ta_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_11, round((b.t_meta_o_12/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_me ta_o_5+b.t_meta_o_6+ b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 )) * 100,2) as t_part_o_12, sum(c.t_tota_o) as totalvalor from tstcms013200 a, tstsls951200 b, tstsls951200 c where a.t_ugbs_o=b.t_ugbs_o and c.t_year_o=b.t_year_oand c.t_cena_o=b.t_cena_o group by a.t_ugbs_o, a.t_dsca_o, a.t_resp_o,b.t_tota_o,b.t_year_o,b.t_meta_o_1,b.t_meta_o_2,b.t_meta_o_3,b.t_meta _o_4,b.t_meta_o_5,b.t_meta_o_6,b.t_meta_o_7,b.t_meta_o_8,b.t_meta_o_9,b.t_meta_o _10,b.t_meta_o_11,b.t_meta_o_12,b.t_meta_o_1,b.t_part_o_1,b.t_part_o_2,b.t_part_ o_3,b.t_part_o_4,b.t_part_o_5,b.t_part_o_6,b.t_part_o_7,b.t_part_o_8,b.t_part_o_ 9,b.t_part_o_10,b.t_part_o_11,b.t_part_o_12 order by t_ugbs_o; when a put the line "create view tbl_view as" on the top of sql query, I received the error Database selected. 201: A syntax error has occurred.Error in line 120Near character position 0Database closed. This line is exactly the last line of query: order by t_ugbs_o; Am I doing somthing wrong? Anybody? thanks, André _________________________________________________________________ Veja só alguns dos novos serviços online no Windows Live Ideas - s o t o novos que ainda n o foram disponibilizados oficialmente. http://ideas.live.com ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Ok. I took the "order by" off my query and I got another error. Database selected. 383: Need to specify view column names in the view definition.Error in line118Near character position 13 Database closed. But the error point to the last line of query again. when I run the query and build a temp table with the result, all columns are showed with especifc name each one. t_ugbs_o 000001t_dsca_o Atacado / Varejot_resp_o Carlost_year_o 2006t_meta_o_1 6106694.70t_meta_o_2 5783227.31t_meta_o_3 10760013.46t_meta_o_4 11560060.92t_meta_o_5 13025877.81t_meta_o_6 10639105.66t_meta_o_7 10639105.66t_meta_o_8 8034574.34t_meta_o_9 10549188.14t_meta_o_10 12334080.42t_meta_o_11 11386485.91t_meta_o_12 0.00t_tota_o 110818414.3300totalporc 37.44t_part_o_1 5.51t_part_o_2 5.22t_part_o_3 9.71t_part_o_4 10.43t_part_o_5 11.75t_part_o_6 9.60t_part_o_7 9.60t_part_o_8 7.25t_part_o_9 9.52t_part_o_10 11.13t_part_o_11 10.27t_part_o_12 0.00totalvalor 295988980.6100 Am I doing anything else that I coudn't. Thanks again, André> To: ids@iiug.org> From: kagel@bloomberg.net> Subject: Re: Create View ERROR [8877]> Date: Thu, 12 Apr 2007 12:58:58 -0400> > Order by is not permitted in a VIEW. > > Art S. Kagel > ----- Original Message ----- > From: Andr• Luiz Rufino <ids@iiug.org> > At: 4/12 12:45:10 > > Hi folks, > > I trying to create a view based in a select statement. The follow command was > performed in dbaccess screen: > > select > a.t_ugbs_o, a.t_dsca_o, a.t_resp_o,b.t_year_o, > round(b.t_meta_o_1,2) as t_meta_o_1,round(b.t_meta_o_2,2) as t_meta_o_2, > round(b.t_meta_o_3,2) as t_meta_o_3,round(b.t_meta_o_4,2) as > t_meta_o_4,round(b.t_meta_o_5,2) as t_meta_o_5, round(b.t_meta_o_6,2) as > t_meta_o_6,round(b.t_meta_o_7,2) as t_meta_o_7,round(b.t_meta_o_8,2) as > t_meta_o_8, round(b.t_meta_o_9,2) as t_meta_o_9,round(b.t_meta_o_10,2) as > t_meta_o_10,round(b.t_meta_o_11,2) as t_meta_o_11, round(b.t_meta_o_12,2) as > t_meta_o_12, > > > b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_meta_o_5+b.t_meta_o_6+b. > t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10 > +b.t_meta_o_11+b.t_meta_o_12 as t_tota_o, > > round(((b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_meta_o_5+b.t_met > a_o_6+b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+ > b.t_meta_o_10 +b.t_meta_o_11+b.t_meta_o_12)/ sum(c.t_tota_o)) * 100,2) as > totalporc, > round((b.t_meta_o_1/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+ > b.t_meta_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_1, > > > round((b.t_meta_o_2/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_2, > > > round((b.t_meta_o_3/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_3, > > > round((b.t_meta_o_4/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_4, > > > round((b.t_meta_o_5/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_5, > > > round((b.t_meta_o_6/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_6, > > > round((b.t_meta_o_7/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_7, > > > round((b.t_meta_o_8/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_8, > > > round((b.t_meta_o_9/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_9, > > > round((b.t_meta_o_10/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_me > ta_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_10, > > > round((b.t_meta_o_11/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_me > ta_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_11, > > > round((b.t_meta_o_12/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_me > ta_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_12, > sum(c.t_tota_o) as totalvalor > from tstcms013200 a, tstsls951200 b, tstsls951200 c > where a.t_ugbs_o=b.t_ugbs_o > and c.t_year_o=b.t_year_oand c.t_cena_o=b.t_cena_o > group by > a.t_ugbs_o, a.t_dsca_o, > > a.t_resp_o,b.t_tota_o,b.t_year_o,b.t_meta_o_1,b.t_meta_o_2,b.t_meta_o_3,b.t_meta > > _o_4,b.t_meta_o_5,b.t_meta_o_6,b.t_meta_o_7,b.t_meta_o_8,b.t_meta_o_9,b.t_meta_o > > _10,b.t_meta_o_11,b.t_meta_o_12,b.t_meta_o_1,b.t_part_o_1,b.t_part_o_2,b.t_part_ > > o_3,b.t_part_o_4,b.t_part_o_5,b.t_part_o_6,b.t_part_o_7,b.t_part_o_8,b.t_part_o_ > 9,b.t_part_o_10,b.t_part_o_11,b.t_part_o_12 > order by t_ugbs_o; > > when a put the line "create view tbl_view as" on the top of sql query, I > received the error > > Database selected. > 201: A syntax error has occurred.Error in line 120Near character position 0 > Database closed. > > This line is exactly the last line of query: > order by t_ugbs_o; > > Am I doing somthing wrong? Anybody? > > thanks, > > Andrй > > _________________________________________________________________ > Veja sу alguns dos novos serviзos online no Windows Live Ideas - s o t o novos > que ainda n o foram disponibilizados oficialmente. > http://ideas.live.com > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Procure em qualquer página Web com protecção eficaz. Obtenha já o Windows Live Toolbar GRATUITO! http://www.toolbar.live.com

Here the result of query again. The last one was not that clear t_ugbs_o 000002t_dsca_o Scala Atacado/Varejot_resp_o Michelottot_year_o 2006t_meta_o_1 3548305.30t_meta_o_2 4323381.62t_meta_o_3 4808994.38t_meta_o_4 4440566.39t_meta_o_5 4236136.59t_meta_o_6 3500829.42t_meta_o_7 3911503.87t_meta_o_8 3946427.36t_meta_o_9 3728662.81t_meta_o_10 4234657.71t_meta_o_11 5344763.77t_meta_o_12 0.00t_tota_o 46024229.22000totalporc 15.55t_part_o_1 7.71t_part_o_2 9.39t_part_o_3 10.45t_part_o_4 9.65t_part_o_5 9.20t_part_o_6 7.61t_part_o_7 8.50t_part_o_8 8.57t_part_o_9 8.10t_part_o_10 9.20t_part_o_11 11.61t_part_o_12 0.00totalvalor 295988980.6100 Thanks again> To: ids@iiug.org> From: kagel@bloomberg.net> Subject: Re: Create View ERROR [8877]> Date: Thu, 12 Apr 2007 12:58:58 -0400> > Order by is not permitted in a VIEW. > > Art S. Kagel > ----- Original Message ----- > From: Andr・ Luiz Rufino <ids@iiug.org> > At: 4/12 12:45:10 > > Hi folks, > > I trying to create a view based in a select statement. The follow command was > performed in dbaccess screen: > > select > a.t_ugbs_o, a.t_dsca_o, a.t_resp_o,b.t_year_o, > round(b.t_meta_o_1,2) as t_meta_o_1,round(b.t_meta_o_2,2) as t_meta_o_2, > round(b.t_meta_o_3,2) as t_meta_o_3,round(b.t_meta_o_4,2) as > t_meta_o_4,round(b.t_meta_o_5,2) as t_meta_o_5, round(b.t_meta_o_6,2) as > t_meta_o_6,round(b.t_meta_o_7,2) as t_meta_o_7,round(b.t_meta_o_8,2) as > t_meta_o_8, round(b.t_meta_o_9,2) as t_meta_o_9,round(b.t_meta_o_10,2) as > t_meta_o_10,round(b.t_meta_o_11,2) as t_meta_o_11, round(b.t_meta_o_12,2) as > t_meta_o_12, > > > b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_meta_o_5+b.t_meta_o_6+b. > t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10 > +b.t_meta_o_11+b.t_meta_o_12 as t_tota_o, > > round(((b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_meta_o_5+b.t_met > a_o_6+b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+ > b.t_meta_o_10 +b.t_meta_o_11+b.t_meta_o_12)/ sum(c.t_tota_o)) * 100,2) as > totalporc, > round((b.t_meta_o_1/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+ > b.t_meta_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_1, > > > round((b.t_meta_o_2/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_2, > > > round((b.t_meta_o_3/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_3, > > > round((b.t_meta_o_4/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_4, > > > round((b.t_meta_o_5/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_5, > > > round((b.t_meta_o_6/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_6, > > > round((b.t_meta_o_7/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_7, > > > round((b.t_meta_o_8/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_8, > > > round((b.t_meta_o_9/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_9, > > > round((b.t_meta_o_10/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_me > ta_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_10, > > > round((b.t_meta_o_11/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_me > ta_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_11, > > > round((b.t_meta_o_12/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_me > ta_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_12, > sum(c.t_tota_o) as totalvalor > from tstcms013200 a, tstsls951200 b, tstsls951200 c > where a.t_ugbs_o=b.t_ugbs_o > and c.t_year_o=b.t_year_oand c.t_cena_o=b.t_cena_o > group by > a.t_ugbs_o, a.t_dsca_o, > > a.t_resp_o,b.t_tota_o,b.t_year_o,b.t_meta_o_1,b.t_meta_o_2,b.t_meta_o_3,b.t_meta > > _o_4,b.t_meta_o_5,b.t_meta_o_6,b.t_meta_o_7,b.t_meta_o_8,b.t_meta_o_9,b.t_meta_o > > _10,b.t_meta_o_11,b.t_meta_o_12,b.t_meta_o_1,b.t_part_o_1,b.t_part_o_2,b.t_part_ > > o_3,b.t_part_o_4,b.t_part_o_5,b.t_part_o_6,b.t_part_o_7,b.t_part_o_8,b.t_part_o_ > 9,b.t_part_o_10,b.t_part_o_11,b.t_part_o_12 > order by t_ugbs_o; > > when a put the line "create view tbl_view as" on the top of sql query, I > received the error > > Database selected. > 201: A syntax error has occurred.Error in line 120Near character position 0 > Database closed. > > This line is exactly the last line of query: > order by t_ugbs_o; > > Am I doing somthing wrong? Anybody? > > thanks, > > Andr・ > > _________________________________________________________________ > Veja s・ alguns dos novos servi・os online no Windows Live Ideas - s o t o novos > que ainda n o foram disponibilizados oficialmente. > http://ideas.live.com > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Veja só alguns dos novos serviços online no Windows Live Ideas — são tão novos que ainda não foram disponibilizados oficialmente. http://ideas.live.com

Last chance, t_ugbs_o 000002 t_dsca_o Scala Atacado/Varejo t_resp_o Michelotto t_year_o 2006 t_meta_o_1 3548305.30 t_meta_o_2 4323381.62 t_meta_o_3 4808994.38 t_meta_o_4 4440566.39 t_meta_o_5 4236136.59 t_meta_o_6 3500829.42 t_meta_o_7 3911503.87 t_meta_o_8 3946427.36 t_meta_o_9 3728662.81 t_meta_o_10 4234657.71 t_meta_o_11 5344763.77 t_meta_o_12 0.00 t_tota_o 46024229.22000 totalporc 15.55 t_part_o_1 7.71 t_part_o_2 9.39 t_part_o_3 10.45 t_part_o_4 9.65 t_part_o_5 9.20 t_part_o_6 7.61 t_part_o_6 7.61t_part_o_7 8.50 t_part_o_8 8.57 t_part_o_9 8.10 t_part_o_10 9.20 t_part_o_11 11.61 t_part_o_12 0.00 totalvalor 295988980.6100 Ok, thanks , one more time....> To: ids@iiug.org> From: andre_rufino@msn.com> Subject: RE: Create View ERROR [8879]> Date: Thu, 12 Apr 2007 13:25:40 -0400> > Here the result of query again. The last one was not that clear > > t_ugbs_o 000002t_dsca_o Scala Atacado/Varejot_resp_o Michelottot_year_o > 2006t_meta_o_1 3548305.30t_meta_o_2 4323381.62t_meta_o_3 4808994.38t_meta_o_4 > 4440566.39t_meta_o_5 4236136.59t_meta_o_6 3500829.42t_meta_o_7 > 3911503.87t_meta_o_8 3946427.36t_meta_o_9 3728662.81t_meta_o_10 > 4234657.71t_meta_o_11 5344763.77t_meta_o_12 0.00t_tota_o > 46024229.22000totalporc 15.55t_part_o_1 7.71t_part_o_2 9.39t_part_o_3 > 10.45t_part_o_4 9.65t_part_o_5 9.20t_part_o_6 7.61t_part_o_7 8.50t_part_o_8 > 8.57t_part_o_9 8.10t_part_o_10 9.20t_part_o_11 11.61t_part_o_12 0.00totalvalor > 295988980.6100 > > Thanks again> To: ids@iiug.org> From: kagel@bloomberg.net> Subject: Re: Create > View ERROR [8877]> Date: Thu, 12 Apr 2007 12:58:58 -0400> > Order by is not > permitted in a VIEW. > > Art S. Kagel > ----- Original Message ----- > From: > Andr・ Luiz Rufino <ids@iiug.org> > At: 4/12 12:45:10 > > Hi folks, > > I > trying to create a view based in a select statement. The follow command was > > performed in dbaccess screen: > > select > a.t_ugbs_o, a.t_dsca_o, > a.t_resp_o,b.t_year_o, > round(b.t_meta_o_1,2) as > t_meta_o_1,round(b.t_meta_o_2,2) as t_meta_o_2, > round(b.t_meta_o_3,2) as > t_meta_o_3,round(b.t_meta_o_4,2) as > t_meta_o_4,round(b.t_meta_o_5,2) as > t_meta_o_5, round(b.t_meta_o_6,2) as > t_meta_o_6,round(b.t_meta_o_7,2) as > t_meta_o_7,round(b.t_meta_o_8,2) as > t_meta_o_8, round(b.t_meta_o_9,2) as > t_meta_o_9,round(b.t_meta_o_10,2) as > t_meta_o_10,round(b.t_meta_o_11,2) as > t_meta_o_11, round(b.t_meta_o_12,2) as > t_meta_o_12, > > > > b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_meta_o_5+b.t_meta_o_6+b. > > t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10 > > +b.t_meta_o_11+b.t_meta_o_12 as t_tota_o, > > > round(((b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_meta_o_5+b.t_met > > a_o_6+b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+ > b.t_meta_o_10 > +b.t_meta_o_11+b.t_meta_o_12)/ sum(c.t_tota_o)) * 100,2) as > totalporc, > > round((b.t_meta_o_1/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+ > > b.t_meta_o_5+b.t_meta_o_6+ > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > )) > * 100,2) as t_part_o_1, > > > > round((b.t_meta_o_2/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > > a_o_5+b.t_meta_o_6+ > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > )) > * 100,2) as t_part_o_2, > > > > round((b.t_meta_o_3/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > > a_o_5+b.t_meta_o_6+ > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > )) > * 100,2) as t_part_o_3, > > > > round((b.t_meta_o_4/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > > a_o_5+b.t_meta_o_6+ > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > )) > * 100,2) as t_part_o_4, > > > > round((b.t_meta_o_5/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > > a_o_5+b.t_meta_o_6+ > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > )) > * 100,2) as t_part_o_5, > > > > round((b.t_meta_o_6/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > > a_o_5+b.t_meta_o_6+ > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > )) > * 100,2) as t_part_o_6, > > > > round((b.t_meta_o_7/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > > a_o_5+b.t_meta_o_6+ > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > )) > * 100,2) as t_part_o_7, > > > > round((b.t_meta_o_8/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > > a_o_5+b.t_meta_o_6+ > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > )) > * 100,2) as t_part_o_8, > > > > round((b.t_meta_o_9/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > > a_o_5+b.t_meta_o_6+ > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > )) > * 100,2) as t_part_o_9, > > > > round((b.t_meta_o_10/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_me > > ta_o_5+b.t_meta_o_6+ > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > )) > * 100,2) as t_part_o_10, > > > > round((b.t_meta_o_11/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_me > > ta_o_5+b.t_meta_o_6+ > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > )) > * 100,2) as t_part_o_11, > > > > round((b.t_meta_o_12/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_me > > ta_o_5+b.t_meta_o_6+ > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > )) > * 100,2) as t_part_o_12, > sum(c.t_tota_o) as totalvalor > from > tstcms013200 a, tstsls951200 b, tstsls951200 c > where a.t_ugbs_o=b.t_ugbs_o > > and c.t_year_o=b.t_year_oand c.t_cena_o=b.t_cena_o > group by > a.t_ugbs_o, > a.t_dsca_o, > > > a.t_resp_o,b.t_tota_o,b.t_year_o,b.t_meta_o_1,b.t_meta_o_2,b.t_meta_o_3,b.t_meta > > > > _o_4,b.t_meta_o_5,b.t_meta_o_6,b.t_meta_o_7,b.t_meta_o_8,b.t_meta_o_9,b.t_meta_o > > > > _10,b.t_meta_o_11,b.t_meta_o_12,b.t_meta_o_1,b.t_part_o_1,b.t_part_o_2,b.t_part_ > > > > o_3,b.t_part_o_4,b.t_part_o_5,b.t_part_o_6,b.t_part_o_7,b.t_part_o_8,b.t_part_o_ > > 9,b.t_part_o_10,b.t_part_o_11,b.t_part_o_12 > order by t_ugbs_o; > > when a > put the line "create view tbl_view as" on the top of sql query, I > received > the error > > Database selected. > 201: A syntax error has occurred.Error in > line 120Near character position 0 > Database closed. > > This line is exactly > the last line of query: > order by t_ugbs_o; > > Am I doing somthing wrong? > Anybody? > > thanks, > > Andr・ > > > _________________________________________________________________ > Veja s・ > alguns dos novos servi・os online no Windows Live Ideas - s o t o novos > que > ainda n o foram disponibilizados oficialmente. > http://ideas.live.com > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ****************************************************************************** * > > Forum Note: Use "Reply" to post a response in t

Ahh, that actually was the first thing I caught but when I spotted the ORDER BY I assumed that you were just using shorthand when you said: "create view tbl_view as". Yes, the CREATE VIEW has to have a column name list that corresponds to the projection clause of the underlying SELECT or the returns of the underlying EXECUTE PROCEDURE statement. So you should have: "create view tbl_view ( t_ugbs_o, t_dsca_o, ... ) as" replacing the elipsis with names for the rest of the selected items. Art S. Kagel ----- Original Message ----- From: Andr• Luiz Rufino <ids@iiug.org> At: 4/12 13:21:17 Ok. I took the "order by" off my query and I got another error. Database selected. 383: Need to specify view column names in the view definition.Error in line118Near character position 13 Database closed. But the error point to the last line of query again. when I run the query and build a temp table with the result, all columns are showed with especifc name each one. t_ugbs_o 000001t_dsca_o Atacado / Varejot_resp_o Carlost_year_o 2006t_meta_o_1 6106694.70t_meta_o_2 5783227.31t_meta_o_3 10760013.46t_meta_o_4 11560060.92t_meta_o_5 13025877.81t_meta_o_6 10639105.66t_meta_o_7 10639105.66t_meta_o_8 8034574.34t_meta_o_9 10549188.14t_meta_o_10 12334080.42t_meta_o_11 11386485.91t_meta_o_12 0.00t_tota_o 110818414.3300totalporc 37.44t_part_o_1 5.51t_part_o_2 5.22t_part_o_3 9.71t_part_o_4 10.43t_part_o_5 11.75t_part_o_6 9.60t_part_o_7 9.60t_part_o_8 7.25t_part_o_9 9.52t_part_o_10 11.13t_part_o_11 10.27t_part_o_12 0.00totalvalor 295988980.6100 Am I doing anything else that I coudn't. Thanks again, Andr ©> To: ids@iiug.org> From: kagel@bloomberg.net> Subject: Re: Create View ERROR [8877]> Date: Thu, 12 Apr 2007 12:58:58 -0400> > Order by is not permitted in a VIEW. > > Art S. Kagel > ----- Original Message ----- > From: AndrâEURc Luiz Rufino <ids@iiug.org> > At: 4/12 12:45:10 > > Hi folks, > > I trying to create a view based in a select statement. The follow command was > performed in dbaccess screen: > > select > a.t_ugbs_o, a.t_dsca_o, a.t_resp_o,b.t_year_o, > round(b.t_meta_o_1,2) as t_meta_o_1,round(b.t_meta_o_2,2) as t_meta_o_2, > round(b.t_meta_o_3,2) as t_meta_o_3,round(b.t_meta_o_4,2) as > t_meta_o_4,round(b.t_meta_o_5,2) as t_meta_o_5, round(b.t_meta_o_6,2) as > t_meta_o_6,round(b.t_meta_o_7,2) as t_meta_o_7,round(b.t_meta_o_8,2) as > t_meta_o_8, round(b.t_meta_o_9,2) as t_meta_o_9,round(b.t_meta_o_10,2) as > t_meta_o_10,round(b.t_meta_o_11,2) as t_meta_o_11, round(b.t_meta_o_12,2) as > t_meta_o_12, > > > b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_meta_o_5+b.t_meta_o_6+b. > t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10 > +b.t_meta_o_11+b.t_meta_o_12 as t_tota_o, > > round(((b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_meta_o_5+b.t_met > a_o_6+b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+ > b.t_meta_o_10 +b.t_meta_o_11+b.t_meta_o_12)/ sum(c.t_tota_o)) * 100,2) as > totalporc, > round((b.t_meta_o_1/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+ > b.t_meta_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_1, > > > round((b.t_meta_o_2/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_2, > > > round((b.t_meta_o_3/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_3, > > > round((b.t_meta_o_4/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_4, > > > round((b.t_meta_o_5/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_5, > > > round((b.t_meta_o_6/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_6, > > > round((b.t_meta_o_7/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_7, > > > round((b.t_meta_o_8/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_8, > > > round((b.t_meta_o_9/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > a_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_9, > > > round((b.t_meta_o_10/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_me > ta_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_10, > > > round((b.t_meta_o_11/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_me > ta_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_11, > > > round((b.t_meta_o_12/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_me > ta_o_5+b.t_meta_o_6+ > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > )) > * 100,2) as t_part_o_12, > sum(c.t_tota_o) as totalvalor > from tstcms013200 a, tstsls951200 b, tstsls951200 c > where a.t_ugbs_o=b.t_ugbs_o > and c.t_year_o=b.t_year_oand c.t_cena_o=b.t_cena_o > group by > a.t_ugbs_o, a.t_dsca_o, > > a.t_resp_o,b.t_tota_o,b.t_year_o,b.t_meta_o_1,b.t_meta_o_2,b.t_meta_o_3,b.t_meta > > _o_4,b.t_meta_o_5,b.t_meta_o_6,b.t_meta_o_7,b.t_meta_o_8,b.t_meta_o_9,b.t_meta_o > > _10,b.t_meta_o_11,b.t_meta_o_12,b.t_meta_o_1,b.t_part_o_1,b.t_part_o_2,b.t_part_ > > o_3,b.t_part_o_4,b.t_part_o_5,b.t_part_o_6,b.t_part_o_7,b.t_part_o_8,b.t_part_o_ > 9,b.t_part_o_10,b.t_part_o_11,b.t_part_o_12 > order by t_ugbs_o; > > when a put the line "create view tbl_view as" on the top of sql query, I > received the error > > Database selected. > 201: A syntax error has occurred.Error in line 120Near character position 0 > Database closed. > > This line is exactly the last line of query: > order by t_ugbs_o; > > Am I doing somthing wrong? Anybody? > > thanks, > > Andr6 29/32 > > _________________________________________________________________ > Veja sÑf alguns dos novos serviD*os online no Windows Live Ideas - s o t o novos > que ainda n o foram disponibilizados oficialmente. > http://ideas.live.com > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************

Ok. Now it works. Thanks a lot.> To: ids@iiug.org> From: kagel@bloomberg.net> Subject: RE: Create View ERROR [8881]> Date: Thu, 12 Apr 2007 13:35:31 -0400> > Ahh, that actually was the first thing I caught but when I spotted the ORDER > BY > I assumed that you were just using shorthand when you said: "create view > tbl_view as". Yes, the CREATE VIEW has to have a column name list that > corresponds to the projection clause of the underlying SELECT or the returns > of > the underlying EXECUTE PROCEDURE statement. So you should have: > > "create view tbl_view ( t_ugbs_o, t_dsca_o, ... ) as" replacing the elipsis > with names for the rest of the selected items. > > Art S. Kagel > ----- Original Message ----- > From: Andr?Luiz Rufino <ids@iiug.org> > At: 4/12 13:21:17 > > Ok. I took the "order by" off my query and I got another error. > > Database selected. > 383: Need to specify view column names in the view definition.Error in line > 118Near character position 13 > Database closed. > > But the error point to the last line of query again. when I run the query and > build a temp table with the result, all columns are showed with especifc name > each one. > > t_ugbs_o 000001t_dsca_o Atacado / Varejot_resp_o Carlost_year_o 2006t_meta_o_1 > 6106694.70t_meta_o_2 5783227.31t_meta_o_3 10760013.46t_meta_o_4 > 11560060.92t_meta_o_5 13025877.81t_meta_o_6 10639105.66t_meta_o_7 > 10639105.66t_meta_o_8 8034574.34t_meta_o_9 10549188.14t_meta_o_10 > 12334080.42t_meta_o_11 11386485.91t_meta_o_12 0.00t_tota_o > 110818414.3300totalporc 37.44t_part_o_1 5.51t_part_o_2 5.22t_part_o_3 > 9.71t_part_o_4 10.43t_part_o_5 11.75t_part_o_6 9.60t_part_o_7 9.60t_part_o_8 > 7.25t_part_o_9 9.52t_part_o_10 11.13t_part_o_11 10.27t_part_o_12 > 0.00totalvalor 295988980.6100 > > Am I doing anything else that I coudn't. > > Thanks again, > > Andr ? To: ids@iiug.org> From: kagel@bloomberg.net> Subject: Re: Create View > ERROR [8877]> Date: Thu, 12 Apr 2007 12:58:58 -0400> > Order by is not > permitted in a VIEW. > > Art S. Kagel > ----- Original Message ----- > From: > Andr釫URc Luiz Rufino <ids@iiug.org> > At: 4/12 12:45:10 > > Hi folks, > > I > trying to create a view based in a select statement. The follow command was > > performed in dbaccess screen: > > select > a.t_ugbs_o, a.t_dsca_o, > a.t_resp_o,b.t_year_o, > round(b.t_meta_o_1,2) as > t_meta_o_1,round(b.t_meta_o_2,2) as t_meta_o_2, > round(b.t_meta_o_3,2) as > t_meta_o_3,round(b.t_meta_o_4,2) as > t_meta_o_4,round(b.t_meta_o_5,2) as > t_meta_o_5, round(b.t_meta_o_6,2) as > t_meta_o_6,round(b.t_meta_o_7,2) as > t_meta_o_7,round(b.t_meta_o_8,2) as > t_meta_o_8, round(b.t_meta_o_9,2) as > t_meta_o_9,round(b.t_meta_o_10,2) as > t_meta_o_10,round(b.t_meta_o_11,2) as > t_meta_o_11, round(b.t_meta_o_12,2) as > t_meta_o_12, > > > > > b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_meta_o_5+b.t_meta_o_6+b. > > > t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10 > > +b.t_meta_o_11+b.t_meta_o_12 as t_tota_o, > > > > round(((b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_meta_o_5+b.t_met > > > a_o_6+b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+ > b.t_meta_o_10 > +b.t_meta_o_11+b.t_meta_o_12)/ sum(c.t_tota_o)) * 100,2) as > totalporc, > > round((b.t_meta_o_1/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+ > > b.t_meta_o_5+b.t_meta_o_6+ > > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > > )) > * 100,2) as t_part_o_1, > > > > > round((b.t_meta_o_2/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > > > a_o_5+b.t_meta_o_6+ > > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > > )) > * 100,2) as t_part_o_2, > > > > > round((b.t_meta_o_3/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > > > a_o_5+b.t_meta_o_6+ > > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > > )) > * 100,2) as t_part_o_3, > > > > > round((b.t_meta_o_4/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > > > a_o_5+b.t_meta_o_6+ > > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > > )) > * 100,2) as t_part_o_4, > > > > > round((b.t_meta_o_5/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > > > a_o_5+b.t_meta_o_6+ > > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > > )) > * 100,2) as t_part_o_5, > > > > > round((b.t_meta_o_6/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > > > a_o_5+b.t_meta_o_6+ > > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > > )) > * 100,2) as t_part_o_6, > > > > > round((b.t_meta_o_7/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > > > a_o_5+b.t_meta_o_6+ > > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > > )) > * 100,2) as t_part_o_7, > > > > > round((b.t_meta_o_8/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > > > a_o_5+b.t_meta_o_6+ > > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > > )) > * 100,2) as t_part_o_8, > > > > > round((b.t_meta_o_9/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_met > > > a_o_5+b.t_meta_o_6+ > > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > > )) > * 100,2) as t_part_o_9, > > > > > round((b.t_meta_o_10/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_me > > > ta_o_5+b.t_meta_o_6+ > > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > > )) > * 100,2) as t_part_o_10, > > > > > round((b.t_meta_o_11/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_me > > > ta_o_5+b.t_meta_o_6+ > > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > > )) > * 100,2) as t_part_o_11, > > > > > round((b.t_meta_o_12/(b.t_meta_o_1+b.t_meta_o_2+b.t_meta_o_3+b.t_meta_o_4+b.t_me > > > ta_o_5+b.t_meta_o_6+ > > > > b.t_meta_o_7+b.t_meta_o_8+b.t_meta_o_9+b.t_meta_o_10+b.t_meta_o_11+b.t_meta_o_12 > > > )) > * 100,2) as t_part_o_12, > sum(c.t_tota_o) as totalvalor > from > tstcms013200 a, tstsls951200 b, tstsls951200 c > where a.t_ugbs_o=b.t_ugbs_o > > and c.t_year_o=b.t_year_oand c.t_cena_o=b.t_cena_o > group by > a.t_ugbs_o, > a.t_dsca_o, > > > > a.t_resp_o,b.t_tota_o,b.t_year_o,b.t_meta_o_1,b.t_meta_o_2,b.t_meta_o_3,b.t_meta > > > > > > _o_4,b.t_meta_o_5,b.t_meta_o_6,b.t_meta_o_7,b.t_meta_o_8,b.t_meta_o_9,b.t_meta_o > > > > > > _10,b.t_meta_o_11,b.t_meta_o_12,b.t_meta_o_1,b.t_part_o_1,b.t_part_o_2,b.t_part_ > > > > > > o_3,b.t_part_o_4,b.t_part_o_5,b.t_part_o_6,b.t_part_o_7,b.t_part_o_8,b.t_part_o_ > > > 9,b.t_part_o_10,b.t_part_o_11,b.t_part_o_12 > order by t_ugbs_o; > > when a > put the line "create view tbl_view as" on the top of sql query, I > received > the error > > Database selected. > 201: A syntax error has occurred.Error in > line 120Near character position 0 > Database closed. > > This line is exactly > the last line of query: > order by t_ugbs_o; > > Am I doing somthing wrong? > Anybody? > > thanks, > > Andr6 29/32 > > > _________________________________________________________________ > Veja s裦 > alguns dos novos serviD*os online no Windows Live Ideas - s o t o novos > que