Background Information Ordinal position notation, AKA ordinals, is column shorthand based on the column order in the list of columns in the SELECT clause, instead of either the column name or column alias. Commonly supported in the ORDER BY clause, some databases (MySQL 3.23+, PostgreSQL 8.0+) support the syn…
SELECT INTO using Oracle
I’m trying to do a SELECT INTO using Oracle. My query is: But I get the following error: Any ideas what’s wrong? The Standard behavior of the above should be as I originally thought: However Oracle implemented it totally differently in their own dialect of SQL Oracle Docs on Insert … Select …
How do I list all tables in a schema in Oracle SQL?
How do i list all tables in a schema in Oracle SQL?
How to do pagination in SQL Server 2008
How do you do pagination in SQL Server 2008 ? Answer You can try something like
Does Mysql have an equivalent to @@ROWCOUNT like in mssql?
How can I get row count values in MySQL as @@ROWCOUNT does in mssql? Answer For SELECTs you can use the FOUND_ROWS construct (documented here): which will return the number of rows in the last SELECT query (or if the first query has a LIMIT clause, it returns the number of rows there would’ve been witho…
How to vary connection string for different work locations
I am working on a C# 4.0, WPF 4.0, SQL 2008 project and I do work at home and in the office. I just setup SubVersion using Visual SVN per the recommendations found in other questions. The problem I am having is the connection string for the database is different for each location. At home I have the database …
How to select columns from a table which have non null values?
I have a table containing hundreds of columns many of which are null, and I would like have my select statement so that only those columns containing a value are returned. It would help me analyze data better. Something like: Select (non null columns) from tablename; I want to select all columns which have at…
List columns with indexes in PostgreSQL
I would like to get the columns that an index is on in PostgreSQL. In MySQL you can use SHOW INDEXES FOR table and look at the Column_name column. Does anything like this exist for PostgreSQL? I’ve tried d at the psql command prompt (with the -E option to show SQL) but it doesn’t show the informat…
SQL Inner Join On Null Values
I have a Join Isnull in a Join like this makes it slow. It’s like having a conditional Join. Is there any work around to something like this? I have a lot of records where QID is Null Anyone have a work around that doesn’t entail modifying the data Answer You have two options or easier
SQL Management Studio won’t recognize a table exists after scripted create
So if I create a new table in the query editor in SQL Management Studio after hitting refresh on the DB I can see and work with that table. However if I want to run another query referencign that table from withen the query editor it doesn’t reconize that table exists. I’ve tried hitting refresh a…