Over the years, I have used Informix, Sybase, DB2 and SQL Server databases. These are some notes from the past.
Informix
-
How to have substring in a SQL
select columnName
from table1
where columnName[1,3] = “abc”;
To concacentae two string columns in the SQL(Informix), use "||" instead of "+"
- To get the record which in one table, but not in another table, use "not exists", not "not in"
select * from table_A where not exists (select id_b from table_B where table_B.id_b = table_A.id_A)
- When write the SQL for a Union, for example:
select AA, " " from table_a
union
select BB, BC from table_b
if BC's size is greater than the length of " ", you will get a "Select error"
- To set datetime
update table1 set columnName = datetime(2003-09-03 00:00:01) year to second;
- Generate Dynamic SQL
select 'update msg set msg_ttl = ''ABC Inventory'' where msg_cd = '||msg_cd||';' from msg
where msg_ttl = 'DEF Inventory';
- Lock table
begin work;
lock table tableName in exclusive mode;
- Group by and having
Select column1 from table1 group by column1 having count(column1)>0;
DB2
When getting SQL0952N Processing was cancelled due to an interrupt. SQLSTATE=57014
Go to \Program Files\IBM\SQLLIB\db2cli.ini
Add the following session:
[Common]
QUERYTIMEOUTINTERVAL=500
Oracle
Get current date
select sysdate from dual; |
|
select * from table_a where some_date >= sysdate - 1(yesterday) |
|
To_date(to_char(sysdate -1, 'YY/mm/DD'), 'YY/mm/DD') -> yesterday, 00:00:00 |
|
SQL Server
Installation issue
I am learning SSIS on my new vista VM machine. I wanted to config the SQL Server database. But the database wouldn’t start. I am getting the following error:
“SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.”
After googling it, I found an article: http://blogs.msdn.com/sql_protocols/archive/2005/10/31/487090.aspx
It was talking about disabling “VIA protocol”. I went to SQL Server Configuration Manager and found VIA protocol, but it was disabled already. Then I read along the comments on that blog and found this:
“Looks like you disabled all protocols. Without a protocol enabled, sql server won't be able accept requests and process queries. You need to enable at least one protocol.”
Aha! That was my issue. I enabled TCP/IP protocol. Everything looks fine now.
Outer join
SELECT column_name(s)
FROM table_name1
LEFT OUTER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
