SQuirreL SQL Client

The error message tells you what's wrong.  You have two things called
't' in each query.  Those aliases have to be unique across the whole
query, not just across one join.

On Wednesday, May 12, 2010, squirrel <squirrel@...> wrote:
>
> This
>
> select tt.tradeID
> from trades t inner join counterparty c
> on t.counterparty_code = c.counterparty_code
> right outer join #temp_tradeid t
> on tt.tradeID = t.tradeID
>
> or this
>
> select tt.tradeID
> from trades t inner join counterparty c
> on t.counterparty_code = c.counterparty_code
> right join #temp_tradeid t
> on tt.tradeID = t.tradeID
>
> fails. I only want to display the rows equal to the total rowcount of
> #temp_tradeid table.
>
>
>
> The Sybase Error code is 7364 and it is
>
> http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.11.0.1/saerrors_en11/errm139.html
>
> Error constant          SQLE_CORRELATION_NAME_AMBIGUOUS
> ODBC 2 State    37000
> ODBC 3 State    42000
> Parameter 1     Ambiguous correlation name.
> Severity        16
> SQLCODE         –139
> SQLSTATE        52012
> Sybase error code       7364
>
> Probable cause
>
> You identified two tables in the same FROM clause with the same correlation
> name.
>
>
> I am not sure if I am blind. But what's wrong with my double join? I know I
> can use =* but I would like to try this syntax.
>
> Any help would be greatly appreciated.
>
> Thanks!
>
> Neil
>
> ------------------------------------------------------------------------------
>
> _______________________________________________
> Squirrel-sql-users mailing list
> Squirrel-sql-users@...
> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>

-- 
Mark J. Reed <markjreed@...>

On Wed, 12 May 2010, squirrel wrote:

> select tt.tradeID
> from trades t inner join counterparty c
> on t.counterparty_code = c.counterparty_code
> right outer join #temp_tradeid t
> on tt.tradeID = t.tradeID
>
> or this
>
> select tt.tradeID
> from trades t inner join counterparty c
> on t.counterparty_code = c.counterparty_code
> right join #temp_tradeid t
> on tt.tradeID = t.tradeID
>
> fails.

/prep headdesk

Where do you define the "tt" table alias?

/exec headdesk

-- 
  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
  jhardin@...    FALaholic #11174     pgpk -a jhardin@...
  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
   Win95: Where do you want to go today?
   Vista: Where will Microsoft allow you to go today?
-----------------------------------------------------------------------
  5 days since a sunspot last seen - EPA blames CO2 emissions

Oh no, that is just to say I was so tired! :(

Thanks for catching it! :)

On Wed, 12 May 2010 18:47:53 -0400, "Mark J. Reed" <markjreed@...>
wrote:
> The error message tells you what's wrong.  You have two things called
> 't' in each query.  Those aliases have to be unique across the whole
> query, not just across one join.
> 
> On Wednesday, May 12, 2010, squirrel <squirrel@...> wrote:
>>
>> This
>>
>> select tt.tradeID
>> from trades t inner join counterparty c
>> on t.counterparty_code = c.counterparty_code
>> right outer join #temp_tradeid t
>> on tt.tradeID = t.tradeID
>>
>> or this
>>
>> select tt.tradeID
>> from trades t inner join counterparty c
>> on t.counterparty_code = c.counterparty_code
>> right join #temp_tradeid t
>> on tt.tradeID = t.tradeID
>>
>> fails. I only want to display the rows equal to the total rowcount of
>> #temp_tradeid table.
>>
>>
>>
>> The Sybase Error code is 7364 and it is
>>
>>
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.11.0.1/saerrors_en11/errm139.html
>>
>> Error constant          SQLE_CORRELATION_NAME_AMBIGUOUS
>> ODBC 2 State    37000
>> ODBC 3 State    42000
>> Parameter 1     Ambiguous correlation name.
>> Severity        16
>> SQLCODE         –139
>> SQLSTATE        52012
>> Sybase error code       7364
>>
>> Probable cause
>>
>> You identified two tables in the same FROM clause with the same
>> correlation
>> name.
>>
>>
>> I am not sure if I am blind. But what's wrong with my double join? I
know
>> I
>> can use =* but I would like to try this syntax.
>>
>> Any help would be greatly appreciated.
>>
>> Thanks!
>>
>> Neil
>>
>>
------------------------------------------------------------------------------
>>
>> _______________________________________________
>> Squirrel-sql-users mailing list
>> Squirrel-sql-users@...
>> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>>

