SQL Problem 
Author Message
 SQL Problem

 Hi folks,

         There is a small sql problem I faced.

 I created a table with one column
       create table abc
                (
                   col_name varchar(8) NOT NULL
                );

 After this I didn't do any kind of transaction against this table.
 So, my next sql statement was
     select a.column
       from table a, abc b
      where a.col_name != b.col_name;

 This didn't give any result.
So i changed this to :
     select a.column
       from table a, abc b
      where a.col_name = b.col_name;
And Lo!! no results....!!!!!!!
  Finally i inserted a space in the table abc.
And both the sql's started working.

  ANY COMMENTS....

REgards

Praveen
Sunnyvale, CA

tel no : 650.594.9899 X 217

______________________________________________________
Get Your Private, Free Email at http://www.***.com/



Wed, 18 Jun 1902 08:00:00 GMT
 SQL Problem

Try:
        select a.column
               from table a, outer abc b
              where a.col_name != b.col_name;
Notice the 'outer'.
This says that if you don't find a row in table b select the row from
table a anyway.

Quote:
} -----Original Message-----

} Sent: Friday, December 12, 1997 1:21 PM

} Subject:      SQL Problem
}
}
}
}  Hi folks,
}
}          There is a small sql problem I faced.
}
}  I created a table with one column
}        create table abc
}                 (
}                    col_name varchar(8) NOT NULL
}                 );
}
}  After this I didn't do any kind of transaction against this table.
}  So, my next sql statement was
}      select a.column
}        from table a, abc b
}       where a.col_name != b.col_name;
}
}  This didn't give any result.
} So i changed this to :
}      select a.column
}        from table a, abc b
}       where a.col_name = b.col_name;
} And Lo!! no results....!!!!!!!
}   Finally i inserted a space in the table abc.
} And both the sql's started working.
}
}
}   ANY COMMENTS....
}
} REgards
}
} Praveen
} Sunnyvale, CA

} tel no : 650.594.9899 X 217
}
}
} ______________________________________________________
} Get Your Private, Free Email at http://www.hotmail.com



Wed, 18 Jun 1902 08:00:00 GMT
 SQL Problem

Quote:
}>Try:
}>   select a.column
}>          from table a, outer abc b
}>         where a.col_name != b.col_name;
}>Notice the 'outer'.
}>This says that if you don't find a row in table b select the row
}>from
}>table a anyway.

 Thanks for the solution.
 But my question is then what does the column in the table abc contain
when the table is created, if not NULL or space. (Refer below)

 Regards
 Praveen

Quote:
}
}
}
}  Hi folks,
}
}          There is a small sql problem I faced.
}
}  I created a table with one column
}        create table abc
}                 (
}                    col_name varchar(8) NOT NULL
}                 );
}
}  After this I didn't do any kind of transaction against this table.
}  So, my next sql statement was
}      select a.column
}        from table a, abc b
}       where a.col_name != b.col_name;
}
}  This didn't give any result.
} So i changed this to :
}      select a.column
}        from table a, abc b
}       where a.col_name = b.col_name;
} And Lo!! no results....!!!!!!!
}   Finally i inserted a space in the table abc.
} And both the sql's started working.
}
}
}   ANY COMMENTS....
}
} REgards
}
} Praveen
} Sunnyvale, CA

} tel no : 650.594.9899 X 217
}
}
} ______________________________________________________
} Get Your Private, Free Email at http://www.hotmail.com

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com


Wed, 18 Jun 1902 08:00:00 GMT
 SQL Problem

Praveen,
In INFORMIX  we can not compare a NULL to any other value including a NULL.
.
When you are executing the second SQL, since there is no data, the engine
might be treating the data as NULL and hence you did not get any data. But
when you inserted a space which is not a NULL value the engine is able to
compare and get you the results.

Nagesh


Friday December 12, 1997 10:21 AM



cc:    (bcc: Nagesh Daliparthy/PI/Prudential)
Subject:  SQL Problem

 Hi folks,
         There is a small sql problem I faced.
 I created a table with one column
       create table abc
                (
                   col_name varchar(8) NOT NULL
                );
 After this I didn't do any kind of transaction against this table.
 So, my next sql statement was
     select a.column
       from table a, abc b
      where a.col_name != b.col_name;
 This didn't give any result.
