In SQL Query, How to Compare Across Different Rows

In SQL query, it is easy to compare values in different columns for same row. However, when compare value at column_A of Row 1 to the values at column_B of Rows 2 to 3, it would be hard. Sure, you always can write the cursor to process rows one by one. Here is a quick way: INNER JOIN this table by itself, you would get following rows: New row 1 = old row 1 and 1 New row 2 = old row 1 and 2 New row 3 = old row 1 and 3 New row 4 = old row 2 and 1 New row 5 = old row 2 and 2 New row 6 = old row 2 and 3 New row 7 = old row 3 and 1 New row 8 = old row 3 and 2 New row 9 = old row 3 and 3 Now you will be able to compare column_A and column_B at new rows 2 to 3, which is what you want. Just remember to eliminate row 1 and row 4 to 9.

Code Conventions: Transact-SQL

There is a great article about code conventions of Transact-SQL:

http://msdn.microsoft.com/en-us/library/ms177563(v=SQL.105).aspx

Transact-SQL Syntax Conventions (Transact-SQL)

Code Conventions: Microsoft Access SQL

1. Use original column name as much as possible until last presentative SELECT. This is to help for visual tracing back to where the data come from originally.

2. Never use format [something] as alias for the column, it would cause the visual confusion when you changed idea to reuse it, or extent to next level of expansion. You may use it at last presentative SELECT.

3. Always please comma (,) in front rather than at end of a line. It would enable you to easily conduct visual check, since all lines are started from a command or comma. For instance:

SELECT Col_1
, Col_2
FROM Tab
;

4. Always use your notepad or SQL editor to edit and save file. When complete, copy it onto Microsoft Access. This is because Microsoft Access will rewrite your scripts to make your life much harder. Every time when you need to edit it, open your text file to edit it and then paste it onto Access.

5. Doing your formatting job as much as possible in Access rather than at later stage at Excel or other presentation layer. It is even more important when you make your report automation.

Quick Sorting Method in SQL Query for Disorderly Sorting Criterion

ORDER BY can be used for orderly sorting criterion only. For disorderly sorting criterion, such as Car, Ship, Airplane, ORDER BY could not accomplish the task. Here is a quick method to use ORDER BY as usually.

Step One: Add order number in front of sorting field by using concatenation function.

Step Two: Using ORDER BY as usually.
Step Three: Using substring function to cut the first character.

Example in T-SQL:

SELECT SUBSTRING(newSort, 2, 8)
, Other_Fields
FROM
(
SELECT CASE WHEN Sort_Field = ‘Car’ THEN ‘1Car’
WHEN Sort_Field = ‘Ship’ THEN ‘2Ship’
WHEN Sort_Field = ‘Airplane’ THEN ‘3Airplane’ END AS newSort
, Other_Fields
FROM Tab
) AS innerQuery
ORDER BY newSort

This method can be used in T-SQL, PL/SQL, etc. For T-SQL, please pay special notice to this: ORDER BY cannot be used for any inner query, it must be placed at outer most query. The solution to overcome this weakness is to either abandon this method or save inner query result to temporary table.

Temporarily Disable and Re-enable the Constraints in Oracle

SQL command script files to disable and enable all constraints:

Disable:

set feedback off
set verify off
set echo off
prompt Finding constraints to disable...
set termout off
set pages 80
set heading off
set linesize 120
spool tmp_disable.sql
select 'spool igen_disable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||' DISABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c join user_tables u on c.table_name = u.table_name;
select 'exit;' from dual;
set termout on
prompt Disabling constraints now...
set termout off
@tmp_disable.sql;
exit
/

Enable:

set feedback off
set verify off
set wrap off
set echo off
prompt Finding constraints to enable...
set termout off
set lines 120
set heading off
spool tmp_enable.sql
select 'spool igen_enable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||' ENABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c join user_tables u on c.table_name = u.table_name;
/
select 'exit;' from dual;
set termout on
prompt Enabling constraints now...
set termout off
@tmp_enable;
!rm -i tmp_enable.sql;
exit
/

Scripts in PL/SQL to disable and enable all constraints:

Disable:

BEGIN
FOR i IN
( SELECT c.owner
, c.table_name
, c.constraint_name
FROM user_constraints c
JOIN user_tables t ON c.table_name = t.table_name
WHERE c.status = 'ENABLED'
ORDER BY c.constraint_type DESC
)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || i.owner || '.' || i.table_name || ' disable constraint ' || i.constraint_name);
END LOOP;
END;
/

Enable:

BEGIN
FOR i IN
( SELECT c.owner
, c.table_name
, c.constraint_name
FROM user_constraints c
JOIN user_tables t ON c.table_name = t.table_name
WHERE c.status = 'DISABLED'
ORDER BY c.constraint_type
)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);
END LOOP;
END;
/