Column type of calculated cols with SQLite

[sudip]

Hi All,

Today I found that calculated cols with SQLite are CHARACTER fields! Is it true by it's nature or I have done some mistakes. Please check 3rd col of the SELECT statement. It should be NUMERIC (in SQLite it should be REAL).

Code: Select all

...
      msql := "select invyear, invno, sum(netamt-paid) as dueamt from (select '"+;
         _accyrid+"' as invyear, invno, netamt, 0.00 as paid from sale where custid = "+c2sql(mcustid)+;
         " UNION select accyrid as invyear, invno, invamt as netamt, (invamt-dueamt) as paid from custbal where custid = "+c2sql(mcustid)+")"+;
         " group by 1, 2"

      table := sql(mdb, msql)
         
      custpmt.custpmtdtl.deleteallitems()
      for i = 1 to len(table)
      
         if valtype(table[i, 3]) = "C"
            table[i, 3] = val(table[i, 3])
         endif
         custpmt.custpmtdtl.additem({table[i, 1], table[i, 2], table[i, 3], 0.00})
      next
...

I searched in SQLite documentation. But, can't find the information right now

Thanks in advance

With best regards.

Sudip

[Rathinagiri]

Hi Sudip,

I think this is because you use sql(oDbo,cQuery) function.

Code: Select all

      do case
         case type1 == "INTEGER" .or. type1 == "REAL" .or. type1 == "FLOAT" .or. type1 == "DOUBLE"
            aadd(typesarr,"N")
         case type1 == "DATE" .or. type1 == "DATETIME"
            aadd(typesarr,"D")
         otherwise
            aadd(typesarr,"C")
      endcase

These lines in sql function deals with the return value type. The calculated columns of SQLite do not come under any of the above, viz., Integer, Real, Float, Double. That's why it is being returned as character.

sqlite3_column_decltype() function returns an empty string for calculated columns.

This is from SQLite manual: http://www.sqlite.org/c3ref/column_decltype.html

Let us research...