Wednesday, September 28, 2005
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
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.
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 - email@example.com
Thanks and Regards,