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.
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:
<< Home
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
Post a Comment
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
# posted by 
: Friday, 30 September, 2005
: Friday, 30 September, 2005 
<< Home
