Wednesday, September 28, 2005

 

Where 1=2

Here is an SQL trick for beginners.

Say you want to know the columns in a table. You can run sp_help on that table, but here is another trick:

1> select * from tablename where 1=2
2> go

This will come back with all the columns and no matching rows (because 1=2 is always false). It will also come back very quickly because the optimizer knows there shouldn't be any matching rows.

This can be especially handy in a complex query that joins a lot of tables. In those cases, sp_help either wouldn't help you, or would take several steps to be pieced together.

Comments:
Hi Robert,

I have a show stopper right here. The warehouse i am using has a View which has sub queries. This view has a TEXT (CLOB in terms of Sybase 12.5.3) Column. When i try to execute the query having an aggregate of some other column and this CLOB, it gives me a error 4426 - An unexpected error has occurred: [Sybase JDBC Driver][Sybase]This query cannot be processed because it contains TEXT, IMAGE or off-row Java columns and references a view defined with a subquery, an outer join, or a distinct clause.

Also, having the CLOB in Groub by Clause also fails with Aggregation. Please Note that we are not using CLOB anywhere directly - Instead we first convert it to CHAR and then take a SUBSTRING: SUBSTRING(CONVERT(CHAR,CLOB_COLUMN),1,100)

Yes, my view uses multiple tables and has UNION. Is there any workaround possible if straight solutions are not available?

Highly appreciate your help - Please mail me if you find anything - joyeshnovokesh@yahoo.com

Thanks and Regards,

Joyesh
 
http://sybasease.blogspot.com/2005/08/getting-help-with-sybase-ase.html
 
Thats quite intelligent :). Never mind. Thanks anyways.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?