12,179 reputation
11947
bio website
location Munich
age
visits member for 3 years, 5 months
seen 49 mins ago

Oct
11
comment How to order by Levenshtein distance?
The levensthein distance is calculated between the name of the "current" row and the name of the "previous" row, where "previous is defined by the order on the name.
Oct
11
comment How to create identity field in MySQL without using auto_increment column?
Just because some company is using a specific design doesn't mean it's a good design.
Oct
11
comment How to create identity field in MySQL without using auto_increment column?
"For example I can say the first 4 characters are the object id and the last 14 characters are the record identifier" - mixing different types of information in a single column is almost always a bad choice. Create three columns one with an autoincrement and make those three the primary key (or unique key). And Oracle does not have an "identifier type that does this trick"
Oct
10
comment What's the term that describes filtering query that makes use of indexes
@ypercube: but the query would need to reference the computed column, correct? So you would need to change the query, or does SQL Server rewrite the query when it detects that the expression is the same as the definition of the computed column?
Oct
10
comment What's the term that describes filtering query that makes use of indexes
Note that Postgres has an index type that can be used for the expression LIKE '%A%' but neither Oracle nor SQL Server can do that. And you can define an index in Oracle and Postgres that makes SUBSTRING(column, 1, 3) = 'abc' "Sargable"
Oct
10
comment Need Query for th problem in oracle
@miracle173: again see the manual docs.oracle.com/cd/E11882_01/appdev.112/e10765/…
Oct
10
comment Need Query for th problem in oracle
If you need this a lot, you could create your own aggregate function. That would probably be faster that your solution.
Oct
10
comment Need Query for th problem in oracle
@miracle173: the manual: docs.oracle.com/cd/E11882_01/server.112/e41084/… "Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator"
Oct
10
comment Need Query for th problem in oracle
Just a side node: Oracle recommends to stop using the proprietary (+) operator and use the standard left join operator
Oct
9
comment Change existing column in PG to auto-incremental primary key
@zetah: Quote from the manual (the link I added): "the sequence is marked as "owned by" the column, so that it will be dropped if the column or table is dropped"
Oct
8
comment Detecting the locked table or row in SQL Server
Works for me too. Thanks a lot
Oct
8
comment Detecting the locked table or row in SQL Server
@MaxVernon: thanks for confirming that. But then I'm even more confused. Why does it not return anything although I know there is a lock and a blocked session?
Oct
8
comment Oracle 11g user created with Create session but able to update the table?
Please show us the complete script you used to create the user. And the update statement you are using.
Oct
8
comment Getting rid of partitioning in Postgresql 9
create table new_table as select * from parent_table. Then drop the old ones.
Oct
8
comment Use if exists in postgresql
And what is the expected output? I don't really understand the "then I want to check for flag_id=2 and value='Yes'" part.
Oct
8
comment Use if exists in postgresql
You need to provide a lot more details than that. Show us the table definition (as create table) show us some sample data (as insert into ...) and show us the expected output based on that sample data. Ideally create a sqlfiddle.com example
Oct
7
comment How to Search a Database of Package Deals for Specific Items with the Fewest Unwanted Extra Items
Which DBMS are you using? Postgres? Oracle?
Oct
6
comment PostgreSQL: How can I identify the connections which is blocking ALTER TABLE?
wiki.postgresql.org/wiki/Lock_Monitoring
Oct
6
comment CTE returns an empty set even though anchor is non-empty
See here for the Postgres example: sqlfiddle.com/#!15/04fab/3 When I try your statement with 1.4.181 (which is still beta btw), the statement never finishes (I killed it after 2 minutes) which smells like an infinite recursion.
Oct
6
comment CTE returns an empty set even though anchor is non-empty
When I run your statement (with the invalid join) in Postgres, it seems the recursion never finishes - my statement only stops because I have set a limit on the number of rows. So I would assume H2 silently stops the infinite recursion somehow. Btw: when I run your statement with H2 1.3.174 I get an General error: "java.lang.IndexOutOfBoundsException: Index: 0, Size: 0"