Miscellaneous Database Tips

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