SQLitening Support Forum

SQLitening Support => You've got Questions? We've got Answers! => Topic started by: cj on August 01, 2018, 12:40:26 pm

Title: Invalid column with too many functions (duplicate column names)
Post by: cj on August 01, 2018, 12:40:26 pm
Is there a limit to the number of functions called in a SELECT statement?
Each of the strftime function calls work if broken into shorter Select statements.

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
DIM sArray() AS STRING
slOpen "junk.db3","C"
slexe "drop table if exists t1"
slexe "create table if not exists t1(c1)"
slselAry "select strftime('%Y',c1),strftime('%m',c1),strftime('%d',c1)," +_
                 "strftime('%H',c1),strftime('%M',c1),strftime('%S',c1) from t1",sArray()
? JOIN$(sArray(),$CR)
END FUNCTION
Title: Re: Invalid column with too many functions
Post by: Bern Ertl on August 01, 2018, 08:26:04 pm
I'm not at my programming rig, so I haven't tested your code, but have you tried:

slselAry "select strftime('%Y',c1) AS Col1,strftime('%m',c1) AS Col2,strftime('%d',c1) AS Col3," +_
                 "strftime('%H',c1) AS Col4,strftime('%M',c1) AS Col5,strftime('%S',c1) AS Col6 from t1",sArray()
Title: Re: Invalid column with too many functions (duplicate column names solved)
Post by: cj on August 02, 2018, 02:06:09 am
That works. Thanks Bern Ertl!

Note:
Duplicate Column Names Raises Error -13 (in the docs.)

"D" modchar does not work as mentioned in the SQLitening docs slsel  "Pragma full_column_names=1"
https://www.sqlite.org/pragma.html#pragma_full_column_names

This pragma is deprecated and exists for backwards compatibility only. New applications should avoid using this pragma. Older applications should discontinue use of this pragma at the earliest opportunity. This pragma may be omitted from the build when SQLite is compiled using SQLITE_OMIT_DEPRECATED.





Title: Re: Invalid column with too many functions (duplicate column names)
Post by: Bern Ertl on August 03, 2018, 04:05:38 pm
Glad it solved the issue.  I've had similar issues in the past so I've gotten into the habit of assigning column aliases now (to expression results) even when I'm not referencing columns by name.