Re: Re: table alias

t; > *From:* Yufan Liu > *Date:* 2015-06-17 07:45 > *To:* user > *Subject:* Re: table alias > Hi James, > > I change everything in the query to upper case: select "FACT"."C1" as “C0" > from (select COL1 as C1 from T1) as "FACT”, and it returns the sam

Re: Re: table alias

: user Subject: Re: table alias Hi James, I change everything in the query to upper case: select "FACT"."C1" as “C0" from (select COL1 as C1 from T1) as "FACT”, and it returns the same error: "Undefined column family. familyName=FACT.null." But when I chang

Re: table alias

Hi James, I change everything in the query to upper case: select "FACT"."C1" as “C0" from (select COL1 as C1 from T1) as "FACT”, and it returns the same error: "Undefined column family. familyName=FACT.null." But when I change to FACT."C1", it works fine. It seems there is problem with double quot

Re: table alias

Hi Yanlin, That's a legit error as well. Putting double quotes around an identifier makes it case sensitive. Without double quotes, identifiers are normalized by upper casing them. So you don't have a "c1" column, but you do have a "C1" column. Thanks, James On Tue, Jun 16, 2015 at 2:23 PM, yanlin

Re: table alias

Hi Jame, Sorry about the bad test case. The actual test case should be this: select "fact"."c1" as “c0" from (select col1 as c1 from t1) as "fact”; Error: ERROR 1001 (42I01): Undefined column family. familyName=fact.null SQLState: 42I01 ErrorCode: 1001 The BI tool i am using tries to generat

Re: table alias

Hi Yufan, The outer query should use the alias name (c1). If not, please file a JIRA when you have a chance. Thanks, James On Tue, Jun 16, 2015 at 2:03 PM, yanlin wang wrote: > Thanks James. My example is bad … > > >> On Jun 16, 2015, at 1:39 PM, James Taylor wrote: >> >> Hi Yanlin, >> The first

Re: table alias

Thanks James. My example is bad … > On Jun 16, 2015, at 1:39 PM, James Taylor wrote: > > Hi Yanlin, > The first error is legit: you're aliasing col1 as c1 in the inner > query but then trying to select it as col1 in the outer query. > > The second error is a known limitation of derived tables

Re: table alias

Hi James, I have tried the queries you guys are using above (select fact.c1 from (select k as k1, col1 as c1 from t1) as fact), it works. But in the result set, it displays the original column name (col1) instead of alias name (c1). Is that expected behavior? 2015-06-16 13:39 GMT-07:00 James Tay

Re: table alias

Hi Yanlin, The first error is legit: you're aliasing col1 as c1 in the inner query but then trying to select it as col1 in the outer query. The second error is a known limitation of derived tables (PHOENIX-2041). Thanks, James On Tue, Jun 16, 2015 at 11:48 AM, yanlin wang wrote: > Hi James, > >

Re: table alias

Hi James, I figured the error i got was not the phoenix version issue and here is the test case you can reproduce it: create table t1 (k varchar primary key, col1 varchar); select fact.col1 from (select k as k1, col1 as c1 from t1) as fact; Error: ERROR 1001 (42I01): Undefined column family.

Re: table alias

Hi James, Thanks for the info. I am using cloudera distribution CLABS_PHOENIX-4.3.0-1.clabs_phoenix1.0.0.p0.78 that can be the issue. I will try to play with other versions. Thx Yanlin > On Jun 16, 2015, at 9:34 AM, James Taylor wrote: > > Hi Yanlin, > What version of Phoenix are you using

Re: table alias

Hi Yanlin, What version of Phoenix are you using? I tried the following in sqlline, and it worked fine: 0: jdbc:phoenix:localhost> create table t1 (k varchar primary key, col1 varchar); No rows affected (10.29 seconds) 0: jdbc:phoenix:localhost> select fact.col1 from (select col1 from t1) as fact;