• Welcome, Guest. Please login.
 
May 20, 2019, 12:00:29 pm

News:

Welcome to the SQLitening support forums!


Invalid column with too many functions (duplicate column names)

Started by cj, August 01, 2018, 12:40:26 pm

Previous topic - Next topic

cj

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

Bern Ertl

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()

cj

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.






Bern Ertl

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.