Thread: [Squirrel-sql-users] Non Squirrel but what's wrong with my query
A Java SQL client for any JDBC compliant database
Brought to you by:
colbell,
gerdwagner
From: squirrel <squ...@ne...> - 2010-05-12 22:28:08
|
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 |
From: Mark J. R. <mar...@gm...> - 2010-05-12 22:48:00
|
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 <squ...@ne...> 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 > Squ...@li... > https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users > -- Mark J. Reed <mar...@gm...> |
From: squirrel <squ...@ne...> - 2010-05-13 14:24:05
|
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" <mar...@gm...> 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 <squ...@ne...> 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 >> Squ...@li... >> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users >> |
From: John H. <jh...@im...> - 2010-05-13 13:14:14
|
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/ jh...@im... FALaholic #11174 pgpk -a jh...@im... 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 |
From: Pablo M. <PMe...@de...> - 2010-05-13 22:49:36
|
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 | pme...@de... | demandbase | 415.683.2940 |
From: John H. <jh...@im...> - 2010-05-14 03:38:17
|
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/ jh...@im... FALaholic #11174 pgpk -a jh...@im... 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 |
From: Matthew D. <mda...@ja...> - 2010-05-14 20:36:15
|
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:PMe...@de...] Sent: Friday, 14 May, 2010 10:09 To: John Hardin Cc: squ...@li... 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 | pme...@de... | demandbase | 415.683.2940 -----Original Message----- From: John Hardin [mailto:jh...@im...] Sent: Thursday, May 13, 2010 8:38 PM To: Pablo Melendez Cc: squ...@li... 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/ jh...@im... FALaholic #11174 pgpk -a jh...@im... 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 Squ...@li... https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users |
From: Pablo M. <PMe...@de...> - 2010-05-14 21:05:48
|
Thanks, Matthew. I'll have our local expert help me put that in place. Pablo Melendez | Data Engineer | pme...@de... | demandbase | 415.683.2940 -----Original Message----- From: Matthew Dahlman [mailto:mda...@ja...] Sent: Friday, May 14, 2010 1:17 PM To: Pablo Melendez; 'John Hardin' Cc: squ...@li... 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:PMe...@de...] Sent: Friday, 14 May, 2010 10:09 To: John Hardin Cc: squ...@li... 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 | pme...@de... | demandbase | 415.683.2940 -----Original Message----- From: John Hardin [mailto:jh...@im...] Sent: Thursday, May 13, 2010 8:38 PM To: Pablo Melendez Cc: squ...@li... 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/ jh...@im... FALaholic #11174 pgpk -a jh...@im... 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 Squ...@li... https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users |
From: John H. <jh...@im...> - 2010-05-15 05:42:40
|
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/ jh...@im... FALaholic #11174 pgpk -a jh...@im... 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 |
From: Matthew D. <mda...@ja...> - 2010-05-17 17:58:37
|
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:jh...@im...] Sent: Friday, 14 May, 2010 22:43 To: Matthew Dahlman Cc: 'Pablo Melendez'; squ...@li... 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/ jh...@im... FALaholic #11174 pgpk -a jh...@im... 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 |
From: Pablo M. <PMe...@de...> - 2010-05-14 17:09:14
|
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 | pme...@de... | demandbase | 415.683.2940 -----Original Message----- From: John Hardin [mailto:jh...@im...] Sent: Thursday, May 13, 2010 8:38 PM To: Pablo Melendez Cc: squ...@li... 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/ jh...@im... FALaholic #11174 pgpk -a jh...@im... 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 |
From: John H. <jh...@im...> - 2010-05-15 05:41:41
|
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:jh...@im...] > Sent: Thursday, May 13, 2010 8:38 PM > To: Pablo Melendez > Cc: squ...@li... > 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/ > jh...@im... FALaholic #11174 pgpk -a jh...@im... > 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/ jh...@im... FALaholic #11174 pgpk -a jh...@im... 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 |