Hello,

I am using version 3.1.1 for Windows. Ever since I applied the latest upgrades, Squirrel will export datasets only with the table column names and not with the column aliases I have assigned. I might have relabeled a column to 'Company Name' but the column header that is exported is always the field name or 'company_name' in this example.

Is there a setting that will correct this?

Thanks.

Pablo Melendez  |  Data Engineer  |  pmelendez@...  |  demandbase  |  415.683.2940

On Thu, 13 May 2010, Pablo Melendez wrote:

> I am using version 3.1.1 for Windows. Ever since I applied the latest 
> upgrades, Squirrel will export datasets only with the table column names 
> and not with the column aliases I have assigned. I might have relabeled 
> a column to 'Company Name' but the column header that is exported is 
> always the field name or 'company_name' in this example.

What database server?

How exactly are you aliasing the columns? Standard "x AS y" syntax? If so, 
that's amazing, as the database engine should be providing the aliased 
column names and Squirrel should not even be aware of the base column 
name...

-- 
  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
  jhardin@...    FALaholic #11174     pgpk -a jhardin@...
  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
   Win95: Where do you want to go today?
   Vista: Where will Microsoft allow you to go today?
-----------------------------------------------------------------------
  5 days since a sunspot last seen - EPA blames CO2 emissions

Using a MySQL server. 

I don't use AS. I'm not sure if AS is a reserved word in MySQL. I'll try that.

The syntax I use would be
	
	SELECT company_name 'Company Name' FROM s.t

Thank you.

Pablo Melendez  |  Data Engineer  |  pmelendez@...  |  demandbase  |  415.683.2940

-----Original Message-----
From: John Hardin [mailto:jhardin@...] 
Sent: Thursday, May 13, 2010 8:38 PM
To: Pablo Melendez
Cc: squirrel-sql-users@...
Subject: Re: [Squirrel-sql-users] column alias not exporting

On Thu, 13 May 2010, Pablo Melendez wrote:

> I am using version 3.1.1 for Windows. Ever since I applied the latest 
> upgrades, Squirrel will export datasets only with the table column names 
> and not with the column aliases I have assigned. I might have relabeled 
> a column to 'Company Name' but the column header that is exported is 
> always the field name or 'company_name' in this example.

What database server?

How exactly are you aliasing the columns? Standard "x AS y" syntax? If so, 
that's amazing, as the database engine should be providing the aliased 
column names and Squirrel should not even be aware of the base column 
name...

-- 
  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
  jhardin@...    FALaholic #11174     pgpk -a jhardin@...
  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
   Win95: Where do you want to go today?
   Vista: Where will Microsoft allow you to go today?
-----------------------------------------------------------------------
  5 days since a sunspot last seen - EPA blames CO2 emissions

Pablo,

MySQL accepts AS, but I expect that it will not change your results at
all.

It's probably due to MySQL's implementation of getColumnName() and
getColumnLabel().

Google up those terms and you'll find the relevant MySQL bugs. The result
is highly dependant on the exact version of the MySQL JDBC driver that you
use. Its implementation of getColumnName has gone back and forth between
using the column name or the alias multiple times.

Probably what you need is to use "useOldAliasMetadataBehavior" in the url
properties. That's likely to make everything return to what you're
expecting.

Regards,
Matt
Jaspersoft

-----Original Message-----
From: Pablo Melendez [mailto:PMelendez@...]
Sent: Friday, 14 May, 2010 10:09
To: John Hardin
Cc: squirrel-sql-users@...
Subject: Re: [Squirrel-sql-users] column alias not exporting

Using a MySQL server.

I don't use AS. I'm not sure if AS is a reserved word in MySQL. I'll try
that.

The syntax I use would be

	SELECT company_name 'Company Name' FROM s.t

Thank you.

Pablo Melendez  |  Data Engineer  |  pmelendez@...  | 
demandbase  |  415.683.2940

