Currently, Phoenix doesn't support projecting selective columns of table or expressions in a view. You need to project all the columns with (select *).
Please see the section "Limitations" on this page or PHOENIX-1507. https://phoenix.apache.org/views.html On Thu, Oct 6, 2016 at 10:05 PM, Mich Talebzadeh <mich.talebza...@gmail.com> wrote: > > Hi, > > I have a table on top of a Hbase table as follows: > > create table "tsco" (PK VARCHAR PRIMARY KEY, "stock_daily"."Date" VARCHAR, > "stock_daily"."close" VARCHAR, "stock_daily"."high" VARCHAR, > "stock_daily"."low" VARCHAR, "stock_daily"."open" VARCHAR, > "stock_daily"."ticker" VARCHAR, "stock_daily"."stock" VARCHAR, > "stock_daily"."volume" VARCHAR > > This works fine. > > Now I want to create a view as follows: > > create view "tsco_clean" (Date date, close integer, high integer, low > integer, open integer, ticker integer, stock varchar, volume integer) as > select to_date("Date",'dd-MMM-yy'), to_number("close"), to_number("high"), > to_number("low"), to_number("open"), "ticker", "stock", to_number("volume") > from "tsco" where "close" != '-' and "high" != '-' and "low" != '-' and > "open" != '-' and to_number("volume") > 0; > > However, it falls over with the following error > > > Error: Error -1 (00000) : Error while executing SQL "create view > "tsco_clean" (Date date, close integer, high integer, low integer, open > integer, ticker integer, stock varchar, volume integer) as select > to_date("Date",'dd-MMM-yy'), to_number("close"), to_number("high"), > to_number("low"), to_number("open"), "ticker", "stock", to_number("volume") > from "tsco" where "close" != '-' and "high" != '-' and "low" != '-' and > "open" != '-' and to_number("volume") > 0": Remote driver error: > RuntimeException: org.apache.phoenix.exception.PhoenixParserException: > ERROR 604 (42P00): Syntax error. Mismatched input. Expecting "ASTERISK", > got "to_date" at line 1, column 151. -> PhoenixParserException: ERROR 604 > (42P00): Syntax error. Mismatched input. Expecting "ASTERISK", got > "to_date" at line 1, column 151. -> MismatchedTokenException: (null > exception message) (state=00000,code=-1) > > > Appreciate any feedback. > > > Thanks > > > Dr Mich Talebzadeh > > > > LinkedIn * > https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw > <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* > > > > http://talebzadehmich.wordpress.com > > > *Disclaimer:* Use it at your own risk. Any and all responsibility for any > loss, damage or destruction of data or any other property which may arise > from relying on this email's technical content is explicitly disclaimed. > The author will in no case be liable for any monetary damages arising from > such loss, damage or destruction. > > >
