cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

The column index is out of range: 1, number of columns: 0

SOLVED
Highlighted
Level 1

The column index is out of range: 1, number of columns: 0

Dear everyone,

I stucked with the problem that input variable could not put into sql statement,Thingworx give error "

The column index is out of range: 1, number of columns: 0

"

my sql statement like:

select EXTRACT(MONTH from TIMESTAMP '[[ddd]]');   //ddd is the input variable  ,Datetime type

if I put the ddd with '2016-12-01 08:40:00', thingworx will give error.

thank you in advance.


1 ACCEPTED SOLUTION

Accepted Solutions

Re: The column index is out of range: 1, number of columns: 0

Thank you Mahesh ,I have solved the problem,just change the query into "select EXTRACT(MONTH from [[ddd]]::TIMESTAMP);" 。

I don't know why , just keep trying.

6 REPLIES 6

Re: The column index is out of range: 1, number of columns: 0

Hi Xiang Jing Guo​,

I hope ddd is the input variable.

Try following query:

select EXTRACT(MONTH from TIMESTAMP [[ddd]]);

input variable should not be in quotes.

It works fine for me.

I hope it helps.

Thanks,

Ankit Gupta

Re: The column index is out of range: 1, number of columns: 0

Dear  Ankit,

I tryed the query you mentioned above,but get the following error:

ERROR: syntax error at or near "$1" .postion 37.

I use the postgresql as the database .

Thanks anyway.

Re: The column index is out of range: 1, number of columns: 0

Hi Xiang Jing Guo,

Have you tried with adding "Date/Time stamp" pattern in your query?

Like 'dd-mm-yyyy, yyyy-mm-dd' etc.


In my scenario, I used as suggested by Ankit and added specific pattern and it works,


select * from "FOO"

where

to_date(START_DATE)

BETWEEN

to_date([[StartDate]], 'dd-mm-yyyy')

AND

to_date([[EndDate]], 'dd-mm-yyyy')


I will suggest, first try to run the query which you are looking for on your database and then replicate it on ThingWorx.


Hope it helps.


Regards,

Mahesh Lohokare

Re: The column index is out of range: 1, number of columns: 0

Thank you Mahesh ,I have solved the problem,just change the query into "select EXTRACT(MONTH from [[ddd]]::TIMESTAMP);" 。

I don't know why , just keep trying.

Re: The column index is out of range: 1, number of columns: 0

Hey, if one of these answers helped you, do you think you could mark it as correct so other community users can more easily resolve similar issues? Thanks!

Re: The column index is out of range: 1, number of columns: 0

yes Tori,but I didn't find where to mark .