SQL Debugging

1. Problem

When using a GROUP BY clause every field in the SELECT list must be either:

• One of the GROUP BY terms - in this example customer_city.
• An aggregate function - for example SUM or COUNT
• An expression based on the above

In the example the field name may not be used on the SELECT line.
In a group by line each region shows up only once - however in a typical region such as Africa there are several different name values. WHich one should SQL pick?

Solutions

• Remove the offending field from the SELECT line
• Add the field to the GROUP BY clause
• Aggregate the offending field

Example

• select count(customer_id),customer_city from customers group by customer_city;

REFERENCE : http://sqlzoo.net/howto/source/u.cgi/err979/oracle

2. Problem 

Objective: insert value for date column in a table say ('12/03/2006');
Here is what you need to do:
Method 1:
insert into db (target_column_name) values (to_date('12/03/2006','mm/dd/yyyy'));

Method 2:
insert into db values (to_date('12/03/2006','mm/dd/yyyy'));

Method 1 is the preferred way because the target column is named. Target columns should always be named. Inserting into a table without naming the target columns is a bug waiting to happen. 

REFERENCE : http://forums.digitalpoint.com/showthread.php?t=109851