• Welcome, Guest. Please login.
 
September 16, 2019, 09:52:56 am

News:

Welcome to the SQLitening support forums!


Duplicate Column Names Raises Error -13

Started by Fred Meier, April 15, 2010, 12:11:54 pm

Previous topic - Next topic

Fred Meier

We will use the following two tables as examples:
   slExe "Create table T1(F1,F2,F3)   
   slExe "Create table T2(F1,F2)

If you join these two tables as follows:
   slSel "Select T1.F1, T2.F1 from T1, T2"
You will receive error -13 Invalid column name or number.

You are requesting F1 twice.  Qualifying if with the table name
makes SQLite happy but presents a problem for SQLitening as follows. 

SQLitening asks SQLite for the column names after the Select statement is
prepared.  SQLite by default only returns the unqualified names(no table
qualifier), therefore returning F1 twice.  SQLitening will raise an error
because later when you ask for the value of F1 using slFN/slFNX it would
not know which one to return. 

There are several ways to handle this situation.

1.  Add the "D" ModChar to the slSel which will allow duplicate column
names.  You would have to use slF/slFX.  Do no use slFN/slFNX because you
will always get the first value returned. 

2.  Set the Pragma call "full_column_names=1".  This Pragma will have SQLite
return qualified column names.  You can now use slFN/slFNX with the
qualified column names.  Note that this does not work with Select *. 

3.  Assign a different column name to the duplicate ones by using the 'As'
phrase as follows:
      slSel "Select T1.F1 as X1, T2.F1 as X2 from T1, T2"
Now you would use X1 and X2 in you slFN/slFNX statements.  I like this one
the best but any of the three will work. 

Debug Aid:
If you get error -13 and don't know which column is duplicated then add
the "D" ModChar to your slSel and follow that with slGetColumnName.  This
will display all the column names SQLite is returning

   slSel "Select T1.F1, T2.F1 from T1, T2"
   ?slGetColumnName