-----Original Message-----
From: John Hardin [mailto:jhardin@...]
Sent: Thursday, May 13, 2010 8:38 PM
To: Pablo Melendez
Cc: squirrel-sql-users@...
Subject: Re: [Squirrel-sql-users] column alias not exporting

On Thu, 13 May 2010, Pablo Melendez wrote:

> I am using version 3.1.1 for Windows. Ever since I applied the latest
> upgrades, Squirrel will export datasets only with the table column
> names and not with the column aliases I have assigned. I might have
> relabeled a column to 'Company Name' but the column header that is
> exported is always the field name or 'company_name' in this example.

What database server?

How exactly are you aliasing the columns? Standard "x AS y" syntax? If so,
that's amazing, as the database engine should be providing the aliased
column names and Squirrel should not even be aware of the base column
name...

--
  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
  jhardin@...    FALaholic #11174     pgpk -a jhardin@...
  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
   Win95: Where do you want to go today?
   Vista: Where will Microsoft allow you to go today?
-----------------------------------------------------------------------
  5 days since a sunspot last seen - EPA blames CO2 emissions

--------------------------------------------------------------------------
----

_______________________________________________
Squirrel-sql-users mailing list
Squirrel-sql-users@...
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users

Thanks, Matthew. I'll have our local expert help me put that in place.

Pablo Melendez  |  Data Engineer  |  pmelendez@...  |  demandbase  |  415.683.2940


-----Original Message-----
From: Matthew Dahlman [mailto:mdahlman@...] 
Sent: Friday, May 14, 2010 1:17 PM
To: Pablo Melendez; 'John Hardin'
Cc: squirrel-sql-users@...
Subject: RE: [Squirrel-sql-users] column alias not exporting

Pablo,

MySQL accepts AS, but I expect that it will not change your results at
all.

It's probably due to MySQL's implementation of getColumnName() and
getColumnLabel().

Google up those terms and you'll find the relevant MySQL bugs. The result
is highly dependant on the exact version of the MySQL JDBC driver that you
use. Its implementation of getColumnName has gone back and forth between
using the column name or the alias multiple times.

Probably what you need is to use "useOldAliasMetadataBehavior" in the url
properties. That's likely to make everything return to what you're
expecting.

Regards,
Matt
Jaspersoft

-----Original Message-----
From: Pablo Melendez [mailto:PMelendez@...]
Sent: Friday, 14 May, 2010 10:09
To: John Hardin
Cc: squirrel-sql-users@...
Subject: Re: [Squirrel-sql-users] column alias not exporting

Using a MySQL server.

I don't use AS. I'm not sure if AS is a reserved word in MySQL. I'll try
that.

The syntax I use would be

	SELECT company_name 'Company Name' FROM s.t

Thank you.

Pablo Melendez  |  Data Engineer  |  pmelendez@...  | 
demandbase  |  415.683.2940

-----Original Message-----
From: John Hardin [mailto:jhardin@...]
Sent: Thursday, May 13, 2010 8:38 PM
To: Pablo Melendez
Cc: squirrel-sql-users@...
Subject: Re: [Squirrel-sql-users] column alias not exporting

On Thu, 13 May 2010, Pablo Melendez wrote:

> I am using version 3.1.1 for Windows. Ever since I applied the latest
> upgrades, Squirrel will export datasets only with the table column
> names and not with the column aliases I have assigned. I might have
> relabeled a column to 'Company Name' but the column header that is
> exported is always the field name or 'company_name' in this example.

What database server?

How exactly are you aliasing the columns? Standard "x AS y" syntax? If so,
that's amazing, as the database engine should be providing the aliased
column names and Squirrel should not even be aware of the base column
name...

--
  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
  jhardin@...    FALaholic #11174     pgpk -a jhardin@...
  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
   Win95: Where do you want to go today?
   Vista: Where will Microsoft allow you to go today?
-----------------------------------------------------------------------
  5 days since a sunspot last seen - EPA blames CO2 emissions

--------------------------------------------------------------------------
----

_______________________________________________
Squirrel-sql-users mailing list
Squirrel-sql-users@...
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users

On Fri, 14 May 2010, Pablo Melendez wrote:

> Using a MySQL server.
>
> I don't use AS. I'm not sure if AS is a reserved word in MySQL. I'll try that.