So i changed this to :
     select a.column
       from table a, abc b
      where a.col_name = b.col_name;
And Lo!! no results....!!!!!!!
  Finally i inserted a space in the table abc.
And both the sql's started working.

  ANY COMMENTS....
REgards
Praveen
Sunnyvale, CA

tel no : 650.594.9899 X 217

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com



Wed, 18 Jun 1902 08:00:00 GMT
 SQL Problem

Praveen,
In INFORMIX  we can not compare a NULL to any other value including a NULL.
.
When you are executing the second SQL, since there is no data, the engine
might be treating the data as NULL and hence you did not get any data. But
when you inserted a space which is not a NULL value the engine is able to
compare and get you the results.

Nagesh


Friday December 12, 1997 10:21 AM



cc:    (bcc: Nagesh Daliparthy/PI/Prudential)
Subject:  SQL Problem

 Hi folks,
         There is a small sql problem I faced.
 I created a table with one column
       create table abc
                (
                   col_name varchar(8) NOT NULL
                );
 After this I didn't do any kind of transaction against this table.
 So, my next sql statement was
     select a.column
       from table a, abc b
      where a.col_name != b.col_name;
 This didn't give any result.
So i changed this to :
     select a.column
       from table a, abc b
      where a.col_name = b.col_name;
And Lo!! no results....!!!!!!!
  Finally i inserted a space in the table abc.
And both the sql's started working.

  ANY COMMENTS....
REgards
Praveen
Sunnyvale, CA

tel no : 650.594.9899 X 217

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com



Wed, 18 Jun 1902 08:00:00 GMT
 SQL Problem

Quote:
} }>Try:
} }> select a.column
} }>        from table a, outer abc b
} }>       where a.col_name != b.col_name;
} }>Notice the 'outer'.
} }>This says that if you don't find a row in table b select the row
} }>from
} }>table a anyway.
}
}  Thanks for the solution.
}  But my question is then what does the column in the table abc contain
} when the table is created, if not NULL or space. (Refer below)

When the table is created, there are no rows, so nothing is contained within table abc.  As
I understand your original message (below), you did not insert anything into table abc.  
Because there were no rows in that table, the OUTER clause is required.

Quote:
} }
} }
} }
} }  Hi folks,
} }
} }          There is a small sql problem I faced.
} }
} }  I created a table with one column
} }        create table abc
} }                 (
} }                    col_name varchar(8) NOT NULL
} }                 );
} }
} }  After this I didn't do any kind of transaction against this table.
} }  So, my next sql statement was
} }      select a.column
} }        from table a, abc b
} }       where a.col_name != b.col_name;
} }
} }  This didn't give any result.
} } So i changed this to :
} }      select a.column
} }        from table a, abc b
} }       where a.col_name = b.col_name;
} } And Lo!! no results....!!!!!!!
} }   Finally i inserted a space in the table abc.
} } And both the sql's started working.
} }
} }
} }   ANY COMMENTS....
} }
} } REgards
} }
} } Praveen
} } Sunnyvale, CA

} } tel no : 650.594.9899 X 217
} }

Mark Collins

The problem lies in how easily and dangerously we forget that
manipulating things is not the same as understanding them.



Wed, 18 Jun 1902 08:00:00 GMT
 SQL Problem

    Scott> Try: select a.column from table a, outer abc b where
    Scott> a.col_name != b.col_name; Notice the 'outer'.  This says
    Scott> that if you don't find a row in table b select the row from
    Scott> table a anyway.

If you dont use OUTER, its a cartesian product and 0 x something is
0.  



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. SQL problem, MSDTC Problem or VB.NET problem?

2. sql problem SA problem urgent

3. SQL Problem: DISTINCT problem

4. datagrid problem or SQL problem?

5. SQL Server 6.5 latest service pack SQL problems

6. Access -> SQL Server SQL Problem

7. Oracle/SQL-Server SQL-Problem

8. Cool SQL problem - SQL for Temporal Aggregation

9. embedded SQL:Problem during compilation in embedded sql(MSSQL) with VC++

10. SQL or T-SQL problem

11. sql-problem ADO - MS Sql Server


 
Powered by phpBB® Forum Software