"SELECT real_name AS alias" is standard SQL. I would be very surprised if 
MySql didn't support it.

> The syntax I use would be
>
> 	SELECT company_name 'Company Name' FROM s.t

Some dialects make "AS" optional. Still, I am surprised that you're not 
seeing the alias. The database should return a result set using the column 
alias; the original name shouldn't even be available.

> Thank you.
>
> Pablo Melendez
>
> -----Original Message-----
> From: John Hardin [mailto:jhardin@...]
> Sent: Thursday, May 13, 2010 8:38 PM
> To: Pablo Melendez
> Cc: squirrel-sql-users@...
> Subject: Re: [Squirrel-sql-users] column alias not exporting
>
> On Thu, 13 May 2010, Pablo Melendez wrote:
>
>> I am using version 3.1.1 for Windows. Ever since I applied the latest
>> upgrades, Squirrel will export datasets only with the table column names
>> and not with the column aliases I have assigned. I might have relabeled
>> a column to 'Company Name' but the column header that is exported is
>> always the field name or 'company_name' in this example.
>
> What database server?
>
> How exactly are you aliasing the columns? Standard "x AS y" syntax? If so,
> that's amazing, as the database engine should be providing the aliased
> column names and Squirrel should not even be aware of the base column
> name...
>
> --
>  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
>  jhardin@...    FALaholic #11174     pgpk -a jhardin@...
>  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
> -----------------------------------------------------------------------
>   Win95: Where do you want to go today?
>   Vista: Where will Microsoft allow you to go today?
> -----------------------------------------------------------------------
>  5 days since a sunspot last seen - EPA blames CO2 emissions
>

-- 
  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
  jhardin@...    FALaholic #11174     pgpk -a jhardin@...
  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
   After ten years (1998-2008) of draconian gun control in the State
   of Massachusetts, the results are in: firearms-related assaults up
   78%, firearms-related homicides up 67%, assault-related emergency
   room visits up 331%. Gun Control does not reduce violent crime.
-----------------------------------------------------------------------
  6 days since a sunspot last seen - EPA blames CO2 emissions

On Fri, 14 May 2010, Matthew Dahlman wrote:

> Google up those terms and you'll find the relevant MySQL bugs. The 
> result is highly dependant on the exact version of the MySQL JDBC driver 
> that you use. Its implementation of getColumnName has gone back and 
> forth between using the column name or the alias multiple times.

...what possible justificaiton could there be for ignoring the alias?

-- 
  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
  jhardin@...    FALaholic #11174     pgpk -a jhardin@...
  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
   After ten years (1998-2008) of draconian gun control in the State
   of Massachusetts, the results are in: firearms-related assaults up
   78%, firearms-related homicides up 67%, assault-related emergency
   room visits up 331%. Gun Control does not reduce violent crime.
-----------------------------------------------------------------------
  6 days since a sunspot last seen - EPA blames CO2 emissions

John,

I can offer no justification at all.

But if you find those MySQL issues you'll see that the general idea is
this:

getColumnLabel should return the alias. 
getColumnName should return the column name.
No it shouldn't.
Yes it should.


-Matt

-----Original Message-----
From: John Hardin [mailto:jhardin@...] 
Sent: Friday, 14 May, 2010 22:43
To: Matthew Dahlman
Cc: 'Pablo Melendez'; squirrel-sql-users@...
Subject: RE: [Squirrel-sql-users] column alias not exporting

On Fri, 14 May 2010, Matthew Dahlman wrote:

> Google up those terms and you'll find the relevant MySQL bugs. The 
> result is highly dependant on the exact version of the MySQL JDBC 
> driver that you use. Its implementation of getColumnName has gone back 
> and forth between using the column name or the alias multiple times.

...what possible justificaiton could there be for ignoring the alias?

-- 
  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
  jhardin@...    FALaholic #11174     pgpk -a jhardin@...
  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
   After ten years (1998-2008) of draconian gun control in the State
   of Massachusetts, the results are in: firearms-related assaults up
   78%, firearms-related homicides up 67%, assault-related emergency
   room visits up 331%. Gun Control does not reduce violent crime.
-----------------------------------------------------------------------
  6 days since a sunspot last seen - EPA blames CO2 emissions